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: oracle, xml, xmldb, xmltable, xmltype
Posted in How to's, Oracle | No Comments »