Advanced SQL/XML: Joins and FLWOR Expressions in the XMLTABLE Function

October 9, 2011

If you look at existing documentation and examples of the XMLTABLE function, you find that most examples use simple XPath expressions inside the XMLTABLE function and rarely more complex XQuery expressions such as FLWOR expressions. This is also true for my 2 articles on XMLTABLE:

XMLTABLE By Example, Part1 and Part2:
http://www.ibm.com/developerworks/data/library/techarticle/dm-0708nicola/
http://www.ibm.com/developerworks/data/library/techarticle/dm-0709nicola/

Why is that? Is it because XQuery expressions other than plain path expressions are never required in the XMLTABLE function? Well, let’s take a look at that question…

First, I do believe that many typical SQL/XML queries can indeed be coded with just regular XPath in the XMLTABLE function. I have seen that this is true in many real application deployments.

But, sometimes it can certainly be useful to embed more complex XQuery expressions than just XPath in an XMLTABLE function. For example, in my blog post “How to get a list of all elements and attributes” you saw that I used the XQuery comma operator to construct a sequence from two expressions. In that case the comma expression was //(*,@*) to get all elements and all attributes. In the same post you also saw the use of the XQuery if-then-else expression in the column definition of the XMLTABLE function.

Using FLWOR expression in the XMLTABLE function can be useful if you join and combine XML from two different XML columns. For example let’s consider the following two tables with te subsequent sample documents:


CREATE TABLE order(orderdetails XML);
CREATE TABLE product(description XML);

INSERT INTO order VALUES('
<order>
  <orderNo>123</orderNo>
  <item>
    <pid>24TX98</pid>
    <quantity>1</quantity>
    <price>29.95</price>
  </item> 
  <item>
    <pid>901V8</pid>
    <quantity>2</quantity>
    <price>5.00</price>
  </item> 
</order>');

INSERT INTO product VALUES('
<product pid="24TX98">
  <name>Outdoor paint</name>
  <description>
    <weight>3kg</weight>
    <color>white</color>
  </description>   
</product>');

INSERT INTO product VALUES('
<product pid="901V8">
  <name>Paint brush</name>
  <description>
    <weight>0.1kg</weight>
  </description>   
</product>');

The order table contains one XML document per order, and the product table one XML document per product. Each product has a @pid attribute as a unique identifier, and  orders have /order/item/pid elements to specify which products have been ordered. Naturally, this allows us to perform a join between orders and products.

For example, the following query is a join to retrieve the product information for all the products that have been ordered in order 123:


SELECT product.description
FROM order, product
WHERE XMLEXISTS('$ORDERDETAILS/order[orderNo = 123]')
  AND XMLEXISTS('$DESCRIPTION/product[ @pid/fn:string(.) =
                   $ORDERDETAILS/order/item/pid/fn:string(.) ]');

This query returns one row (one XML document) for each product that is referenced in order 123.

Now, what if you want to combine data from an order document and the associated product documents, and return this information in one relational row per product?  For example, assume you want rows that show the product ID and product weight (from the product documents) as well as the price and quantity of that product in the order document.

In that case you could use an XQuery FLWOR expression to perform the join and combine the product and order information in the XMLTABLE function, as shown in the following query.

Since the product and order tables appear in the FROM clause of the query, the FLWOR expression references their XML columns through the variables $DESCRIPTION and $ORDERDETAILS. The return clause of the FLWOR expression constructs XML fragments that combine the desired elements and attributes from each matching pair of product and order documents. The constructed <result> elements are then input to the COLUMNS clause where they are broken up into relational columns.


SELECT T.*
FROM order, product,
     XMLTABLE('for $o in $ORDERDETAILS/order[orderNo = 123]/item
                for $p in $DESCRIPTION/product
                where $p/@pid/fn:string(.) = $o/pid/fn:string(.)
                return
                    <result>
                        {$p/@pid}
                        {$p/description/weight}
                        {$o/quantity}
                        {$o/price}
                    </result>'
         COLUMNS
             prodid VARCHAR(15)  PATH '@pid',
             weight VARCHAR(5)   PATH 'weight',
             qty    INTEGER      PATH 'quantity',
             price  DECIMAL(6,2) PATH 'price')
         AS T;

   
PRODID          WEIGHT QTY         PRICE
--------------- ------ ----------- --------
24TX98          3kg              1    29.95
901V8           0.1kg            2     5.00

  2 record(s) selected.

It is worthwhile noting that you can produce the same result set without the use of FLWOR expressions, as shown in the next query. This query uses two XMLTABLE function, one for the desired order information and one for the desired product information. The key trick is that the two XMLTABLE function are joined on the product ID using the predicate [@pid = $p]. Without this predicate the two XMLTABLE functions would produce a Cartesian product, which is not desired.


SELECT T2.prodid, T2.weight, T1.qty, T1.price
FROM order, product,
     XMLTABLE('$ORDERDETAILS/order/item'
        COLUMNS
           prodid VARCHAR(15)  PATH 'pid',
           qty    INTEGER      PATH 'quantity',
           price  DECIMAL(6,2) PATH 'price')
     AS T1,
     XMLTABLE('$DESCRIPTION/product[@pid = $p]'
                                  PASSING T1.prodid AS "p"
        COLUMNS
           prodid VARCHAR(15)  PATH '@pid',
           weight VARCHAR(5)   PATH 'description/weight')
     AS T2;

 
                  
PRODID          WEIGHT QTY         PRICE
--------------- ------ ----------- --------
24TX98          3kg              1    29.95
901V8           0.1kg            2     5.00

  2 record(s) selected.

The join predicate between the two XMLTABLE functions can also be placed in the SQL WHERE clause:


SELECT T2.prodid, T2.weight, T1.qty, T1.price
FROM order, product,
     XMLTABLE('$ORDERDETAILS/order/item'
        COLUMNS
           prodid VARCHAR(15)  PATH 'pid',
           qty    INTEGER      PATH 'quantity',
           price  DECIMAL(6,2) PATH 'price')
     AS T1,
     XMLTABLE('$DESCRIPTION/product'
        COLUMNS
           prodid VARCHAR(15)  PATH '@pid',
           weight VARCHAR(5)   PATH 'description/weight')
     AS T2
WHERE T1.prodid = T2.prodid;       

However, this query applies the join predicate after the XMLTABLE functions have produced their rows, which can be slower than the previous query where the predicate was in the XMLTABLE function itself.

You can find many more examples of the XMLTABLE function, XML joins, and joins between XML and relational columns in Chapter 9 of the DB2 pureXML Cookbook.

About these ads

2 Responses to “Advanced SQL/XML: Joins and FLWOR Expressions in the XMLTABLE Function”

  1. haris Says:

    Hi,

    Thanks to write such great example.

    Anyway I don’t find any other [xml] database can perform such task except DB2. XQuery+SQL+TEXT INDEX+Stored Proc.

    I think I will explore your blog deeper.


  2. Thanks for your comment. I hope you find this blog useful.


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: