The XMLTABLE function, and a case where a left outer join can help…

April 12, 2010

If you are or will be using XML in a relational database, chances are that you will be using SQL/XML to query XML and relational data in an integrated manner. Most XML applications are not black or white, meaning they do not use XML exclusively without any relationship to any current or existing relational data. More often than not, XML documents somehow relate to some structured data for which the relational data model might be the better choice. This makes the combined management of XML documents and relational data in a single database -often even in a single table!- so appealing.

One of the most popular and most versatile functions in the SQL/XML language standard is the XMLTABLE function. Let’s look a simple example.

First, let’s a create a simple database table that contains a mix of relational data and XML:

create table products(id integer, name varchar(20), details XML);

insert into products
values(1, 'Snow Shovel',
'<product>
  <fullname>Snow Shovel, Deluxe Edition</fullname>
  <category>S5-D</category>
  <color>Silver</color>
</product>');

insert into products
values(2, 'Gloves',
'<product>
  <category>K16-F</category>
  <color>Blue</color>
  <size range="Medium">7</size>
</product>');

Now assume we want to retrieve the id, name, category, color, and size for some or all of the products. We can issue a SQL/XML query such as the following:

select id, name, T.*
from products,
  XMLTABLE('$DETAILS/product'
    COLUMNS
      cat   VARCHAR(10) PATH 'category',
      color VARCHAR(10) PATH 'color',
      size  VARCHAR(10) PATH 'size/@range') as T;

ID          NAME                 CAT        COLOR      SIZE
----------- -------------------- ---------- ---------- ----------
 1          Snow Shovel          S5-D       Silver     -
 2          Gloves               K16-F      Blue       Medium

 2 record(s) selected.

The XMLTABLE function in the “from” clause is a table function that produces zero, one, or multiple rows for each XML document. In this example it produces one row for each XML document in the “product” table. Implicitly there is an inner join between the XMLTABLE function and the “product” table.

Now what happens if there is a row whose XML column is NULL? Let’s insert such a row and repeat the previous query:

insert into products values(3, 'Ice Scraper', NULL);

select id, name, T.*
from products,
  XMLTABLE('$DETAILS/product'
    COLUMNS
      cat   VARCHAR(10) PATH 'category',
      color VARCHAR(10) PATH 'color',
      size  VARCHAR(10) PATH 'size/@range') as T;

ID          NAME                 CAT        COLOR      SIZE
----------- -------------------- ---------- ---------- ----------
 1          Snow Shovel          S5-D       Silver     -
 2          Gloves               K16-F      Blue       Medium

 2 record(s) selected.

You may be surprised that the ID and NAME of the newly inserted product do not show up in this query result. A corresponding row is missing from the result set, because for this row there is no XML document on the XML-side of the inner join.

What we need in this case is a left outer join, so that the relational columns from the base table are returned even if the XML column (the other side of the implicit join) is NULL:

select id, name, T.*
from products LEFT OUTER JOIN
 XMLTABLE('$DETAILS/product'
   COLUMNS
     cat   VARCHAR(10) PATH 'category',
     color VARCHAR(10) PATH 'color',
     size  VARCHAR(10) PATH 'size/@range') as T ON 1=1;

ID          NAME                 CAT        COLOR      SIZE
----------- -------------------- ---------- ---------- ----------
 1          Snow Shovel          S5-D       Silver     -
 2          Gloves               K16-F      Blue       Medium
 3          Ice Scraper          -          -          -

 3 record(s) selected.

The join condition of this left outer join is trivial: 1=1, which always evaluates to true. It means that we want this left outer join to be applied to every row in this table.

You can read more about the XMLTABLE function in the developerWorks article “XMLTABLE By Example“, or in Chapter 7 and Chapter 9 of the DB2 pureXML Cookbook.

The XMLTABLE function is part of the SQL standard. DB2 supports it, Oracle does too, but as far as I know the XMLTABLE function is not available in SQL Server 2008.

About these ads

7 Responses to “The XMLTABLE function, and a case where a left outer join can help…”


  1. I have been reading a few of your posts and have enjoyed it. Keep it up

  2. Venu Says:

    Hi Matt,
    I am using the XMLTABLE function to retrieve any XML column in SQL DB2.
    In my case, I receive the xml tag name thru calling program in the middle of the process. I want to use the dynamic value while retrieving the column in XMLTABLE.

    Here is my SQL,
    SELECT BUS_INDV_IND
    INTO :HV-MC0201B-BUS-INDV-IND
    FROM FILING A,
    XMLTABLE (‘$a/ReturnCompositionBO/composition/forms/*’
    PASSING A.FILING_XML AS “a”
    COLUMNS
    FIELD-1 char(30) path
    ‘XXXXXXXXXXX/@computed’) AS T
    WHERE BUS_INDV_IND = :HV-RQ-BUS-INDV-IND
    AND INT_TP_ID = :HV-RQ-INT-TP-ID

    can I able to pass some value in the place of XXXXXXXXX instead of hard coding the xml tagname.

    Thank you.
    Regards,
    Venu.


  3. Hi Venu,
    no, this is not directly possible. Much a like a table name or column name, the element names are compiled into the access plan of the query and cannot be a parameter.

    However, you can use predicates on the name of an element via the XPath function name(), like this:

    SELECT BUS_INDV_IND INTO :HV-MC0201B-BUS-INDV-IND,
    FIELD1 INTO :HV-F1
    FROM FILING A,
    XMLTABLE (‘$a/ReturnCompositionBO/composition/forms/*/*[name() = $x]‘
    PASSING A.FILING_XML AS “a”, cast( :HV-FIELD AS VARCHAR(32) ) AS “x”
    COLUMNS
    FIELD1 char(30) path ‘@computed’) AS T
    WHERE BUS_INDV_IND = :HV-RQ-BUS-INDV-IND
    AND INT_TP_ID = :HV-RQ-INT-TP-ID

    Unfortunately, the SQL standard does not allow for parameters in the column-generating XPath expressions. Therefore the query above uses in the row-generating expression.

    The other option is that you can compose your query as a string where the XXXXXXXXX is a string variable. Each time you get a new value for XXXXXXXXX you would prepare the query before executing it.

    Does that help?

    Matthias

  4. Gyan Says:

    Hi,

    I need a help in how can we use a XML cloumns variable to update some table in z/OS.

    I mean is it possible to use XML table to update so other table similar to what we do in insert.


    • Hi Gyan,

      thanks for your comment. I’m not 100% sure what you are asking for. You say “similar to what we do in insert” but I don’t know what exactly you are doing in your INSERT statements. Do you mean shredding of XML to relational columns by using the XMLTABLE function in INSERT statements?

      And do you now want to use the XMLTABLE function in an UPDATE statement to update relational columns with values that the XMLTABLE function extracts from a document that is passed in as a parameter?

      If you can clarify what you need then we can look for suitable solution.

      Thanks,

      Matthias

      • Gyan Says:

        Hi Matthias,

        Thanks for reply back so soon. What i meant really is as follows.

        Do you mean shredding of XML to relational columns by using the XMLTABLE function in INSERT statements? – Yes.

        And do you now want to use the XMLTABLE function in an UPDATE statement to update relational columns with values that the XMLTABLE function extracts from a document that is passed in as a parameter? – Yes.


      • Thanks, Gyan. I just published another blog post that tries to answer your question with a few examples. Take a look and let me know if that helps:
        Using the XMLTABLE function in UPDATE and MERGE statements“.


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: