10 Replies Latest reply on Oct 26, 2018 12:00 AM by 2980262

    extract relational data from xml - xquery

    3230404

      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

        • 1. Re: extract relational data from xml - xquery
          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
          
          • 3. Re: extract relational data from xml - xquery
            3230404

            many thanx dear.

            • 4. Re: extract relational data from xml - xquery
              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.....

              • 5. Re: extract relational data from xml - xquery
                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>

                 

                 

                 

                 

                • 6. Re: extract relational data from xml - xquery
                  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

                  • 7. Re: extract relational data from xml - xquery
                    mNem
                    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 
                    
                    
                    
                    • 8. Re: extract relational data from xml - xquery
                      2980262

                      Hello,

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

                      • 9. Re: extract relational data from xml - xquery
                        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?

                        • 10. Re: extract relational data from xml - xquery
                          2980262

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