Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select *
from xmltable (
  '/row'
  passing xmltype('<row id="124">
<c1>Name</c1>
<c2>Name2</c2>
</row>')
  columns
    col1 varchar2(30) path '*[1]',
    col2 varchar2(30) path '*[2]',
    col3 varchar2(30) path '*[3]'
);


COL1
COL2
COL3


Name
Name2
null


select result
from xmltable (
  '/row'
  passing xmltype('<row id="124">
<c1>Name</c1>
<c2>Name2</c2>
</row>')
  columns
    col1 varchar2(30) path '*[1]',
    col2 varchar2(30) path '*[2]',
    col3 varchar2(30) path '*[3]'
)
unpivot (result for x in (col1 as 1, col2 as 2, col3 as 3));


RESULT


Name

Name2


create table your_table(xml_col) as
select xmltype('<row id="124">
<c1>Name</c1>
<c2>Name2</c2>
</row>')
from dual
union all
select xmltype('<row id="125">
<x>Value X</x>
<y>Value Y</y>
<z>999</z>
</row>')
from dual;
2 rows affected

select x.*
from your_table t
cross join xmltable (
  '/row'
  passing t.xml_col
  columns
    col1 varchar2(30) path '*[1]',
    col2 varchar2(30) path '*[2]',
    col3 varchar2(30) path '*[3]'
) x;


COL1
COL2
COL3


null
null
null

999
999
999


with your_table(xml_col) as (
  select xmltype('<row id="124">
<c1>Name</c1>
<c2>Name2</c2>
</row>')
  from dual
  union all
  select xmltype('<row id="125">
<x>Value X</x>
<y>Value Y</y>
<z>999</z>
</row>')
  from dual
)
select x.*
from your_table t
cross join xmltable (
  '/row'
  passing t.xml_col
  columns
    col1 varchar2(30) path '*[1]',
    col2 varchar2(30) path '*[2]',
    col3 varchar2(30) path '*[3]'
) x;


COL1
COL2
COL3


Name
Name2
null

Value X
Value Y
999


select id, result
from your_table t
cross join xmltable (
  '/row'
  passing t.xml_col
  columns
    id number path '@id',
    col1 varchar2(30) path '*[1]',
    col2 varchar2(30) path '*[2]',
    col3 varchar2(30) path '*[3]'
) x
unpivot (result for x in (col1 as 1, col2 as 2, col3 as 3));


ID
RESULT


124
Name

124
Name2

125
Value X

125
Value Y

125
999


select x.*
from your_table t
cross join xmltable (
  '/row'
  passing t.xml_col
  columns
    id number path '@id',
    col1_name varchar2(30) path '*[1]/local-name()',
    col2_name varchar2(30) path '*[2]/local-name()',
    col3_name varchar2(30) path '*[3]/local-name()',
    col1 varchar2(30) path '*[1]',
    col2 varchar2(30) path '*[2]',
    col3 varchar2(30) path '*[3]'
) x;


ID
COL1_NAME
COL2_NAME
COL3_NAME
COL1
COL2
COL3


124
null
null
null
null
null
null

125
z
z
z
999
999
999


with your_table(xml_col) as (
  select xmltype('<row id="124">
<c1>Name</c1>
<c2>Name2</c2>
</row>')
  from dual
  union all
  select xmltype('<row id="125">
<x>Value X</x>
<y>Value Y</y>
<z>999</z>
</row>')
  from dual
)
select x.*
from your_table t
cross join xmltable (
  '/row'
  passing t.xml_col
  columns
    id number path '@id',
    col1_name varchar2(30) path '*[1]/local-name()',
    col2_name varchar2(30) path '*[2]/local-name()',
    col3_name varchar2(30) path '*[3]/local-name()',
    col1 varchar2(30) path '*[1]',
    col2 varchar2(30) path '*[2]',
    col3 varchar2(30) path '*[3]'
) x;


ID
COL1_NAME
COL2_NAME
COL3_NAME
COL1
COL2
COL3


124
c1
c2
null
Name
Name2
null

125
x
y
z
Value X
Value Y
999


select id, col, result
from your_table t
cross join xmltable (
  '/row'
  passing t.xml_col
  columns
    id number path '@id',
    col1_name varchar2(30) path '*[1]/local-name()',
    col2_name varchar2(30) path '*[2]/local-name()',
    col3_name varchar2(30) path '*[3]/local-name()',
    col1 varchar2(30) path '*[1]',
    col2 varchar2(30) path '*[2]',
    col3 varchar2(30) path '*[3]'
) x
unpivot ((col, result) for x in ((col1_name, col1) as 1, (col2_name, col2) as 2, (col3_name, col3) as 3));


ID
COL
RESULT


124
c1
Name

124
c2
Name2

125
x
Value X

125
y
Value Y

125
z
999