How to list all elements and attributes in DB2 for z/OS

January 26, 2013

In a previous post I described how to write a query that produces a list of all elements and attributes that occur in a document or a set of documents. 

The core idea of that technique was to use the XPath expression $doc//(*, @*) in the XMLTABLE function. In this expression, $doc references the XML document or XML column that is used as input. This expression also uses the so-called comma operator that combines two squences into one. These two sequences are are all elements (*) and all attributes (@*).

For this technique to work in DB2 for z/OS, you need to code the XPath expression in a slightly different way:  ($doc//*,  $doc//@*)  , which lists all elements followed by all attributes.

It is useful to sort the result by the parent of each node in the document so that the elements and attributes that belong to the same parent appear in consecutive order in the result set.

The following listing shows two queries that produce the same result set, and the second of the two queries is the recommendad notation for DB2 for z/OS:


SELECT T.*
FROM purchaseorder p,
     XMLTABLE('$doc//(*, @*)' passing p.porder as "doc"
      COLUMNS
       node   VARCHAR(20) PATH 'name(.)',
       parent VARCHAR(20) PATH '../name(.)') AS T
WHERE poid = 5000
ORDER BY parent;

SELECT T.*
FROM purchaseorder p,
     XMLTABLE('($doc//*, $doc//@*)' passing p.porder as "doc"
      COLUMNS
       node   VARCHAR(20) PATH 'name(.)',
       parent VARCHAR(20) PATH '../name(.)') AS T
WHERE poid = 5000
ORDER BY parent;

 And then you can ertainly extend such queries to also list the node values or an indication whether the node is an element or an attribute, as previously described here.

Similar considerations apply to queries that list all paths in a document.

 

 

 

About these ads

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

Follow

Get every new post delivered to your Inbox.

Join 50 other followers

%d bloggers like this: