Skip to content

Get XML Node name with Select statment in Oracle DB

15 September 2012

In the previous trial I’ve got XmlNode as a recordset. The pro was that XmlNode structure was constant and mandatory so it was easy to process it.

The set-up

This time there is a similar structure but children are not mandatory at all so they might be some, many or missing at all.

The basic structure contains a root node; at least one child which might have zero or more children. The sample XmlNode is below between the brackets of XmlType.

There is a root node named ‘docs‘. Second-level node is named ‘doc‘ and at least one must exist as a child of ‘docs‘. Third level nodes might or might not exist. If any exists it might have some attributes. Attributes are known number but might be bypassed.

The task

The task is to get whatever child nodes exist under ‘doc‘ node; their node name, value and the values of the presented attributes.

Here is the final SELECT statement:

SELECT * FROM
 XMLTABLE( 'for $i in /docs/doc/*
 return <n nname="{$i/name(.)}" val="{$i/text()}" lbl="{$i/@lbl}"
 visible="{$i/@visible}" readonly="{$i/@readonly}"
 dbValue="{$i/@dbValue}"/>'
 PASSING XMLTYPE('<docs inTransaction="false">
                    <doc templid="4032" lang="bg">
                      <test lbl="test lable" visible="true" readonly="true">This is a test< / test>
                   </doc>
                   <doc templid="4031" lang="bg">
                     <valor lbl="Execution date" visible="false" dbValue="select to_char(sysdate, 'dd.mm.yyyy') from dual" / >
                   < / doc>
                 < / docs>') 
 COLUMNS nname VARCHAR2(100) PATH './@nname',
         val VARCHAR2(100) PATH './@val',
         lbl VARCHAR2(100) PATH './@lbl',
         visible VARCHAR2(100) PATH './@visible',
         readonly VARCHAR2(100) PATH './@readonly',
         dbValue VARCHAR2(100) PATH './@dbValue'
 );

To Expound

For those interested in what’s what. Or those thinking that the above is complicated.

It turns out that there it is not possible to get directly any node name using existing Oracle (ver. 10 & 11) packages and functions (or at least I am not aware of). This is problem for this case because if node exists I must know what node name is and appropriate actions have to be taken.

Fortunately from Oracle 10gR2 XmlTable accepts string as an anonymous functions (so called FLOWR expressions).

Let’s analyse the parts in the XmlTable from the SELECT statement above.

The First Part

We have a string representation of an anonymous function. In fact this is a function’s body applied to a collection (of xml node in the case). Almost all languages have some sort of for or foreach loops that might be applied to different types of collections (see  How does for each loop work?)

So the function body is

function collLoop() {  /* to add a definition for any case ;-) */
 for ($i in selectNodes("/docs/doc/*")) { /* /docs/doc/* is just our collection */
  /* for block is missing here but obviously 
     it makes something to supply desired result. */
 }
}

Each function must have a return clause. This one also has. This is what is desired to be returned as a recordset.

return <n nname="{$i/name(.)}" val="{$i/text()}" lbl="{$i/@lbl}"
 visible="{$i/@visible}" readonly="{$i/@readonly}"
 dbValue="{$i/@dbValue}"/>'

Here for each node found (current is kept in variable $i) a new node is composed as a string . Curly brackets – {} – are just a template (well known and used in many contemporary frameworks),. They are replaced with the value of the XPath query passed into them.

The result is:

<n nname="name_of_the_node_found" 
   val="value_of_the_node_found" 
   lbl="value of the lbl attribute"
   visible="value of the visible attribute" 
   readonly="value of the readonly attribute"
   dbValue="value of the dbValue attribute" />

The result of this function is n number of nodes (as string) which goes into the XmlTable rows.

The Second Part

The second part is whatever is between brackets of PASSING XMLTYPE. In there might be a string representation of XML structure or CLOB or any input valid for XmlType. This tell to our anonymous function where to get collection from. This is quite straightforward  and it seems to me that there is nothing special to discuss.

The Third Part

PASSING XMLTYPE requires COLUMNS definitions (refer to XmlTable). So here they are. Whatever we’d like to appear as a recordset column name is defined here. For each column has PATH definition, i.e. which element form the current node must appear in that column.

End

So in short here is a sample with the result:

SQL> 
SQL> SELECT * FROM
  2  XMLTABLE( 'for $i in /docs/doc/*
  3                   return <n nname="{$i/name(.)}" val="{$i/text()}" lbl="{$i/@lbl}"
  4                             visible="{$i/@visible}" readonly="{$i/@readonly}">
  5                            <dbValue>{$i/dbValue/text()}</dbValue>
  6                          </n>'
  7  PASSING XMLTYPE('<docs inTransaction="false">
  8                    <doc id="4032" lang="bg">
  9                      <test lbl="test lable" visible="true" readonly="true">This is a test</test>
 10                    </doc>
 11                    <doc id="4031" lang="bg">
 12                      <valor lbl="start_date" visible="false">
 13                        <dbValue>
 14                          <![CDATA[ select to_char(sysdate, 'dd.mm.yyyy') from dual ]]>
 15                        </dbValue>
 16                      </valor>
 17                    </doc>
 18                  </docs>')
 19  COLUMNS nname VARCHAR2(100) PATH './@nname',
 20          val VARCHAR2(100) PATH './@val',
 21          lbl VARCHAR2(100) PATH './@lbl',
 22          visible VARCHAR2(100) PATH './@visible',
 23          readonly VARCHAR2(100) PATH './@readonly',
 24          dbValue VARCHAR2(100) PATH './dbValue'
 25  );

NNAME     VAL              LBL          VISIBLE   READONLY   DBVALUE
--------- ---------------- ------------ --------- ---------- -----------------------------------------------
test      This is a test   test lable   true      true       
valor                      start_date   false                select to_char(sysdate, 'dd.mm.yyyy') from dual

SQL>
Advertisements

From → Oracle

Leave a Comment

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: