DB2 10.5 for Linux, UNIX, and Windows

XMLCAST specification

The XMLCAST specification returns the cast operand (the first operand) cast to the type specified by the data type. XMLCAST supports casts involving XML values, including conversions between non-XML data types and the XML data type. If the cast is not supported, an error is returned (SQLSTATE 22003).


xmlcast-specification

|--XMLCAST--(--+-expression-------+--AS--data-type--)-----------|
               +-NULL-------------+                     
               '-parameter-marker-'

expression

If the cast operand is an expression (other than a parameter marker or NULL), the result is the argument value converted to the specified target data type. The expression or the target data type must be the XML data type (SQLSTATE 42846).

NULL

If the cast operand is the keyword NULL, the target data type must be the XML data type (SQLSTATE 42846). The result is a null XML value.

parameter-marker

If the cast operand is a parameter marker, the target data type must be XML (SQLSTATE 42846). A parameter marker is normally considered to be an expression, but is documented separately in this case because it has special meaning. If the cast operand is a parameter marker, the specified data type is considered to be a promise that the replacement will be assignable to the specified (XML) data type (using store assignment). Such a parameter marker is considered to be a typed parameter marker, which is treated like any other typed value for the purpose of function resolution, a describe operation on a select list, or column assignment.

data-type

The name of an existing SQL data type. If the name is not qualified, the SQL path is used to perform data type resolution. If a data type has associated attributes, such as length or precision and scale, these attributes should be included when specifying a value for data-type. CHAR defaults to a length of 1, and DECIMAL defaults to a precision of 5 and a scale of 0 if not specified. Restrictions on the supported data types are based on the specified cast operand.

For a cast operand that is an expression, the supported target data types depend on the data type of the cast operand (source data type).
For a cast operand that is the keyword NULL, the target data type must be XML.
For a cast operand that is a parameter marker, the target data type must be XML.

Note: Support in non-Unicode databases: When XMLCAST is used to convert an XML value to an SQL data type, code page conversion is performed. The encoding of the cast expression is converted from UTF-8 to the database code page. Characters in the original expression that are not present in the database code page are replaced by substitution characters as a result of this conversion.

Examples

Create a null XML value.
```
   XMLCAST(NULL AS XML)
```

Convert a value extracted from an XMLQUERY expression into an INTEGER:

   XMLCAST(XMLQUERY('$m/PRODUCT/QUANTITY'
     PASSING xmlcol AS "m") AS INTEGER)

Convert a value extracted from an XMLQUERY expression into a varying-length character string:

   XMLCAST(XMLQUERY('$m/PRODUCT/ADD-TIMESTAMP'
     PASSING xmlcol AS "m") AS VARCHAR(30))

Convert a value extracted from an SQL scalar subquery into an XML value.

   XMLCAST((SELECT quantity FROM product AS p
     WHERE p.id = 1077) AS XML)