Purpose
This function extracts the content of an XML document (excluding element names) and converts it to a CHAR, VARCHAR, VARCHAR2, NUMBER, FLOAT, BLOB, or CLOB data type, or any datetime data type.
Syntax
XMLCAST ( value_expression AS datatype )
Parameters
| Field | Description |
|---|---|
| value_expression | Specifies the content to be parsed as a valid XML fragment. |
| AS datatype | Specifies the return type, which can be CHAR, VARCHAR, VARCHAR2, NUMBER, FLOAT, BLOB, or CLOB, or any datetime data type. |
Return type
Returns CHAR, VARCHAR, VARCHAR2, NUMBER, FLOAT, BLOB, or CLOB, or any datetime data type.
Examples
# Convert to CHAR
obclient> SELECT XMLCAST(XMLPARSE(DOCUMENT '<?xml version="1.0"?><a>a</a>') AS CHAR) FROM DUAL;
+-----------------------------------------------------------------+
| XMLCAST(XMLPARSE(DOCUMENT'<?XMLVERSION="1.0"?><A>A</A>')ASCHAR) |
+-----------------------------------------------------------------+
| a |
+-----------------------------------------------------------------+
1 row in set
# Convert to VARCHAR2
obclient> SELECT XMLCAST(XMLPARSE(DOCUMENT '<?xml version="1.0"?><a>aaaaaa</a>') AS VARCHAR2(5)) FROM DUAL;
+-----------------------------------------------------------------------------+
| XMLCAST(XMLPARSE(DOCUMENT'<?XMLVERSION="1.0"?><A>AAAAAA</A>')ASVARCHAR2(5)) |
+-----------------------------------------------------------------------------+
| aaaaa |
+-----------------------------------------------------------------------------+
1 row in set
# Convert to BLOB
obclient> SELECT XMLCAST(XMLPARSE(DOCUMENT '<?xml version="1.0"?><root><a>1</a><b>23f</b></root>') AS BLOB) FROM DUAL;
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| XMLCAST(XMLPARSE(DOCUMENT'<?XMLVERSION="1.0"?><ROOT><A>1</A><B>23F</B></ROOT>')ASBLOB) |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 123F |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set
# Convert to CLOB
obclient> SELECT XMLCAST(XMLPARSE(DOCUMENT '<?xml version="1.0" encoding="UTF-8"?>
<note>
<to>To</to>
<FROM>Jani</FROM>
<heading>Reminder</heading>
<body>Do not forget me this weekend!</body>
</note>') AS CLOB) RES FROM DUAL;
+----------------------------------------------+
| RES |
+----------------------------------------------+
| ToJaniReminderDo not forget me this weekend! |
+----------------------------------------------+
1 row in set
# Convert to NUMBER
obclient> SELECT XMLCAST(XMLPARSE(CONTENT '<a>123.01</a>') AS NUMBER) FROM DUAL;
+---------------------------------------------------+
| XMLCAST(XMLPARSE(CONTENT'<A>123.01</A>')ASNUMBER) |
+---------------------------------------------------+
| 123.01 |
+---------------------------------------------------+
1 row in set
# Convert to FLOAT
obclient> SELECT XMLCAST(XMLPARSE(CONTENT '<a>123.01</a>') AS FLOAT) FROM DUAL;
+--------------------------------------------------+
| XMLCAST(XMLPARSE(CONTENT'<A>123.01</A>')ASFLOAT) |
+--------------------------------------------------+
| 123.01 |
+--------------------------------------------------+
1 row in set
# Convert to DECIMAL
obclient> SELECT XMLCAST(XMLPARSE(CONTENT '<a>123.01</a>') AS DECIMAL) FROM DUAL;
+----------------------------------------------------+
| XMLCAST(XMLPARSE(CONTENT'<A>123.01</A>')ASDECIMAL) |
+----------------------------------------------------+
| 123 |
+----------------------------------------------------+
1 row in set
# Convert to TIMESTAMP
obclient> SELECT XMLCAST(XMLPARSE(CONTENT '<a>2023-04-01 15:13:00</a>') AS TIMESTAMP) AS RES FROM DUAL;
+----------------------------+
| RES |
+----------------------------+
| 2023-04-01 15:13:00.000000 |
+----------------------------+
1 row in set
# Convert to DATE
obclient> SELECT XMLCAST(XMLPARSE(CONTENT '<a>2023-04-01 15:13:00</a>') AS DATE) AS RES FROM DUAL;
+---------------------+
| RES |
+---------------------+
| 2023-04-01 15:13:00 |
+---------------------+
1 row in set
# Convert to INTERVAL YEAR TO MONTH
obclient> SELECT XMLCAST(XMLPARSE(CONTENT '<a>12-3</a>') AS INTERVAL YEAR(3) TO MONTH) AS RES FROM DUAL;
+---------+
| RES |
+---------+
| +012-03 |
+---------+
1 row in set
# Convert to INTERVAL DAY TO SECOND
obclient> SELECT XMLCAST(XMLPARSE(CONTENT '<a>140 5:12:10.2222222</a>') AS INTERVAL DAY(3) TO SECOND(7)) AS RES FROM DUAL;
+-----------------------+
| RES |
+-----------------------+
| +140 05:12:10.2222222 |
+-----------------------+
1 row in set