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