Friday, May 8, 2009

Pass it to me, no, wait, to me! (Parameter passing from SQL to XQuery and back)

Many of you know that parameters can be passed from the SQL context to the XQuery context. The functions XMLEXISTS, XMLQUERY, and XMLTABLE all have a "passing" clause that can be used to make constants, columns, or parameters available for XML-related expressions:

WHERE XMLEXISTS('$d/employee[@id=$id]' PASSING doc AS "d", CAST (? AS INTEGER) AS "id")

There were significant enhancements to parameter passing starting from DB2 9.5. Even though it was passed to the field (pun intended) it has not reached everybody. A good starting point to read more about what is possible is the document "Passing parameters between SQL statements and XQuery expressions" in the DB2 Information Center.

Let me start with a simplification that was introduced. It allows to leave out the passing clause for column names. In that case the case-sensitive column name must be used directly in the XQuery expression. The above example could be simplified as follows:

WHERE XMLEXISTS('$DOC/employee[@id=$id]' CAST (? AS INTEGER) AS "id")

Only the parameter marker is passed in now, the column name "DOC" is used instead of the previously named variable "doc". So much for the SQL to XQuery passing.

In some cases, you also want to transfer some information from the XQuery context to SQL. This is not a standard XQuery problem because DB2 with its pureXML functionality is a bilingual, hybrid database system and unique in its XML support. So far you could already evaluate SQL expressions from within XQuery by calling the db2-fn:sqlquery function. This function has been extended to allow you to specify parameters and a function named PARAMETER() was added to SQL to receive those values in the SQL context.

values(xmlquery('for $p in db2-fn:sqlquery("select xmlrow(tabschema, tabname) from syscat.tables where tabschema=PARAMETER(1)","HENRIK") return $p'))

In the above example we return as XML the fully qualified table names for all tables in the schema "HENRIK". Try it yourself by replacing "HENRIK" with your username or "SYSIBM". You notice how PARAMETER(1) fetches the passed value which was - separated by a comma - specified as argument of the sqlquery() function.

Some time has passed since I started this blog entry. Have a nice weekend!