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!


Per Anell said...

Thanks for this post, it was of great help to me. The problem I have is that the XML I'm trying to parse has a node with only attributes, ie no value and no sub-elements, and DB2 doesnt like that (I get Error QName=err:SENR0001. SQLSTATE=2200W), which on the DB2 website is explained as:
The sequence to be serialized contains an item that is an attribute node. Error QName=err:SENR0001.

A request was made to serialize an XML value. The XML value is a sequence containing an item that is an XQuery attribute node. There is no defined sequence normalization for an attribute node as an item in a sequence and therefore the sequence cannot be serialized.

The statement cannot be processed.
User response

Remove any items in the sequence containing just an attribute node or do not attempt to serialize the sequence.
Do you know of any workaround to do this using XMLQUERY or do I have to resort to text parsing?

Per Anell

Henrik Loeser said...

Hi Per,

depending on what you want to do, a common workaround is to wrap to result into a constructed element.


Let me know what you need and I can comment with more details.


Per Anell said...

I have an XML document (saved in a BLOB in DB2) that looks like below (removed some additional nodes).

I'm able to parse this into an XML document using xmlparse(document BLOB) but then I want to use XMLQuery to extract the value of one or more of the attributes from the XML document, eg the AdditionalInfo/Order/@paymentMethodId attribute, and it's giving me the error message cited above. How would I use a constructor to do this?

Henrik Loeser said...

Your example doesn't show up because of formatting. Let me show you three queries to demonstrate the problem and 2 ways around:

xquery let $i:=<foo id="1" att1="att" /> return $i/@att1

xquery let $i:=<foo id="1" att1="att" /> return <res>{$i/@att1}</res>

xquery let $i:=<foo id="1" att1="att" /> return xs:string($i/@att1)

Per Anell said...

Here's the XML again:

I want to extract eg the paymentMethodId attribute in the AdditionalInfo/Order node.

Per Anell said...

And this is the SQL I use when I get the error message:

SELECT XMLQUERY('$IncomingRequest/AdditionalInfo/Order/@paymentMethodId' PASSING xmlparse(document '

as "IncomingRequest") FROM SYSIBM.SYSDUMMY1;

Per Anell said...

So I was able to do this by using a simple XMLCAST of the XMLQUERY result:

XMLCAST(XMLQUERY('$IncomingRequest/*:AdditionalInfo/*:Order/@paymentMethodId' PASSING xmlparse(document TO.T_ADDITIONAL_INFO) as "IncomingRequest") as varchar(128)) as "paymentMethodId"

Where TO.T_ADDITIONAL_INFO is a string (containing XML) defined as VARCHAR(4500).


Related Posts with Thumbnails