By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
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;
3 rows affected
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
| 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 |