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> conn test/test
Connected.
 
SQL> -- The test user has the DBA role...
 
SQL> select * from v$version;
 
BANNER
-----------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE    11.1.0.6.0      Production
TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production
 
5 rows selected.
 
/* The content of the data.xml file.

<?xml version="1.0" encoding="UTF-8"?>
<ROOT>
  <ID>0</ID>
  <INFO>
    <INFO_ID>0</INFO_ID>
    <INFO_CONTENT>Text</INFO_CONTENT>
  </INFO>
</ROOT>

*/
 
SQL> drop directory  xmlstore;
 
Directory dropped.
 
 
SQL> -- the directory is on a Windows system...
 
SQL> create directory xmlstore as 'E:\temp';
 
Directory created.
 
SQL> create table test
  2  (xmldata xmltype);
 
Table created.
 
SQL> INSERT into test
  2  VALUES
  3  (XMLTYPE(bfilename('XMLSTORE','data.xml'),NLS_CHARSET_ID('AL32UTF8')));
 
1 row created.
 
 
SQL> set long 100000000
 
SQL> select * from test;
 
XMLDATA
--------------------------------------------------------
<?xml version="1.0" encoding="UTF-8"?>
<ROOT>
  <ID>0</ID>
  <INFO>
    <INFO_ID>0</INFO_ID>
    <INFO_CONTENT>Text</INFO_CONTENT>
  </INFO>
</ROOT>
 
 
1 row selected.
 
SQL> select XMLTYPE('<ROOT><ID>0</ID><INFO><INFO_ID>0</INFO_ID><INFO_CONTENT>Text</INFO_CONTENT></INFO></ROOT>') as "XDATA"
  2  from dual;
 
XDATA
------------------------------------------------------------------------------------------
<ROOT><ID>0</ID><INFO><INFO_ID>0</INFO_ID><INFO_CONTENT>Text</INFO_CONTENT></INFO></ROOT>
 
1 row selected.
 
 
SQL> select XMLTYPE(bfilename('XMLSTORE','data.xml'),NLS_CHARSET_ID('AL32UTF8')) as "XDATA"
  2  from dual;
ERROR:
ORA-21500: internal error code, arguments: [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s]
 
 
 
no rows selected
 
 
SQL> select extract((XMLTYPE(bfilename('XMLSTORE','data.xml'),NLS_CHARSET_ID('AL32UTF8'))),'*') as "XDATA"
  2  from dual;
 
XDATA
------------------------------------------------------------------------------------------
<ROOT><ID>0</ID><INFO><INFO_ID>0</INFO_ID><INFO_CONTENT>Text</INFO_CONTENT></INFO></ROOT>
 
1 row selected.
 
SQL> select xdata
  2  from (XMLTABLE('*'
  3                 PASSING (XMLTYPE(bfilename('XMLSTORE','data.xml'),NLS_CHARSET_ID('AL32UTF8')))
  4                 COLUMNS xdata xmltype PATH '/*'
  5                )
  6       )
  7  ;
 
XDATA
------------------------------------------------------------------------------------------
<ROOT><ID>0</ID><INFO><INFO_ID>0</INFO_ID><INFO_CONTENT>Text</INFO_CONTENT></INFO></ROOT>

Cheers

Marco