Native XML Database

This is the continuation of my previous post, to see how we can write a query that achieves the following:

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

And here is the sample data that we want to use. Note the twist that the first document contains two <a> elements!

drop table t1;
create table T1(doc XML);

insert into t1 values(
'<root docNo="1">
   <a>
     <a1>1</a1>
     <a2>1111</a2>
   </a>
   <a>
     <a1>90901</a1>
     <a2>90902</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>5555</a2>
   </a>
   <b>
     <b1>2</b1>
     <b2>6666</b2>
   </b>
   <c>
     <c1>1</c1>
     <c2>2</c2>
   </c>
</root>');

To return <a2> if the value of <a1> equals the value of <c1>, and to return <b2> if <b1> equals <c2>, you could follow the exact same query pattern as in my previous post and write the following XQuery:

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

<result docNo="1"><a2>1111</a2><a2>90902</a2></result>
<result docNo="2"><a2>5555</a2><b2>6666</b2></result>

 2 record(s) selected.

However, we probably don’t want <a2>90902</a2> in the first result record, because its corresponding <a1> sibling does not match <c1>!

The problem is that the XPath expression $i[ a/a1 = c/c1 ]/a/a2  returns all occurrences of /a/a2 in the document if only one of  the a/a1 equals c/c1. We need to write an XPath expression that recognizes the fact that the elements <a1> and <a2> are pairs under a common <a> element. What we want is the following: if <a1> equals <c1>, then return the <a2> element that has the same parent as the <a1> that equals <c1>.

The following query does the trick. Note that the predicate in square brackets is now applied to $i/a instead of $i. In other words, we shift the predicate one level down in order to address the <a1> and <a2> under a common <a> element.

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

<result docNo="1"><a2>1111</a2></result>
<result docNo="2"><a2>5555</a2><b2>6666</b2></result>

 2 record(s) selected.

Or the same in the SQL/XML:

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


DOCNO       A1EQUALSC1  B1EQUALSC2
----------- ----------- -----------
          1        1111           -
          2        5555        6666

 2 record(s) selected.

It’s all about climbing up and down the branches of the XML document tree to get the correct twigs that we want. In fact, drawing the document as a tree on a piece of paper can be a great help!