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).
-
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)