sqloracle-databasejoinxmltable

How to use joins(inner,left) with xmltable?


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


Solution

  • You don't need any INNER JOINs, 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