DB2 10.5 for Linux, UNIX, and Windows

Constant and parameter marker passing to XMLEXISTS and XMLQUERY

The XMLEXISTS predicate and the XMLQUERY scalar function execute XQuery expressions from within an SQL statement. Use constants and parameter markers to pass data from the SQL statement to variables in an XQuery expression executed within the SQL statement.

XQuery variables can be specified as part of the XQuery expression in XMLEXISTS and XMLQUERY. Values are passed into these variables through the passing clause. These values are SQL expressions. Because the values passed to the XQuery expression are non-XML values, they must be cast, either implicitly or explicitly, to types supported by DB2® XQuery.

The method of passing constants and parameter markers to XMLQUERY is the same as that of XMLEXISTS, however, the XMLEXISTS usage is more common. This is because parameterized predicates in XMLQUERY, when used in SELECT clauses, do not eliminate any rows from the result set. Instead, the predicates are used to determine which fragments of a document are returned. To actually eliminate rows from a result set, the XMLEXISTS predicate should be used in the WHERE clause. Rows that contain empty sequences are therefore not returned as part of the result set. The examples discussed here show this more common usage with XMLEXISTS.

Example: Implicit casting

In the following query, the SQL character string constant 'Aurora', which is not an XML type, is implicitly cast to an XML type in the XMLEXISTS predicate. Following the implicit cast, the constant has the XML schema subtype of xs:string, and is bound to the variable $cityName. This constant can then be used in the predicate of the XQuery expression.

SELECT XMLQUERY ('$d/customerinfo/addr' passing c.INFO as "d")
FROM Customer as c
WHERE XMLEXISTS('$d//addr[city=$cityName]'
                 passing c.INFO as "d",
                 'Aurora' AS "cityName")

Example: Explicit casting

In the following query, the parameter marker must be explicitly cast to a data type because the type of the parameter marker cannot be determined. The parameter marker that is explicitly cast to an SQL VARCHAR type is then implicitly cast to the xs:string XML schema type.

SELECT XMLQUERY ('$d/customerinfo/addr' passing c.INFO as "d")
FROM Customer as c
WHERE XMLEXISTS('$d//addr[city=$cityName]'
                 passing c.INFO as "d",
                 CAST (? AS VARCHAR(128)) AS "cityName")