Skip to Main Content
Forums

DevOps, CI/CD and Automation

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

extract relational data from xml - xquery

3230404Oct 21 2018 — edited Oct 25 2018

our application stores all data as xml in oracle database, for example i have the below data as appear in the browser:

2018-10-21_14h10_57.png

in database the record appear like this:

<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>

as u see above c1 holds the owner data, c2 holds the transactions data... and so on..

in the above example the first "c2" node appear like <c2/> becuase it has no transactions in it. however if it has it will appear like this <c2>"some transaction number"</c2>

now from database i want to extract this data as below:

| Owner | Transaction |
| 10 | |
| 11 | 100 |
| 11 | 200 |
| 12 | |
| 13 | 300 |
| 14 | |
| 15 | 400 |
| 15 | 700 |
| 15 | 600 |
| 15 | 500 |

any help plz....

BR
This post has been answered by mNem on Oct 22 2018
Jump to Answer

Comments

mNem 
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
cormaco

That reminds me of this:

generating relational data from xml with multilevel node

and this:

Getting XMLRecord value by position

3230404

many thanx dear.

3230404

again thanx allot @"mNem", u see for the below xml example:

<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"/>

  <c3 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>

because i have values in c2 m=1 i cant connect it with <c1>188849</c1>, the result for ur SQL is like this:

1003362511 188849

1003362511 188616      10        2

1003362511 188616      20        2      2

1003362511 188619      100      3

1003362511 188622                  4

where  i need it to be like this:

1003362511 188849      1

1003362511 188849      2

1003362511 188849      3

1003362511 188849      4

1003362511 188616      10        2

1003362511 188616      20        2      2

1003362511 188619      100      3

1003362511 188622                  4

can u help plz.....

mNem

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>

3230404

from where this <c1>1888492</c1> because its not there in the xml?

unfortunately this is how our application store the xml.

if there is a way to transform the xml to a format easy for u before or during the select it will be ok.

The result set im expecting is same as below:

1003362511 188849      1

1003362511 188849      2

1003362511 188849      3

1003362511 188849      4

1003362511 188616      10        2

1003362511 188616      20        2      2

1003362511 188619      100      3

1003362511 188622                  4

mNem
Answer 
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
Marked as Answer by 3230404 · Sep 27 2020
1 person found this helpful
Orcl Apex

Hello,

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

mNem

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?

Orcl Apex

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

1 - 10

Post Details

Added on Oct 21 2018
#general-xml, #sql-pl-sql, #xml
10 comments
523 views