Selecting (XML) data directly from disk

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

Marco