By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE table_name ( data ) AS
SELECT '<ID>
<DOMAIN>IND</DOMAIN>
<DOMAINID>112AC</DOMAINID>
</ID>
<GROUP>
<GP>ASIA</GP>
<RSN>GOOD</RSN>
</GROUP>' FROM DUAL
1 rows affected
SELECT x.*
FROM table_name t
CROSS APPLY XMLTABLE(
'//root'
PASSING XMLTYPE( '<root>' || t.data || '</root>' )
COLUMNS
domain VARCHAR2(10) PATH './ID/DOMAIN',
domainid VARCHAR2(10) PATH './ID/DOMAINID',
gp VARCHAR2(50) PATH './GROUP/GP',
rsn VARCHAR2(50) PATH './GROUP/RSN'
) x
| DOMAIN | DOMAINID | GP | RSN |
|---|---|---|---|
| IND | 112AC | ASIA | GOOD |
SELECT XMLQUERY(
'/root/ID/DOMAINID/text()'
PASSING XMLTYPE( '<root>'||data||'</root>' )
RETURNING CONTENT
) AS domainid
FROM table_name
| DOMAINID |
|---|
| 112AC |