Conversion of XMLType data

OceanBase Database supports implicit conversion between the XMLType and VARCHAR data types.

Note that it is not recommended to use XMLType as a string. OceanBase Database stores XMLType data in binary format internally. This format optimizes XPath queries and removes the original formatting information from the user input, such as line breaks, indentation, and whitespace in XML elements that are insignificant according to XML specifications. If you want to adjust the output format of XMLType data, you can use the XMLSERIALIZE() function.

The following table describes the conversion rules between XMLType data and other data types.

XMLType data can be implicitly or explicitly converted to VARCHAR, VARCHAR2, NVARCHAR2, and CHAR using the CAST function. However, these types cannot be converted to XMLType using the CAST() function. Here are some conversion examples:

# Explicit conversion examples
SELECT CAST(XMLPARSE(DOCUMENT '<a>123</a>') AS VARCHAR(100)) FROM DUAL;

SELECT CAST(XMLPARSE(DOCUMENT '<a>123</a>') AS VARCHAR2(100)) FROM DUAL;

SELECT CAST(XMLPARSE(DOCUMENT '<a>123</a>') AS NVARCHAR(100)) FROM DUAL;

SELECT CAST(XMLPARSE(DOCUMENT '<a>123</a>') AS CHAR(100)) FROM DUAL;
SELECT CAST(XMLPARSE(DOCUMENT '<a>123</a>') AS VARCHAR2(2)) FROM DUAL;

# Implicit conversion examples

SELECT UPPER(XMLPARSE(DOCUMENT'<a>123</a>')) FROM DUAL;
SELECT LOWER(XMLPARSE(DOCUMENT'<a>123</a>')) FROM DUAL;
SELECT SUBSTR(XMLPARSE(DOCUMENT'<a>123</a>')) FROM DUAL;
SELECT LENGTH(XMLPARSE(DOCUMENT'<a>123</a>')) FROM DUAL;

References

For more information about functions related to the conversion of XMLType data, see the following topics:

XMLCAST()

XMLSERIALIZE()

EXTRACTVALUE()

XMLPARSE()

GETCLOBVAL

GETSTRINGVAL