How to extract values from a single xmltype tag into different columns in oracle?

Question

I have a xmltype column in a table which contains the following tag

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

I have extracted the values for type in a column using the following query

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

The output is ABSABSREL in a single column in a table. I want to get each value in a separate column in a table. Please help with how can this be done?

Answer 1

Use 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

Which, for the sample data:

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;

Outputs:

OBJECT_ID	TYPE	EPSG	UNIT	X	Y
1	ABS	4277	decLL	-2	49
2	ABS	4277	decLL	-1	50
3	REL	4277	decLL	0	51

db<>fiddle here