Returning data from one path based on predicates in another (part 2/2)

April 6, 2011

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!

 

 

 

About these ads

3 Responses to “Returning data from one path based on predicates in another (part 2/2)”

  1. Amit Saxena Says:

    Hello Matthias,

    Is it possible to export from DB2 table to a .csv file? If yes, what wud be the command? Can’t I right click from the command center and say export.

    Any help is appreciated!

    -Amit


  2. Hi Amit,

    yes, you can export to any type of delimited format flat file that you choose. This functionality is provided through the EXPORT utility. It can be invoked from the DB2 command line but also from the DB2 Control Center or from IBM Data Studio.

    Here is some information:

    http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.admin.dm.doc/doc/c0004556

    http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.admin.dm.doc/doc/r0004646.html

    http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.admin.cmd.doc/doc/r0008303.html

    I know you can invoke EXPORT from the DB2 GUI (such as Control Center) and there may even be a menu item for it. I’m not using the GUI much so I don’t recall the exact place. Invoking EXPORT at the command line is my personal preference.

    - Matthias

  3. Amit Saxena Says:

    Thank you Matthias. I will try it and let you know.

    -Amit


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

%d bloggers like this: