Purpose
XMLCAST() extracts content (excluding element names) from an XML document and casts the content to data of the CHAR, VARCHAR, VARCHAR2, NUMBER, FLOAT, BLOB, CLOB, or any datetime type.
Syntax
XMLCAST( value_expression AS datatype )
Parameters
| Parameter | Description |
|---|---|
| value_expression | Specifies that the input string must be a valid XML fragment. |
| AS datatype | The return type, which can be CHAR, VARCHAR, VARCHAR2, NUMBER, FLOAT, BLOB, CLOB, or any datetime type. |
Return type
The return type is CHAR, VARCHAR, VARCHAR2, NUMBER, FLOAT, BLOB, CLOB, or any datetime type.
Examples
# Cast the extracted content to CHAR data.
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
# Cast the extracted content to VARCHAR2 data.
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
# Cast the extracted content to BLOB data.
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
# Cast the extracted content to CLOB data.
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
# Cast the extracted content to NUMBER data.
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
# Cast the extracted content to FLOAT data.
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
# Cast the extracted content to DECIMAL data.
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
# Cast the extracted content to TIMESTAMP data.
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
# Cast the extracted content to DATE data.
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
# Cast the extracted content to INTERVAL YEAR TO MONTH data.
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
# Cast the extracted content to INTERVAL DAY TO SECOND data.
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