説明
この関数は、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>この週末は私を忘れないでね！</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