Convert XMLType data

In OceanBase Database, the conversion between XMLType and VARCHAR is similar to implicit conversion.

We recommend that you do not use XMLType data as a string. In OceanBase Database, XMLType data is stored in binary format. This optimizes XPath queries while removing the original format of user input, such as XML element line breaks, indentation, and ignorable spaces that conform to XML specifications. If you want to specify the output format of XMLType data, use the XMLSERIALIZE() function.

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

You can implicitly convert XMLType data into VARCHAR, VARCHAR2, NVARCHAR2, and CHAR data types or use the CAST() function for explicit conversion. However, you cannot use the CAST() function to convert data of these string types into XMLType data. Here are some conversion examples:

# Explicit conversion
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

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