By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE table_name ( data ) AS
SELECT '<?xml version="1.0" encoding="US-ASCII"?>
<GML:GMMessage
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:GML="GML"
xmlns:busHdr="busHdr"
xmlns:GMLType="GML.Type"
xsi:schemaLocation="GML ../schema/gml.xsd busHdr ../schema/bushdr.xsd"
SchemaVersion="9.8">
<BusinessHdr>
<busHdr:BusObjectType>ABC</busHdr:BusObjectType>
<busHdr:BusObjectOwner>HDHDH</busHdr:BusObjectOwner>
<busHdr:BusObjectId>DJHDAHDAJHDA</busHdr:BusObjectId>
<busHdr:BusObjectVersion>1</busHdr:BusObjectVersion>
</BusinessHdr>
<Transaction>
<GenericEvent>NEW</GenericEvent>
<Group>
<GroupId>3424234</GroupId>
<Reason>MANUAL</Reason>
</Group>
<Id>
<Domain>ssdsgdsg</Domain>
<DomainId>123456ACC</DomainId>
<Version>1</Version>
</Id>
<Date>2021-02-01</Date>
</Transaction>
</GML:GMMessage>' FROM DUAL
1 rows affected
SELECT x.*
FROM table_name t
CROSS APPLY XMLTABLE(
XMLNAMESPACES( 'GML' AS "GML" ),
'//GML:GMMessage/Transaction'
PASSING XMLTYPE( t.data )
COLUMNS
domain VARCHAR2(10) PATH './Id/Domain',
domainid VARCHAR2(10) PATH './Id/DomainId',
version NUMBER(3,0) PATH './Id/Version',
groupid VARCHAR2(50) PATH './Group/GroupId',
reason VARCHAR2(50) PATH './Group/Reason',
dt DATE PATH './Date'
) x
| DOMAIN | DOMAINID | VERSION | GROUPID | REASON | DT |
|---|---|---|---|---|---|
| ssdsgdsg | 123456ACC | 1 | 3424234 | MANUAL | 01-FEB-21 |