Posts Tagged ‘xmltable’

Oracle XML DB – extracting relational data using XMLTables

Wednesday, April 22nd, 2015

Background

So say you’ve got some XML in an XMLType table. You want to get the data into a “normal” relational table (maybe for faster reporting etc)

The XML looks like this:

<!-- this is one row in the XML table (xml_table) -->
<mxrecords>
   <mxrecord>
       <sysurn></sysurn>
       <description></description>
   </mxrecord>
   <mxrecord>
       <sysurn></sysurn>
       <description></description>
   </mxrecord>
   <mxrecord>
       <sysurn></sysurn>
       <description></description>
   </mxrecord>
</mxrecords>

<!-- this is another row in the XML table -->
<mxrecords>
   <mxrecord>
       <sysurn></sysurn>
       <description></description>
   </mxrecord>
   <mxrecord>
       <sysurn></sysurn>
       <description></description>
   </mxrecord>
   <mxrecord>
       <sysurn></sysurn>
       <description></description>
   </mxrecord>
   <mxrecord>
       <sysurn></sysurn>
       <description></description>
   </mxrecord>
</mxrecords>

The relational table looks like this:

create table reporting(
    sysurn varchar2(50),
    description varchar2(4000)
);

Lets rip the XML into the relational table:

insert into reporting
select mxrecord.* from xml_table xt,
    XMLTABLE(
        '/mxrecords/mxrecord'
        passing xt.OBJECT_VALUE
        columns mxrecord XMLTYPE PATH '/mxrecord') mxrecords,
    XMLTABLE(
        '/mxrecord'
        passing mxrecords.mxrecord
        columns sysurn varchar2(50) PATH '/mxrecord/sysurn',
                description varchar2(4000) PATH '/mxrecord/description') mxrecord

Tags: , , , ,
Posted in How to's, Oracle | No Comments »