August 14, 2012
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:
- XQuery FLWOR expression
- XQuery FLWOR expression within an SQL VALUES clause
- SQL/XML query with the XMLQUERY function
- 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.
This is an add-on to my previous post on Using the XMLTABLE function in UPDATE and MERGE statements.
I had tested all the examples in that previous post in DB2 for Linux, UNIX, and Windows but overlooked that DB2 for z/OS currently has a restriction for the MERGE statement.
The MERGE statement in DB2 for z/OS expects a VALUES clause to provide the data that is to be merged, not an arbitrary sub-select. For details, see:
As a result, the MERGE example at the end of my previous post needs to be adjusted for DB2 z/OS. For example, you can use an SQL procedure to loop over the rows produced by the XMLTABLE query and feed these rows into the MERGE statement:
CREATE PROCEDURE XMLMERGE(IN P_CHANGE XML) LANGUAGE SQL BEGIN DECLARE ID INT; DECLARE X INTEGER; DECLARE Y VARCHAR(20); DECLARE SQLCODE INT; DECLARE C1 CURSOR FOR SELECT ID, X, Y FROM XMLTABLE('$DOC/root/mydata' PASSING P_CHANGE AS "DOC" COLUMNS id INTEGER PATH '@id', x INTEGER PATH 'elem1', y VARCHAR(20) PATH 'elem2') T; OPEN C1; LOOP1: LOOP FETCH C1 INTO ID, X, Y; IF SQLCODE <> 0 THEN LEAVE LOOP1; END IF; MERGE INTO RELTABLE R USING (VALUES(ID, X, Y)) AS N(ID, X, Y) ON (R.ID = N.ID) WHEN MATCHED THEN UPDATE SET R.X = X, R.Y = Y WHEN NOT MATCHED THEN INSERT VALUES(ID, X, Y); END LOOP LOOP1; CLOSE C1; END
Thanks to my colleague Guogen Zhang for providing this sample solution.