Tuesday, January 31, 2012

XMLQuery: The document that never was and the intermediate step error

Recently I got a help request by email stating that a snippet of SQL code didn't work. There wasn't much detail included about what went wrong, but to me it boiled down to a SELECT statement using a XMLQUERY function, all inside a SQL-based function. I had some minutes, so I gave it a try.

Many people start developing code with an easy skeleton, then extending it and adding the more complex stuff. Why not do the same for an XQuery?

db2 => values xmlquery('$FOO' passing '<a><b>small test</b></a>' as "FOO")
------
<a><b>small test</b></a>

The above the scaled down version of the easy skeleton. It seems to work well. Now let's extend it:

db2 => values xmlquery('$FOO/a/b' passing '<a><b>small test</b></a>' as "FOO")
SQL16011N  The result of an intermediate step expression in an XQuery path expression contains an atomic value. Error QName=err:XPTY0019.  SQLSTATE=10507

Hm, that doesn't look expected, or does it? Looking into the XQuery and DB2 error description doesn't help either if you are dealing with such problems. The only thing that really helps here is experience. And it says: Remember to process well-formed documents!
In the above statements we pass a string in for XQuery processing. The simple skeleton works because we just return the string. However, the XQuery processor refuses to apply step expressions ("a" and "b" in our case) to the value "$FOO" because it is NOT an XML document, but a string. Hence the error message with the hint "contains an atomic value" (the string). What can we do? We can pass in a valid XML document:

db2 => values xmlquery('$FOO/a/b' passing xmlparse(document '<a><b>small test</b></a>') as "FOO")
------
<b>small test</b>

To produce an XML document out of a string we just use the XMLPARSE function as shown. And suddenly everything seems clear. Remember to process well-formed documents!