Passing parameters from XQuery to SQL

From within an XQuery expression, the db2-fn:sqlquery function executes an SQL fullselect that retrieves an XML node sequence. When using db2-fn:sqlquery, use the PARAMETER function to reference the passed data from the XQuery expression to the SQL fullselect statement specified by db2-fn:sqlquery.

Using the PARAMETER function, parameters can be specified as part of an SQL fullselect expression in db2-fn:sqlquery. If you use PARAMETER functions in the db2-fn:sqlquery invocation, you must also specify XQuery expressions that will be used by the PARAMETER functions. During the processing of the SQL fullselect, each PARAMETER function call is replaced with the result value of the corresponding XQuery expression in the db2-fn:sqlquery function invocation. The value supplied by the PARAMETER function can be referenced multiple times within the same SQL statement.

The XQuery expressions that are part of the db2-fn:sqlquery function invocation return a value. Because the values passed to the fullselect are XML values, they must be cast, either implicitly or explicitly, to types supported by Db2® SQL. Refer to the db2-fn:sqlquery documentation and the documentation on casting between data types for more information on supported casts.

Example: Passing a parameter to db2-fn:sqlquery

The following example is an XQuery expression that uses db2-fn:sqlquery. During processing of the db2-fn:sqlquery function, both references to parameter(1) return the value of the order date attribute $po/@OrderDate.

When run against the Db2 SAMPLE database the XQuery expression returns the purchase ID, part ID, and the purchase date for all the parts sold within the promotion dates.

xquery 
for $po in db2-fn:xmlcolumn('PURCHASEORDER.PORDER')/PurchaseOrder,
  $item in $po/item/partid 
for $p in db2-fn:sqlquery( 
  "select description 
  from product 
  where promostart < parameter(1)
  and
  promoend  >  parameter(1)", 
  $po/@OrderDate )
where $p//@pid = $item 
return 
<RESULT>
  <PoNum>{data($po/@PoNum)}</PoNum>
  <PartID>{data($item)} </PartID>
  <PoDate>{data($po/@OrderDate)}</PoDate>
</RESULT>