首批通过分布式安全可靠测评，为关键业务系统打造
XMLCAST
更新时间：2026-03-09 08:20:17
编辑
分享
描述
该函数用于抽取 XML 文档的内容（不包括元素名称），并将其转换为 CHAR、VARCHAR、VARCHAR2、NUMBER、FLOAT、BLOB 或 CLOB 及任何日期时间数据类型。
语法
XMLCAST ( value_expression AS datatype )
参数解释
| 字段 | 说明 |
|---|---|
| value_expression | 指定解析的内容必须为一个有效的 XML 片段。 |
| AS datatype | 指定返回的类型，可以为 CHAR、VARCHAR、VARCHAR2、NUMBER、FLOAT、BLOB 或 CLOB 及任何日期时间数据类型。 |
返回类型
返回 CHAR、VARCHAR、VARCHAR2、NUMBER、FLOAT、BLOB 或 CLOB 及任何日期时间数据类型。
示例
# 转换为 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
# 转换为 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
# 转换为 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
# 转换为 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
# 转换为 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
# 转换为 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
# 转换为 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
# 转换为 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
# 转换为 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
# 转换为 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
# 转换为 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
