HOWTO: Selecting XML data directly from your local 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.

So as you can read here… It can be very easily done…

 
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 that is located on E:\temp
 
< ?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>
 
1 ROW selected.
 
.

I hope this was of some help.

😎

Marco

Marco Gralike Written by:

2 Comments

  1. Jason
    March 1

    Hi
    Useful, thanks.
    But for me the following works (I’m on 11.2):

    SELECT XMLTYPE(bfilename(‘XMLSTORE’,’data.xml’),NLS_CHARSET_ID(‘AL32UTF8’)) AS “XDATA”
    2 FROM dual;

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

    0

    0
    Text

  2. March 2

    Of course your richt Jason, but don’t forget this post was from 2008… In 11.2 extract, extractvalue and others are officially already deprecated so… Nowadays in 11.x and above I would shorten my example in the same way as you would have done, if not only in 2008, the example given was not really a supported method of doing things. Now it isn’t a problem anymore and accepted as one of the methods that can be used to load, select XML from the server side.

Comments are closed.