extract relational data from xml - xquery

with t (xmldoc) as 
(
select 
'
<row id="111">
  <c1>10</c1>
  <c1 m="2">11</c1>
  <c1 m="3">12</c1>
  <c1 m="4">13</c1>
  <c1 m="5">14</c1>
  <c1 m="6">15</c1>
  <c2/>
  <c2 m="2">100</c2>
  <c2 m="2" s="2">200</c2>
  <c2 m="4">300</c2>
  <c2 m="6">400</c2>
  <c2 m="6" s="2">700</c2>
  <c2 m="6" s="3">600</c2>
  <c2 m="6" s="4">500</c2>
  <c3 m="6"/>
  <c4 m="6"/>
  <c5 m="6"/>
  <c11>1</c11>
  <c12>297_LUAY1__OFS_BROWSERTC</c12>
  <c13>1810211410</c13>
  <c14>297_LUAY1_OFS_BROWSERTC</c14>
  <c15>PS0010001</c15>
  <c16>1</c16>
</row>
'
from dual

)
select x.* from
t, 
xmltable
(
  '
    let $c1s :=  $t/row/c1 
    let $c2s :=  $t/row/c2
    let $rs  :=
          for $c1 in $c1s
          let $m := $c1/@m
          let $c2s-match-for-c1 := $c2s[@m = $m]
          let $temp-rs := 
                  if(empty($c2s-match-for-c1)) then ()
                  else
                      for $c2 in $c2s-match-for-c1
                      return <r owner = "{$c1}" transaction = "{data($c2)}">{$c2/@m, $c2/@s}</r>
                                
          return if(empty($temp-rs)) then <r owner="{$c1}">{$c1/@m}</r> 
                 else $temp-rs
    return $rs   
  '
  passing xmltype(t.xmldoc) as "t" 
  columns
  ownr        varchar2(10)     path '@owner',
  trans       varchar2(20)     path '@transaction',
  m           varchar2(10)     path '@m',
  s           varchar2(10)     path '@s'
) x

OWNR       TRANS                M          S        
---------- -------------------- ---------- ----------
10                                                   
11         100                  2                    
11         200                  2          2         
12                              3                    
13         300                  4                    
14                              5                    
15         400                  6                    
15         700                  6          2         
15         600                  6          3         
15         500                  6          4         

 10 rows selected

That reminds me of this:

generating relational data from xml with multilevel node

and this:

Getting XMLRecord value by position

what is the possibility of the following xml input .... if positive, please post your expected result set ....

<row id="1003362511">

  <c1>188849</c1>

<c1>1888492</c1>

  <c1 m="2">188616</c1>

  <c1 m="3">188619</c1>

  <c1 m="4">188622</c1>

  <c2>1</c2>

  <c2 m="1" s="2">2</c2>

  <c2 m="1" s="3">3</c2>

  <c2 m="1" s="4">4</c2>

  <c2 m="2">10</c2>

  <c2 m="2" s="2">20</c2>

  <c2 m="3">100</c2>

  <c2 m="4"/>

  <c2 m="4"/> 

  <c11>1</c11>

  <c12>305_LUAY1__OFS_BROWSERTC</c12>

  <c13>1810221346</c13>

  <c14>305_LUAY1_OFS_BROWSERTC</c14>

  <c15>PS0010001</c15>

  <c16>1</c16>

</row>

SQL> 
with t (xmldoc) as 
(
select 
'
<row id="1003362511">
  <c1>188849</c1>
  <c1 m="2">188616</c1>
  <c1 m="3">188619</c1>
  <c1 m="4">188622</c1>
  <c2>1</c2>
  <c2 m="1" s="2">2</c2>
  <c2 m="1" s="3">3</c2>
  <c2 m="1" s="4">4</c2>
  <c2 m="2">10</c2>
  <c2 m="2" s="2">20</c2>
  <c2 m="3">100</c2>
  <c2 m="4"/>
  <c2 m="4"/> 
  <c11>1</c11>
  <c12>305_LUAY1__OFS_BROWSERTC</c12>
  <c13>1810221346</c13>
  <c14>305_LUAY1_OFS_BROWSERTC</c14>
  <c15>PS0010001</c15>
  <c16>1</c16>
</row>
'
from dual

)
select x.* from
t, 
xmltable
(
  '
    let $row-id := $t/row/@id
    let $c1s :=  $t/row/c1[@m] 
    let $c2s :=  $t/row/c2
    let $rs  :=
          for $c1 in $c1s
          let $m := $c1/@m
          let $c2s-match-for-c1 := $c2s[@m = $m]
          let $temp-rs := 
                  if(empty($c2s-match-for-c1)) then ()
                  else
                      for $c2 in $c2s-match-for-c1
                      return <r owner = "{$c1}" transaction = "{data($c2)}">{$row-id, $c2/@m, $c2/@s}</r>
                                
          return if(empty($temp-rs)) then <r owner="{$c1}">{$row-id,$c1/@m}</r> 
                 else $temp-rs
                 
    let $c1-without-m := 
         for $i in $t/row/c1[empty(@m)]
         for $c2-without-matching-c1 in $t/row/c2[not(@m = $t/row/c1/@m)]
         return <r owner = "{data($i)}" transaction = "{data($c2-without-matching-c1)}">
                   {$row-id, $c2-without-matching-c1/@m, $c2-without-matching-c1/@s}
                </r>
                
    return ($c1-without-m, $rs)   
  '
  passing xmltype(t.xmldoc) as "t" 
  columns
  id          varchar2(10)     path '@id',
  ownr        varchar2(10)     path '@owner',
  trans       varchar2(20)     path '@transaction',
  m           varchar2(10)     path '@m',
  s           varchar2(10)     path '@s'
) x

ID         OWNR       TRANS                M          S        
---------- ---------- -------------------- ---------- ----------
1003362511 188849     1                                         
1003362511 188849     2                    1          2         
1003362511 188849     3                    1          3         
1003362511 188849     4                    1          4         
1003362511 188616     10                   2                    
1003362511 188616     20                   2          2         
1003362511 188619     100                  3                    
1003362511 188622                          4                    
1003362511 188622                          4                    

 9 rows selected 

Hello,

can you explain your XML logic so that I can better understand it.

This is what I did in a similar situation ...

match_recognize: Working with pattern-matching for multiple columns

It is probably better if you could spend some time on the problem and then ask SPECIFIC question(s) about things you have trouble following.

Creating a new thread will invite anyone to respond instead of a certain individual (though in this case, the question is about his post).

Any way, why limit it to one person when there are many more experienced guys to benefit from?

Huh, Why are you shy to explain logic, frank kulash/soloman/mathguy nerver used to do so.