Monday, July 11, 2011

XQuery: Variable-based step in XPath expression

Recently I was asked how parts of an XPath expression can be passed into an XQuery in DB2. For performance, it is - of course - best to know all the steps when compiling a query. Let's look into the options by introducing an example:

Imagine a document where we have an element "greeting" inside either "b" or "c":

We want to search for either /a/b/greeting or /a/c/greeting and pass in the "b" or "c" as variable "qt" (query tag).

One option is to compose the entire statement inside the application by concatenating the query string, then executing it as dynamic SQL.

If the variable needs to be processed as part of a stored procedure, the query string could be composed inside the procedure, then prepared and executed using a cursor. The Information Center has a good example for such a procedure and XQuery for this second option.

A third option would be to process the variable as part of the XPath expression itself within XMLQUERY, XMLEXISTS, or XMLTABLE (XMLEXISTS shown):

XMLEXISTS('$DOC/a/*[local-name() eq $qt]/greeting' PASSING (cast ? as varchar(60)) as "qt")

We are using the XPath function fn:local-name() to access the element name and then comparing it against the passed in variable. Also note how we can use a parameter marker with our XQuery/XPath expression. The above works with namespaces, too. In that case use something like "/nsPrefix:*[local-name() eq $qt]/" in your XPath expression.