Skip to content

Transform XML to recordset with Oracle

29 August 2012

Recently I had to transform some XML data stored as CLOB into ORACLE database as a normal selectable recordset. Fortunately numbers and type of required columns were known.

Here is the setup – to reduce the following samples the XML data goes into a CLOB.

CREATE TABLE t (clmn CLOB);
INSERT INTO t VALUES('<object>
  <object>
    <name>Price< / name>
    <tag>N< / tag>
    <len>15< / len>
    <controlin>0< / controlin>
    <comment>Price< / comment>
    <default>0.00< / default>
    <visible>1< / visible>
    <scale>2< / scale>
    <lowbound>0< / lowbound>
    <highbound>999999999.99< / highbound>
    <canzero>false< / canzero>
  < / object>
  <object>
    <name>ReasonEx< / name>
    <tag>T< / tag>
    <len>35< / len>
    <controlin>0< / controlin>
    <comment>More Reasons< / comment>
    <default/>
    <visible>1< / visible>
  < / object>
  <object>
    <name>PType< / name>
    <tag>T< / tag>
    <len>6< / len>
    <controlin>0< / controlin>
    <comment>Payment type< / comment>
    <default/>
    <visible>1< / visible>
  < / object>
< / object>');

Generally the structure is a node with few child nodes, each of which has different number of children.

I need to fire some

SELECT clmn FROM t;

and to get in return something like

FNAME     FLEN
-----     ----
Price       15
ReasonEx    35
VidPla       6

The first step taken was to convert this CLOB into a XmlType object because result will go as a parameter to xmlsequence which will make easier selecting nodes from it.

SQL> SELECT XMLTYPE(t.clmn) AS cxml FROM t WHERE ROWNUM = 1;

CXML
-----------------------------------------------------------------------------
<object>
  <object>
    <name>Price< / name>
    <tag>N< / tag>
    <len>15< / len>

Second step is to transform XML into few rows, each containing a second level node – i.e. object/object/*. For the purpose the extracted value of the created in the beginning CLOB, transformed into a XmlType, is passed to XmlSequence. To get value from XmlSequence I need to cast it to Table. Here is the result.

SQL> SELECT p.* FROM (SELECT XMLTYPE(t.clmn) AS cxml FROM t WHERE ROWNUM = 1),
 2          TABLE(xmlsequence(extract(cxml, '/object/object'))) p;

COLUMN_VALUE
--------------------------------------------------------------------
<object><name>Price< / name><tag>N< / tag><len>15< / len><controlin>
<object><name>ReasonEx< / name><tag>T< / tag><len>35< / len><control
<object><name>VidPla< / name><tag>T< / tag><len>6< / len><controlin>

Finally I need the values of each particular child node as name, len, etc. So I extract the value of the table using functions with the same names, passing the node name and transform it as a string value. Here it is:

SQL> SELECT EXTRACT(VALUE(p), '//name/text()').getStringVal() AS fieldName,
  2         EXTRACT(VALUE(p), '//len/text()').getStringVal() AS fieldValLen,
  3         EXTRACT(VALUE(p), '//tag/text()').getStringVal() AS fieldType,
  4         EXTRACT(VALUE(p), '//default/text()').getStringVal() AS fieldType
  5  FROM (SELECT XMLTYPE(t.clmn) AS cxml FROM t WHERE ROWNUM = 1),
  6       TABLE(xmlsequence(extract(cxml, '/object/object'))) p;

FIELDNAME FIELDVALLEN FIELDTYPE FIELDTYPE
--------- ----------- --------- ---------
Price     15          N         0.00
ReasonEx  35          T
VidPla    6           T

So I’ve got values as I needed but there is one problem – all columns are type of string.  I prefer to get the correct type which for columns two and four are number. To achieve this I’ve joined the table with column casted to XmlTable with desired columns defined as a type (which I know what it is)

SQL> SELECT x.*
  2    FROM t,
  3         XMLTABLE('/object/object' passing XMLTYPE(t.clmn)
  4         COLUMNS
  5                rn FOR ORDINALITY,
  6                fname VARCHAR2(50) PATH '/*/name',
  7                flen NUMBER PATH '/*/len',
  8                ftype VARCHAR2(1) PATH '/*/tag',
  9                mandatory NUMBER PATH '/*/controlin',
 10                defval VARCHAR2(1000) PATH '/*/default',
 11                decNum NUMBER PATH '/*/scale'
 13       ) x;

      RN FNAME     FLEN FTYPE MANDATORY DEFVAL DECNUM
-------- -----     ---- ----- --------- ------ ------
       1 Price       15 N             0 0.00        2
       2 ReasonEx    35 T             0
       3 VidPla       6 T             0

Now I have a resultset with child nodes as rows and their values in the expected type.

Hope this to be helpful to somebody. Your comments are appreciated.

Advertisements

From → Oracle

3 Comments
  1. Manuel García permalink

    how interesting you saved my day!! thanks!!

  2. Cesar permalink

    You saved my day too! Thanks a lot!!

Trackbacks & Pingbacks

  1. Get XML Node name with Select statment in Oracle DB « ValueType

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: