Result set cardinalities when querying repeating XML elements

Recently I received some questions about the result sets when querying XML, and especially when querying repeating elements that occur more than once per document.

As it turns out, the same logical result can be returned in different ways, depending on how you write your XQuery or SQL/XML query.

Let’s look at a simple table with two XML documents, and then at several different queries against that data. Here is the sample data:

create table testtable(doc XML);

insert into testtable(doc)
 values ('<a id="1">
             <b>1</b>
             <b>2</b>
          </a>');

insert into testtable(doc)
 values ('<a id="2">
             <b>3</b>
             <b>4</b>
             <b>5</b>
         </a>');

Now assume we want to return all the <b> elements from these two documents. You can write such a query in several different ways, each returning the same <b> elements in a slightly different way:

1. XQuery FLWOR expression
2. XQuery FLWOR expression within an SQL VALUES clause
3. SQL/XML query with the XMLQUERY function
4. SQL/XML query with the XMLTABLE function

Let’s look at each of these options in turn.

1. XQuery FLWOR expression

The fist example is a simple XQuery FLWOR expression. It iterates over the path /a/b in all documents and returns the <b> elements one by one. The result is a sequence of 5 elements, and each is returned as a single item in the result set:

xquery
 for $b in db2-fn:xmlcolumn("TESTTABLE.DOC")/a/b
 return $b';

<b>1</b>
<b>2</b>
<b>3</b>
<b>4</b>
<b>5</b>

5 record(s) selected.

2. XQuery FLWOR expression in an SQL VALUES clause

If you enclose the same FLWOR expression in an SQL VALUES clause then the same XML elements are returned in a different format.

In this example, the VALUES clause produces a single value. The SQL type of that value is the XML data type and the value itself is a sequence of 5 elements. The entire sequence is returned as a single value of type XML:

values(xmlquery('
 for $b in db2-fn:xmlcolumn("TESTTABLE.DOC")/a/b
 return $b'));

<b>1</b><b>2</b><b>3</b><b>4</b><b>5</b>

1 record(s) selected.

3. SQL/XML query with the XMLQUERY function

You could also write an SQL SELECT statement and include your XQuery or XPath expression in an XMLQUERY function.

Note that the XMLQUERY function is a scalar function, i.e. it returns one result value of type XML for each row that it is applied to. Since our sample table contains two rows, the following query returns two results values of type XML. The first value is a sequence with all the <b> elements from the first document, and the second value is the sequence of all <b> elements from the second document:

SELECT xmlquery('for $b in $DOC/a/b return $b') as col1
FROM testtable;

COL1
----------------------------
<b>1</b><b>2</b>
<b>3</b><b>4</b><b>5</b>

2 record(s) selected.


-- same result with a simple XPath:
SELECT xmlquery('$DOC/a/b') as col1
FROM testtable;

COL1
----------------------------
<b>1</b><b>2</b>
<b>3</b><b>4</b><b>5</b>

2 record(s) selected.

The potential benefit of this result format is that you now exactly which <b> elements came from the same input document. If you prefer to return each <b> element as a separate item, use the XMLTABLE function.

4. SQL/XML query with the XMLTABLE function

The XMLTABLE function is not a scalar function, it’s a table function. This means that it returns a set of result rows for each input document. More precisely, it return one result row for each item that is produced buy the row-generating expression /a/b:

-- return a column of type XML:
SELECT X.*
FROM testtable,
 XMLTABLE('$DOC/a/b'
 COLUMNS
 col1 XML PATH '.') as X;

COL1
----------------------------
<b>1</b>
<b>2</b>
<b>3</b>
<b>4</b>
<b>5</b>

5 record(s) selected.

-- return a column of type integer:
SELECT X.*
FROM testtable,
 XMLTABLE('$DOC/a/b'
 COLUMNS
 col1 INTEGER PATH '.') as X;

COL1
--------
 1
 2
 3
 4
 5

5 record(s) selected.

The result sets in all of these example make sense and are consistent with SQL semantics. You can chose the shape of your query results and write your queries accordingly.