Posts Tagged ‘oracle’

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 »

Oracle XML DB – XMLType table creation

Wednesday, March 18th, 2015

Background

So I’ve been doing some Oracle XML work again. I’d lost my notes from last time (nice touch) so here are my notes from this time:

XMLType Table Creation

CREATE TABLE xml_table OF XMLType

This will create “an xml table” into which you can insert XML. It will check that the XML is well formed before insertion. It will not validate that all documents inserted are the same or that elements contain valid data.

For that we need to create a table based upon an XML Schema/XSD

-- create an Oracle directory where the schema can be stored
create or replace directory XSD as '/home/foo/bar/oracle/xsd'

-- copy your Schema into the directory
-- then
BEGIN
  DBMS_XMLSCHEMA.registerSchema(SCHEMAURL => 'http://www.foobar.com/xsd/my_schema.xsd', SCHEMADOC => bfilename('XSD','my_schema.xsd'));
END;

-- Note: the SCHEMAURL can be basically anything. Oracle uses it as a unique identifier
-- Create the table
CREATE TABLE matter OF xml_table XMLSCHEMA "http://www.foobar.com/xsd/my_schema.xsd" ELEMENT "nameOfTheRootXmlElement";

There are many overloaded versions of DBMS_XMLSCHEMA.registerSchema so you can shortcut some of the steps above. For example:

BEGIN
  DBMS_XMLSCHEMA.registerSchema(
    http://www.foobar.com/xsd/my_schema.xsd',
    bfilename('XSD','my_schema.xsd'),
    TRUE, -- generate required Oracle types. Default = true
    TRUE, -- generate javabeans. Default = false
    FALSE, -- generate errors when registering schema. True = do not
    TRUE -- generate table
);
END;

Full docs for the DBMS_XMLSCHEMA package are here
here

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

View Source of Oracle Trigger

Wednesday, May 11th, 2011

select trigger_body from user_triggers where trigger_name = 'XXXXX'

Tags: ,
Posted in quick tips | No Comments »

Using Oracle’s SYS_GUID()

Friday, April 29th, 2011

Background

For years I’ve bemoaned Oracle’s lack of an auto-number/identity type column (think MySQL AUTO_INCREMENT). Obviously you can create sequences and triggers to produce the same effect but it always seemed like a bit of a palaver to me.

So, I had a smile on my when I discovered the delight that is SYS_GUID().

Taken from the Oracle documentation:

SYS_GUID generates and returns a globally unique identifier (RAW value) made up of 16 bytes. On most platforms, the generated identifier consists of a host identifier, a process or thread identifier of the process or thread invoking the function, and a nonrepeating value (sequence of bytes) for that process or thread.

Example

select SYS_GUID() from dual

  SYS_GUID()
= ================================
1 A20D42BA1F165DB5E04400144FB99F0A

SYS_GUID() as a default

Because SYS_GUID() “returns a globally unique identifier” we can use it as a default value in a Primary Key column of a table.

create table products( 
    product_id raw(32) default sys_guid() not null primary key
    ............
);

Tags: ,
Posted in Development | No Comments »