I have a xml like below,
<a_level1>
<level2>
<list1>
<a1>123456</a1>
<list2>
<variables>
<a2>115</a2>
<a3>1313.57</a3>
<a4>asd</a4>
</variables>
</list2>
</list1>
<list1>
<a1>1072001</a1>
<list2>
<variables>
<a2>215</a2>
<a3>2740.18</a3>
<a4>qwe</a4>
</variables>
</list2>
</list1>
</level2>
</a_level1>
I can extract variables from xmltype to table like this.
select a.a1,b.a2,b.a3,b.a4
from x.x_table t,
xmltable(
'/a_level1/level2/list1'
PASSING xmltype(t.xml_data)
COLUMNS a1 varchar2(20) PATH 'a1',
list2 XMLType PATH 'list2'
) a,
xmltable(
'/list2/variables'
PASSING a.list2
COLUMNS a2 varchar2(20) PATH 'a2',
a3 varchar2(20) PATH 'a3',
a4 varchar2(20) PATH 'a4'
) b
where t.xml_id = 1
result is;
a1 | a2 | a3 | a4 |
---|---|---|---|
123456 | 115 | 1313.57 | asd |
123456 | 215 | 2740.18 | qwe |
my question is how can i use inner join in this query
You don't need any INNER JOIN
s, or even the second XMLTABLE
, as you can use the full path from the second table and then go back up the hierarchy to get the a1
value:
select a.*
from /*x.*/x_table t
CROSS JOIN
XMLTABLE(
'/a_level1/level2/list1/list2/variables'
PASSING xmltype(t.xml_data)
COLUMNS
a1 varchar2(20) PATH './../../a1',
a2 varchar2(20) PATH 'a2',
a3 varchar2(20) PATH 'a3',
a4 varchar2(20) PATH 'a4'
) a
where t.xml_id = 1;
Which, for your sample data, outputs:
A1 A2 A3 A4 123456 115 1313.57 asd 1072001 215 2740.18 qwe
db<>fiddle here