XQuery support in DB2 10 for z/OS

November 30, 2011

If you think that mainframe computers are old dinosaurs that only run ancient COBOL code – think again! Now mainframes also run XQuery!

While DB2 for Linux, UNIX, and Windows has been supporting XQuery and SQL/XML since Version 9.1 (released in 2006), DB2 9 for z/OS “only” supported XPath and SQL/XML.

I have put the word “only” in quotes because for many applications XPath and SQL/XML are fully sufficient. When you combine XPath expressions with SQL/XML functions such as XMLTABLE plus other SQL language constructs you can write very powerful XML queries and accomplish many of the same things that you can do with XQuery.

DB2 10 for z/OS has added a variety of new XML features such as node-level XML updates, XML-type parameters and variables in stored procedures and user-defined functions, and enhancements for XML Schemas and XML indexes.

With APARs PM47617 and PM47618, DB2 for z/OS now also supports XQuery within the SQL functions XMLTABLE, XMLQUERY, XMLEXISTS, and XMLMODIFY.

So what are the additional capabilities and benefits that XQuery provides? Examples include:

  • You can compose new XML structures using direct element constructors
  • You can use FLWOR expressions (for-let-where-order by-return) to iterate over and manipulate intermediate query results
  • You can join and combine information from multiple XML documents
  • You can use XQuery comma-expressions to construct and use new sequences of XML nodes
  • You can code if-then-else logic to implement conditional expressions
  • etc.

Let’s look at some examples.

Construct new XML structures with direct element and attribute constructors

The following query constructs a new order summary document from each order (XML document) that is selected from the “orders” table. New elements, such as <orderSummary> and <orderedItems> are constructed by providing the start and end tags explicitly. Similarly, the attribute orderNumber is also constructed explicitly. The content of the constructed elements and attributes is computed by XPath (or XQuery) expressions that extract selected information from each source document.

SELECT XMLQUERY('
          <orderSummary orderNumber="{$po/order/orderNo/text()}">
             <orderedItems>{$o/order/items/item/itemName}</orderedItems>
          </orderSummary>'
       PASSING orders.orderdoc AS "po")
FROM orders
WHERE ...

FLWOR expressions

The next query joins the tables “orders” and “items” on their XML columns “orderdoc” and “details”, respectively. The join predicate in the XMLEXISTS ensures that we find the items that match a given order and that we don’t produce a Cartesian product. For each pair of order document and item document, the FLWOR expression in the SELECT clause combines information from both documents into a new documents that contains the item name, the ordered quantity, and the item details.

SELECT XMLQUERY('for $o in $po/orders/items/item
                   for $i in $it/item
                 where $o/itemName = $i/name
                 return <orderdItem>
                           {$i/name}
                           {$o/item/quantity}
                           {$i/details}
                        </orderdItem>'
         PASSING orders.orderdoc AS "po", items.details as "it")
FROM orders, items
WHERE
XMLEXISTS('$po/order/items/item[itemName = $it/item/name]'
           PASSING orders.orderdoc AS "po", items.details as "it")

 

For more information on XQuery in DB2 10 for z/OS, here is a good place to continue reading:

http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/topic/com.ibm.db2z10.doc.xml/src/tpc/db2z_expover.htm

About these ads

4 Responses to “XQuery support in DB2 10 for z/OS”

  1. Andreas Says:

    Will XQuery update expressions work “everywhere” for DB2 on z/OS with the mentioned APARs?


  2. Hi Andreas,

    XQuery updates are supported in DB2 10 for z/OS even without APARs PM47617 and PM47618. (Also see:
    http://nativexmldatabase.com/2010/11/30/node-level-xml-updates-in-db2-10-for-zos/ )

    I’m not sure what you mean by “work everywhere”. If you are referring to a specific restriction (or a specific requirement that you might have), can you elaborate and maybe provide an example?

    Thanks,

    Matthias

  3. Andreas Says:

    The XMLMODIFY function can only be used in UPDATE statements in the SET clause, right?

    With Xquery update expressions on LUW you seem to be able to modify documents during SELECTs and INSERTs as well.

    One useful situation would be to construct and insert xml elements from relational columns during SELECT. The same goes for INSERTs, it seem quite useful to be able to modify an element value during INSERT instead of first do the INSERT and then an UPDATE.


    • Thanks, Andreas. The additional support for XQuery does not change the current restrictions for the SQL function XMLMODIFY, which can only be used in the SET clause of UPDATE statements.

      I agree that it can also be useful to have XML update expressions in SELECT or INSERT statements. If this is a strong requirement that you have for DB2 z/OS, please do send me an email about this (mnicola at us.ibm.com). Thx!

      Matthias


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: