Purpose
This function extracts the content of an XML document (excluding element names) and converts it to the 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
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 a 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