Selecting (XML) data directly from disk

5

On the OTN XMLDB Forum, a question was asked if you could directly select from a XML file on disk, or to be more precise "
XQUERY / XMLTABLE Syntax to read and XML from a Windows local drive?". I had the idea that this could be easily done, nowadays via XMLDB functionality. So I tried it and came to the following conclusions. Although tested on Oracle 11.1.0.6.0 on Windows, this should also work on Oracle 10.2.x.

....
 

SQL&gt; conn test/test<br />Connected.<br />&nbsp;<br />SQL&gt; -- The test user has the DBA role...<br />&nbsp;<br />SQL&gt; select * from v$version;<br />&nbsp;<br />BANNER<br />-----------------------------------------------------------------------<br />Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production<br />PL/SQL Release 11.1.0.6.0 - Production<br />CORE    11.1.0.6.0      Production<br />TNS <font color="navy"><strong>for</strong></font> 32-bit Windows: Version 11.1.0.6.0 - Production<br />NLSRTL Version 11.1.0.6.0 - Production<br />&nbsp;<br />5 rows selected.<br />&nbsp;<br /><font color="darkgreen">/* The content of the data.xml file.<br /><br />&lt;?xml version=&quot;1.0&quot; encoding=&quot;UTF-8&quot;?&gt;<br />&lt;ROOT&gt;<br />  &lt;ID&gt;0&lt;/ID&gt;<br />  &lt;INFO&gt;<br />    &lt;INFO_ID&gt;0&lt;/INFO_ID&gt;<br />    &lt;INFO_CONTENT&gt;Text&lt;/INFO_CONTENT&gt;<br />  &lt;/INFO&gt;<br />&lt;/ROOT&gt;<br /><br />*/</font><br />&nbsp;<br />SQL&gt; drop directory  xmlstore;<br />&nbsp;<br />Directory dropped.<br />&nbsp;<br />&nbsp;<br />SQL&gt; -- the directory is on a Windows system...<br />&nbsp;<br />SQL&gt; create directory xmlstore as <font color="navy">'E:\temp'</font>;<br />&nbsp;<br />Directory created.<br />&nbsp;<br />SQL&gt; create table test<br />  2  (xmldata xmltype);<br />&nbsp;<br />Table created.<br />&nbsp;<br />SQL&gt; INSERT into test<br />  2  VALUES<br />  3  (XMLTYPE(bfilename(<font color="navy">'XMLSTORE'</font>,<font color="navy">'data.xml'</font>),NLS_CHARSET_ID(<font color="navy">'AL32UTF8'</font>)));<br />&nbsp;<br />1 row created.<br />&nbsp;<br />&nbsp;<br />SQL&gt; set <font color="navy"><strong>long</strong></font> 100000000<br />&nbsp;<br />SQL&gt; select * from test;<br />&nbsp;<br />XMLDATA<br />--------------------------------------------------------<br />&lt;?xml version=<font color="red">&quot;1.0&quot;</font> encoding=<font color="red">&quot;UTF-8&quot;</font>?&gt;<br />&lt;ROOT&gt;<br />  &lt;ID&gt;0&lt;/ID&gt;<br />  &lt;INFO&gt;<br />    &lt;INFO_ID&gt;0&lt;/INFO_ID&gt;<br />    &lt;INFO_CONTENT&gt;Text&lt;/INFO_CONTENT&gt;<br />  &lt;/INFO&gt;<br />&lt;/ROOT&gt;<br />&nbsp;<br />&nbsp;<br />1 row selected.<br />&nbsp;<br />SQL&gt; select XMLTYPE(<font color="navy">'&lt;ROOT&gt;&lt;ID&gt;0&lt;/ID&gt;&lt;INFO&gt;&lt;INFO_ID&gt;0&lt;/INFO_ID&gt;&lt;INFO_CONTENT&gt;Text&lt;/INFO_CONTENT&gt;&lt;/INFO&gt;&lt;/ROOT&gt;'</font>) as <font color="red">&quot;XDATA&quot;</font><br />  2  from dual;<br />&nbsp;<br />XDATA<br />------------------------------------------------------------------------------------------<br />&lt;ROOT&gt;&lt;ID&gt;0&lt;/ID&gt;&lt;INFO&gt;&lt;INFO_ID&gt;0&lt;/INFO_ID&gt;&lt;INFO_CONTENT&gt;Text&lt;/INFO_CONTENT&gt;&lt;/INFO&gt;&lt;/ROOT&gt;<br />&nbsp;<br />1 row selected.<br />&nbsp;<br />&nbsp;<br />SQL&gt; select XMLTYPE(bfilename(<font color="navy">'XMLSTORE'</font>,<font color="navy">'data.xml'</font>),NLS_CHARSET_ID(<font color="navy">'AL32UTF8'</font>)) as <font color="red">&quot;XDATA&quot;</font><br />  2  from dual;<br />ERROR:<br />ORA-21500: internal error code, arguments: [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s]<br />&nbsp;<br />&nbsp;<br />&nbsp;<br />no rows selected<br />&nbsp;<br />&nbsp;<br />SQL&gt; select extract((XMLTYPE(bfilename(<font color="navy">'XMLSTORE'</font>,<font color="navy">'data.xml'</font>),NLS_CHARSET_ID(<font color="navy">'AL32UTF8'</font>))),<font color="navy">'*'</font>) as <font color="red">&quot;XDATA&quot;</font><br />  2  from dual;<br />&nbsp;<br />XDATA<br />------------------------------------------------------------------------------------------<br />&lt;ROOT&gt;&lt;ID&gt;0&lt;/ID&gt;&lt;INFO&gt;&lt;INFO_ID&gt;0&lt;/INFO_ID&gt;&lt;INFO_CONTENT&gt;Text&lt;/INFO_CONTENT&gt;&lt;/INFO&gt;&lt;/ROOT&gt;<br />&nbsp;<br />1 row selected.<br />&nbsp;<br />SQL&gt; select xdata<br />  2  from (XMLTABLE(<font color="navy">'*'</font><br />  3                 PASSING (XMLTYPE(bfilename(<font color="navy">'XMLSTORE'</font>,<font color="navy">'data.xml'</font>),NLS_CHARSET_ID(<font color="navy">'AL32UTF8'</font>)))<br />  4                 COLUMNS xdata xmltype PATH <font color="navy">'/*'</font><br />  5                )<br />  6       )<br />  7  ;<br />&nbsp;<br />XDATA<br />------------------------------------------------------------------------------------------<br />&lt;ROOT&gt;&lt;ID&gt;0&lt;/ID&gt;&lt;INFO&gt;&lt;INFO_ID&gt;0&lt;/INFO_ID&gt;&lt;INFO_CONTENT&gt;Text&lt;/INFO_CONTENT&gt;&lt;/INFO&gt;&lt;/ROOT&gt;<br />

 

