Native XML Database

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.