with cte as
(
select '<root>
<attr1>aaa</attr1>
<attr2>bbb</attr2>
<attr3>ccc</attr3>
</root>' as xml_attr from dual
)
select attr1,attr2,attr3
from cte x,
xmltable('/root'
passing xmltype(x.xml_attr)
columns
attr1 varchar(100) path 'attr1',
attr2 varchar(100) path 'attr2',
attr3 varchar(100) path 'attr3'
) xt
;
if there are multiple items under an element, use multiple xmltable to query a lower granularity
with cte as
(
select '<root>
<attr1>aaa</attr1>
<attr2>bbb</attr2>
<attr3>ccc</attr3>
<records>
<record>1</record>
<record>2</record>
<record>3</record>
</records>
</root>' as xml_attr from dual
)
select attr1,attr2,attr3, rec
from cte x,
xmltable('/root'
passing xmltype(x.xml_attr)
columns
attr1 varchar(100) path 'attr1',
attr2 varchar(100) path 'attr2',
attr3 varchar(100) path 'attr3',
records xmltype path 'records'
) xt
,xmltable('/records/record'
passing xt.records
columns
rec int path '/'
) xt2
;