Cheers

Smiley 

Marco 

Share.

About Author

Marco Gralike, working for AMIS Services BV as a Principal Oracle Database Consultant in the Netherlands, has experience as a DBA since 1994 (Oracle 6). Marco is also eager and skillful in other fields, like Operating System Administration and Application Servers, mainly to find working, performing solutions. Marco has been specializing in Oracle XMLDB, since 2003, focusing on his old love, database administration and performance. He is an Oracle XMLDB enthusiast ever since. He is also a dedicated contributor of the Oracle User Group community, helping people with their steep XMLDB learning curve. To this purpose, Marco also devoted his personal blog site to XMLDB and other Oracle issues. Marco is a member of the OakTable network and an Oracle ACE Director (specialization Oracle XMLDB).

5 Comments

  1. Karin Kriebisch on

    Hello Marco,
    I have some questions about your article:
    Can give any indication about the performance of this read action? Where is the greatest restriction in the performance?
    Are there any restrictions on the file sizes other than the OS?
    And in which circumstances would you preferably use this technique?

    Cheers,
    Karin

  2. On 10.2.0.2.0 it works without the extract. Note by the way how the encoding is changed to “ISO-8859-1″

    [pre]

    PGI@pgio> select XMLTYPE(bfilename(‘XMLSTORE’,'data.xml’),NLS_CHARSET_ID(‘AL32UTF8′)) as “XDATA”
    2 from dual
    3 /

    XDATA
    ——————————————————————————–

    0

    0
    Text

    [/pre]

  3. A drive local to your database server. No, it also works for other operating systems (adjust your directory alias accordingly).

  4. Nice, but it raise some questions:

    What is a “Windows local drive”? A drive local to your database server? A drive local to your database client?
    Is the above code only working for Windows?