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.