Forum Stats

  • 3,851,951 Users
  • 2,264,054 Discussions
  • 7,904,915 Comments

Discussions

Categories

extract relational data from xml - xquery

3230404
3230404 Member Posts: 44
edited Oct 25, 2018 8:00PM in General XML

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:

OwnerTransaction
10
11100
11200
12
13300
14
15400
15700
15600
15500

any help plz....

BR

Tagged:
3230404
· Share on TwitterShare on Facebook

Best Answer

  • mNem
    mNem Member Posts: 1,380 Gold Trophy
    edited Oct 22, 2018 11:58AM 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.* fromt, 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}&lt;/r>                                          return if(empty($temp-rs)) then <r owner="{$c1}">{$row-id,$c1/@m}&lt;/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') xID         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
    3230404
    · Share on TwitterShare on Facebook

Answers

  • mNem
    mNem Member Posts: 1,380 Gold Trophy
    edited Oct 21, 2018 12:03PM 
    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.* fromt, 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}&lt;/r>                                          return if(empty($temp-rs)) then <r owner="{$c1}">{$c1/@m}&lt;/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') xOWNR       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
    · Share on TwitterShare on Facebook
  • cormaco
    cormaco Member Posts: 1,986 Silver Crown
    edited Oct 21, 2018 1:10PM

    That reminds me of this:

    generating relational data from xml with multilevel node

    and this:

    Getting XMLRecord value by position

    · Share on TwitterShare on Facebook
  • 3230404
    3230404 Member Posts: 44
    edited Oct 22, 2018 2:18AM

    many thanx dear.

    · Share on TwitterShare on Facebook
  • 3230404
    3230404 Member Posts: 44
    edited Oct 22, 2018 7:29AM

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

    · Share on TwitterShare on Facebook
  • mNem
    mNem Member Posts: 1,380 Gold Trophy
    edited Oct 22, 2018 8:35AM

    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>

    · Share on TwitterShare on Facebook
  • 3230404
    3230404 Member Posts: 44
    edited Oct 22, 2018 9:48AM

    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

    · Share on TwitterShare on Facebook
  • mNem
    mNem Member Posts: 1,380 Gold Trophy
    edited Oct 22, 2018 11:58AM 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.* fromt, 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}&lt;/r>                                          return if(empty($temp-rs)) then <r owner="{$c1}">{$row-id,$c1/@m}&lt;/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') xID         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
    3230404
    · Share on TwitterShare on Facebook
  • User_ZUUOD
    User_ZUUOD Member Posts: 800 Bronze Badge
    edited Oct 22, 2018 5:06PM

    Hello,

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

    · Share on TwitterShare on Facebook
  • mNem
    mNem Member Posts: 1,380 Gold Trophy
    edited Oct 24, 2018 1:23PM

    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?

    · Share on TwitterShare on Facebook
  • User_ZUUOD
    User_ZUUOD Member Posts: 800 Bronze Badge
    edited Oct 25, 2018 8:00PM

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

    · Share on TwitterShare on Facebook