Conversion of the XMLType data type

In OceanBase Database, the XMLType data type can be implicitly converted to the VARCHAR data type.

Note that it is not recommended to use XMLType as a string. OceanBase Database stores XMLType data in binary format, which is optimized for XPath queries and removes the original formatting information provided by users, such as line breaks, indentation, and whitespace in XML element that are not required by the XML specification. 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 the XMLType data type and other data types.

The XMLType data type can be implicitly or explicitly converted to the VARCHAR, VARCHAR2, NVARCHAR2, and CHAR data types using the CAST function. However, these data types cannot be converted to the XMLType data type 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 the XMLType data type, see the following topics:

XMLCAST()

XMLSERIALIZE()

EXTRACTVALUE()

XMLPARSE()

GETCLOBVAL

GETSTRINGVAL