CODEWITHSUNDEEP
sqloracle-databasejoinxmltable
muhammed mahir koçbıyıkığlu
muhammed mahir koçbıyıkığlu

Reputation: 3

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

Upvotes: 0

Views: 545

Answers (1)

MT0
MT0

Reputation: 168505

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

Upvotes: 1

Related Questions