Monday, May 14, 2012

Parse xml documents directly from filesystem without loading into Oracle database

First, create a database directory object and grant it read,write privilages.
Create or replace directory xmlload as '/tmp/xmlload';
grant read,write on XMLLOAD to <username>;

-- to avoid some ora-600 errors in version of DB.
alter session set events='31156 trace name context forever, level 0x400';

If a DBA is tasked to read a xml file, he will like to see the output as rows and columns after parsing base on xquery condition...


WITH vendxml_col AS
(select XMLTYPE(bfilename('XMLLOAD','template.xml'),NLS_CHARSET_ID('AL32UTF8'))vend_doc from dual)
-- Above WITH used to simulate your table/data read from file.
  FROM vendxml_col,
       XMLTABLE('for $i in /import_data/template/template_attribute
$i/@name eq "UPN"
$i/@searchable eq "n"
                                   return $i'
                PASSING vendxml_col.vend_doc
                name varchar2(300) PATH
operation varchar2(500) PATH
searchable varchar2(1) PATH
'@searchable') a;

However, if you  want a xml output  as a result of your parsing you can simply read xmltype instead of xmltable.

select xmlquery(
'for $i in /import_data/template/template_attribute
$i/@name eq "VIJAY"
                                   return $i'
PASSING d.xml_doc  returning content) the_result
(select xmltype (bfilename('XMLLOAD','template.xml'),NLS_CHARSET_ID('AL32UTF8')) xml_doc from dual) d;

No comments:

Post a Comment

Feedback welcome