Intra-document references: Returning data from one path depending on predicates in another path

April 3, 2011

A few weeks ago somebody asked me how to select information from one branch of an XML document based on search conditions in another branch of the same document. Writing such a query in XPath (or XQuery or SQL/XML) is actually not very complicated. It’s a nice example of a query that is very hiearchical and “navigational” in nature, and yet very easy to express in XPath.

Let’s look at a simple example, using the three XML documents and a simple table, as shown below. We want to query each of these documents as follows:

  • if the value of <a1> equals the value of <c1>, then return <b1>
  • similarly, if <a2> equals <c2>, then return <b2>
  • these two conditions are not mutually exclusive, i.e. both may be true in a given document
  • in any case, include the @docNo in the result
drop table t1;
create table T1(doc XML);

insert into t1 values(
'<root docNo="1">
   <a>
     <a1>1</a1>
     <a2>2</a2>
   </a>
   <b>
     <b1>1111</b1>
     <b2>2222</b2>
   </b>
   <c>
     <c1>1</c1>
     <c2>0</c2>
   </c>
</root>');

insert into t1 values(
'<root docNo="2">
   <a>
     <a1>1</a1>
     <a2>2</a2>
   </a>
   <b>
     <b1>3333</b1>
     <b2>4444</b2>
   </b>
   <c>
     <c1>0</c1>
     <c2>2</c2>
   </c>
</root>');

insert into t1 values(
'<root docNo="3">
   <a>
     <a1>1</a1>
     <a2>2</a2>
   </a>
   <b>
     <b1>5555</b1>
     <b2>6666</b2>
   </b>
   <c>
     <c1>1</c1>
     <c2>2</c2>
   </c>
</root>');

To query these document as outlined abobve, you could write an XQuery or a SQL/XML query, depending on your preference. The core pieces of XPath required are the same in either case.

Here is one possible solution in XQuery and its result:

xquery
for $i in db2-fn:xmlcolumn('T1.DOC')/root
return <result>
         {$i/@docNo}
         {$i[ a/a1 = c/c1 ]/b/b1}
         {$i[ a/a2 = c/c2 ]/b/b2}
       </result>;

<result docNo="1"><b1>1111</b1></result>
<result docNo="2"><b2>4444</b2></result>
<result docNo="3"><b1>5555</b1><b2>6666</b2></result>

 3 record(s) selected.

In this XQuery, the “for” clause iterates over the root elements of the three document. For each document it constructs a <result> element, whose child nodes are defined by three expressions. In each of these expressions, note that $i is bound to the <root> element of the current document.

  • The first expression (line 4), simply includes the @docNo attribute from the <root> element.
  • The second expression (line 5), applies the predicate [a/a1=c/c1] to $i, such that the element $i/b/b1 is returned only if a1=c1 in this document
  • The third expression (line 6), applies the predicate [a/a2=c/c2] to $i, so that the element $i/b/b2 is returned only if a2=c2.

The same XPath expression can be plugged into a SQL/XML statement. For example, you can use the XMLTABLE function to return the result in list form:

SELECT X.*
FROM t1, XMLTABLE('$DOC/root'
 COLUMNS
    docno      INTEGER PATH './@docNo',
    a1equalsc1 INTEGER PATH '.[a/a1=c/c1]/b/b1',
    a2equalsc2 INTEGER PATH '.[a/a2=c/c2]/b/b2' ) as X;

DOCNO       A1EQUALSC1  A2EQUALSC2
----------- ----------- -----------
          1        1111           -
          2           -        4444
          3        5555        6666

 3 record(s) selected.

And of course you can modify these queries if you want the results to be returned in a different way.

Now there is an interesting variation to this exercise, which is slightly more tricky then it looks at first sight. Let’s say we change the requirements for our query to the following:

  • if the value of <a1> equals the value of <c1>, then return <a2>
  • similarly, if <b1> equals <c2>, then return <b2>

How would you change the queries above? And what happens if there can be multiple <a> elements and multiple <b> elements in each document? I’ll post the answer in a few of days…

 

 

 

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 52 other followers

%d bloggers like this: