OceanBase Database supports implicit conversion between the XMLType data type and the VARCHAR data type.
Note that we do not recommend that you use XMLType as a string. OceanBase Database stores XMLType data in binary format. This binary format optimizes XPath queries and removes the original format information of the input, such as the line breaks, indentation, and ignorable whitespace of XML element. If you want to adjust the output format of XMLType data, you can use the XMLSERIALIZE() function.
The conversion rules between XMLType and other data types are as follows:
| Source data type | Target data type | Implicit conversion | Explicit conversion |
|---|---|---|---|
| XMLType | VARCHAR2 | XMLType data can be inserted into a VARCHAR2 column and can be processed by functions such as SUBSTR(). |
Supported. You can use CAST(XMLType as VARCHAR2(n)) or XMLSERIALIZE(). |
| XMLType | CLOB | Not supported | Not supported, but you can use XMLSERIALIZE() to convert XMLType data into the CLOB data type. |
| XMLType | BLOB | Not supported | Not supported, but you can use XMLSERIALIZE() to convert XMLType data into the BLOB data type. |
| XMLType | Other primitive data types | Not supported | Not supported, but you can use XMLCAST() to convert the text in an XML document into other primitive data types. |
| VARCHAR2 | XMLType | You can directly insert VARCHAR2 data into an XMLType column, but you cannot directly use a function with XMLType as the input parameter, such as EXTRACTVALUE(). |
Not supported |
| CLOB | XMLType | Not supported | Not supported |
| BLOB | XMLType | Not supported | Not supported |
| Other primitive data types | XMLType | Not supported | Not supported |
XMLType data can be implicitly or explicitly converted to the VARCHAR, VARCHAR2, NVARCHAR2, and CHAR data types by using the CAST function. However, these data types cannot be converted 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:
