0 
0 Read Time:2 Minute, 54 Second
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
Happy
0 %
Sad
0 %
Excited
0 %
Sleepy
0 %
Angry
0 %
Surprise
0 %
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
The encoding bit is due to your client NLS setting, see: http://forums.oracle.com/forums/thread.jspa?messageID=1255505
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]
A drive local to your database server. No, it also works for other operating systems (adjust your directory alias accordingly).
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?