An Even More Secret Advice About db2-fn:sqlquery() - Casting in XQuery

Fred Sobotka has a blog posting about how to make parameter passing from XQuery to SQL (and back) work. It details on the conditions to be met:

For DB2 9.5, Fix Pack 3 or newer is a must, due to a problem with SQL and XMLCAST. This means that folks running DB2 Express-C without the FTL (which is currently the only way to bring Express-C up to Fix Pack 3) will be thwarted by that APAR until IBM issues a (long overdue) refresh.

XML data being passed into a SQL UDF must be cast into the appropriate SQL data type for that UDF.

The final output of the SQL UDF being called within db2-fn:sqlquery must be cast back to XML, either by surrounding it with an XMLTEXT function, or by passing it into XMLCAST and specifying a type of XML.

Let me comment more on it and show you how you can work around prerequisite 1) a little bit. DB2 9.5 is still a must because then the parameter passing from XQuery to SQL was introduced. FP3 is NOT required in my experience as the query below shows. The trick is to additionally cast in XQuery (see the use of xs:string()).

xquery
<people>
{for $a in db2-fn:xmlcolumn('XMLTBL.XMLCOL')
return <person
name="{$a/name/text()}"
soundex="{db2-fn:sqlquery('SELECT XMLTEXT(SOUNDEX(CAST(parameter(1) AS CHAR(128))) ) FROM sysibm.sysdummy1', xs:string($a/name/text()))}" />
}
<people>

The other two requirements are due to the typing information that is (not) available. The CAST in SQL (bullet point 2) is necessary to tell SQL how to handle the value in the SQL world. The string could be a VARCHAR(500) or a CHAR(10). The latter would obviously fail given the values passed in. So you better tell SQL what values to expect. Making XML from the output (requirement #3) is also a must because db2-fn:sqlquery() by definition returns a sequence of XML values. I hope this gives some more insight and helps those on DB2 9.5 prior to FP3.

BTW: I tested this on my DB2 Express-C 9.5 (shows as "9.5.0" when connecting).