Conversion of the XMLType data type

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

Note that we do not recommend that you use XMLType as a string. OceanBase Database stores XMLType data in binary format. This format optimizes XPath queries and removes the original formatting information of the input, such as the line breaks, indentation, and ignorable whitespace of XML elements. If you want to adjust the output format of XMLType data, you can use the XMLSERIALIZE() function.

The conversion rules between the XMLType data type and other data types are as follows:

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

XMLCAST()

XMLSERIALIZE()

EXTRACTVALUE()

XMLPARSE()

GETCLOBVAL

GETSTRINGVAL