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 »
