Archive for the ‘Oracle’ Category

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 »

Oracle XML DB – Inserting XML

Friday, March 20th, 2015

Background

So following on from my previous post about creating XMLType tables in Oracle (either CLOB or schema based), here’s a few quick notes on getting data into the tables.

Inserting Data into XMLType Table

-- Create XMLType Table
CREATE TABLE XML_TABLE OF XMLType 

-- create an Oracle directory to hold the XML (readable by Oracle)
CREATE OR REPLACE DIRECTORY XML as '/home/foo/bar/xml'

-- insert the record (reading from filesystem)
-- filename.xml would be in directory you created above
INSERT INTO XML_TABLE(SELECT XMLTYPE(bfilename('XML', 'filename.xml'), nls_charset_id('UTF8')) FROM dual);

Of course you could easily write some PL/SQL to iterate all files in the directory.

You can also insert XML directly via SQL:

INSERT INTO XML_TABLE VALUES(XMLType('<mxrecords>
        <mxrecord>
            <sysurn>sysurn1</sysurn>
            <eventid>eventid1</eventid>
        </mxrecord>
        <mxrecord>
            <sysurn>sysurn2</sysurn>
            <eventid>eventid2</eventid>
        </mxrecord>
    </mxrecords>'
));

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