如何从一个单一的xmltype标签提取值到不同的列在oracle?

Question

表中有一个xmltype列，其中包含以下标记

<LOCATION TYPE="ABS" EPSG="4277" UNIT="decLL">
<X>-2.12105834</X>
<Y>49.20372223</Y>
</LOCATION>

我已经使用以下查询

提取了列中type的值

SELECT a.object_id,a.xml_data.extract('//LOCATION/@TYPE').getStringVal() AS "Location Type"
FROM   object_history a;

表中单列的输出是ABSABSREL。我想在表的单独列中获得每个值。请帮助如何做到这一点?

Answer 1

使用XMLTABLE:

SELECT object_id,
x.*
FROM   object_history h
CROSS APPLY XMLTABLE(
'/LOCATION'
PASSING h.xml_data
COLUMNS
type VARCHAR2(3)  PATH './@TYPE',
epsg NUMBER       PATH './@EPSG',
unit VARCHAR2(10) PATH './@UNIT',
x    NUMBER       PATH './X',
y    NUMBER       PATH './Y'
) x

对于样本数据:

CREATE TABLE object_history ( object_id, xml_data ) AS
SELECT 1, XMLTYPE('<LOCATION TYPE="ABS" EPSG="4277" UNIT="decLL"><X>-2</X><Y>49</Y></LOCATION>') FROM DUAL UNION ALL
SELECT 2, XMLTYPE('<LOCATION TYPE="ABS" EPSG="4277" UNIT="decLL"><X>-1</X><Y>50</Y></LOCATION>') FROM DUAL UNION ALL
SELECT 3, XMLTYPE('<LOCATION TYPE="REL" EPSG="4277" UNIT="decLL"><X>0</X><Y>51</Y></LOCATION>') FROM DUAL;

输出:

<表类>OBJECT_ID类型EPSG单位XYtbody><<tr>1ABS4277decLL2492ABS4277decLL1503REL4277decLL051