Casting of XMLQUERY results to non-XML types

To return the results of the XMLQUERY function to the SQL context for further processing, such as for comparison or ordering operations, cast the XML value that is returned to a compatible SQL type. You can use the XMLCAST specification to cast between XML and non-XML values.

Note:

You can cast the result of XMLQUERY to an SQL data type only when the XQuery expression specified in XMLQUERY returns a sequence that contains one item that has been atomized.
In a non-UTF-8 database, casting the result of XMLQUERY to an SQL data type causes code page conversion to occur as the returned value is converted from an internal UTF-8 encoding to the database code page. Any code points in the returned value that are not part of the database code page will be replaced by substitution characters. The introduction of substitution characters might cause unexpected behavior for comparisons between XML and non-XML values, so care should be taken to ensure that the stored XML data contains only code points that are included in the database code page.

Example: Comparing XML values to non-XML values in a query

In the following query, the sequence returned by XMLQUERY is cast from its XML type to a character type, so that it can be compared with the NAME column of the PRODUCT table. (If the XML value that results from XMLQUERY is not a serialized string, then the XMLCAST operation could fail.)

SELECT R.Pid
FROM PURCHASEORDER P, PRODUCT R
WHERE R.NAME =
        XMLCAST( XMLQUERY ('$d/PurchaseOrder/item/name'
                           PASSING P.PORDER AS "d") AS VARCHAR(128))

Example: Ordering by XMLQUERY results

In the following query, product IDs are returned in an order sorted by the value of the <name> element of the product's description, which is stored as an XML document. Because SQL cannot sort on XML values, the sequence must be cast to a value that SQL can order on, in this case, character.

SELECT Pid
FROM PRODUCT
ORDER BY XMLCAST(XMLQUERY ('$d/product/description/name'
                           PASSING DESCRIPTION AS "d") AS VARCHAR(128))