generating relational data from xml with multilevel node

3230404

i have the below xml example where i want to generate relational data from it.

<row id="AA17001J21K8-DRINTEREST">

<fromDate>20171121</fromDate>

<fromDate m="2">20171120</fromDate>

<fromDate m="3">20171119</fromDate>

<fromDate m="4">20171118</fromDate>

<fromDate m="5">20171118</fromDate>

<fromDate m="6">20171118</fromDate>

<toDate>20171121</toDate>

<toDate m="2">20171120</toDate>

<toDate m="3">20171119</toDate>

<toDate m="4">20171118</toDate>

<toDate m="5">20171118</toDate>

<toDate m="6">20171118</toDate>

<noOfDays>1</noOfDays>

<noOfDays m="2">1</noOfDays>

<noOfDays m="3">1</noOfDays>

<noOfDays m="4">1</noOfDays>

<noOfDays m="5">1</noOfDays>

<noOfDays m="6">1</noOfDays>

<balance>24121.02</balance>

<balance m="2">27502.14</balance>

<balance m="3">27485.89</balance>

<balance m="4">30000</balance>

<balance m="4" s="2">1163.94</balance>

<balance m="5">30000</balance>

<balance m="6">30000</balance>

<balance m="6" s="2">1163.94</balance>

</row>

i simply need the output to be like this:

fromDate       toDate        noOfDays     balance1       balance2

20171121      20171121   1                   24121.02

20171120      20171120   1                   27502.14

20171119      20171119    1                  27485.89

20171118      20171118    1                  30000            1163.94

20171118      20171119    1                  30000

20171118      20171119    1                  30000            1163.94

im using script like below (below is a sample script that i used on real data but with the same concept):

/* Formatted on 4/18/2018 4:05:29 PM (QP5 v5.163.1008.3004) */

       SELECT recid,

              from_date.from_date,

              to_dates.to_dates,

              days.days,

              balance.balance,

              balance2.balance2,

             

         FROM t24.FBNK_AA_INTEREST_AC003 t

              LEFT JOIN XMLTABLE (

                           '/row'

                           PASSING t.xmlrecord

                           COLUMNS xml_from_date XMLTYPE PATH 'c1',

                                   xml_to_date XMLTYPE PATH 'c2',

                                   xml_days XMLTYPE PATH 'c3',

                                   xml_balance XMLTYPE PATH 'c4',

                                   xml_balance2 XMLTYPE PATH 'c4'

                                   ) xmldata

                 ON (1 = 1)

              LEFT JOIN XMLTABLE (

                           '/c1'

                           PASSING xmldata.xml_from_date

                           COLUMNS from_date DATE PATH '.',

                                   fruit_idx    FOR ORDINALITY) from_date

                 ON (1 = 1)

              LEFT JOIN XMLTABLE (

                           '/c2'

                           PASSING xmldata.xml_to_date

                           COLUMNS to_dates DATE PATH '.',

                                   fruit_idx2   FOR ORDINALITY) to_dates

                 ON (fruit_idx = fruit_idx2)

              LEFT JOIN XMLTABLE (

                           '/c3'

                           PASSING xmldata.xml_days

                           COLUMNS days NUMBER (10) PATH '.',

                                   fruit_idx3   FOR ORDINALITY) days

                 ON (fruit_idx = fruit_idx3)

              LEFT JOIN XMLTABLE (

                           '/c4[@s=""]'

                           PASSING xmldata.xml_balance

                           COLUMNS balance NUMBER PATH '.',

                                   fruit_idx4   FOR ORDINALITY) balance

                 ON (fruit_idx = fruit_idx4)

              LEFT JOIN XMLTABLE (

                           '/c4'

                           PASSING xmldata.xml_balance2

                           COLUMNS balance2 NUMBER PATH '.[@s="2"]',

                                   fruit_idx5   FOR ORDINALITY) balance2

                 ON (fruit_idx = fruit_idx5)

              LEFT JOIN XMLTABLE (

                           '/c8[@s=""]'

                           PASSING xmldata.xml_accr_amt

                           COLUMNS accr_amt NUMBER PATH '.',

                                   fruit_idx6   FOR ORDINALITY) accr_amt

                 ON (fruit_idx = fruit_idx6)

              LEFT JOIN XMLTABLE (

                           '/c8'

                           PASSING xmldata.xml_accr_amt

                           COLUMNS accr_amt2 NUMBER PATH '.[@s="2"]',

                                   fruit_idx7   FOR ORDINALITY) accr_amt2

                 ON (fruit_idx + 1 = fruit_idx7)

        WHERE recid = 'AA17001J21K8-DRINTEREST'

the script actually worked except for "balance2", u see what i cant achieve is to bring the nodes with "s=2" inline with balance1. the problem im getting is that balance2 is coming but in a different row.

any help plz

cormaco

Only recently another user had almost the same problem:

https://community.oracle.com/thread/4132627

I posted there a solution using xquery, here is a similar solution specific for your desired output:

with example ( xmldata ) as ( SELECT    xmltype('<row id="AA17001J21K8-DRINTEREST">    <fromDate>20171121</fromDate>    <fromDate m="2">20171120</fromDate>    <fromDate m="3">20171119</fromDate>    <fromDate m="4">20171118</fromDate>    <fromDate m="5">20171118</fromDate>    <fromDate m="6">20171118</fromDate>    <toDate>20171121</toDate>    <toDate m="2">20171120</toDate>    <toDate m="3">20171119</toDate>    <toDate m="4">20171118</toDate>    <toDate m="5">20171118</toDate>    <toDate m="6">20171118</toDate>    <noOfDays>1</noOfDays>    <noOfDays m="2">1</noOfDays>    <noOfDays m="3">1</noOfDays>    <noOfDays m="4">1</noOfDays>    <noOfDays m="5">1</noOfDays>    <noOfDays m="6">1</noOfDays>    <balance>24121.02</balance>    <balance m="2">27502.14</balance>    <balance m="3">27485.89</balance>    <balance m="4">30000</balance>    <balance m="4" s="2">1163.94</balance>    <balance m="5">30000</balance>    <balance m="6">30000</balance>    <balance m="6" s="2">1163.94</balance></row>') from dual) select     row_id,    fromdate,    todate,    noofdays,    to_number(balance1,'999999.99') balance1,    to_number(balance2,'999999.99') balance2from example,xmltable(    'for $row in /row         let $rowid := $row/@id         for $fromdate in $row/fromDate            let $m        := $fromdate/@m            let $todate   := if (exists($m)) then $row/toDate[@m = $m] else $row/toDate[not(@m)]            let $noofdays := if (exists($m)) then $row/noOfDays[@m = $m] else $row/noOfDays[not(@m)]            let $balance1 := if (exists($m)) then $row/balance[@m = $m and not(@s)] else $row/balance[not(@m)]            let $balance2 := if (exists($m)) then $row/balance[@m = $m and @s = "2"] else ""                return                     <row> {$rowid} { $fromdate } {$todate} {$noofdays} <B1>{$balance1}</B1> <B2>{$balance2}</B2> </row>'    passing xmldata    columns        row_id   varchar2(30) path '@id',        fromdate varchar2(8)  path 'fromDate',        todate   varchar2(8)  path 'toDate',        noofdays number(10)   path 'noOfDays',        balance1 varchar2(10) path 'B1/balance',        balance2 varchar2(10) path 'B2/balance')

Output:

ROW_ID

FROMDATE TODATE

NOOFDAYS   BALANCE1   BALANCE2

------------------------------ -------- -------- ---------- ---------- ----------

AA17001J21K8-DRINTEREST	20171121 20171121	1 24121,02
AA17001J21K8-DRINTEREST	20171120 20171120	1 27502,14
AA17001J21K8-DRINTEREST	20171119 20171119	1 27485,89
AA17001J21K8-DRINTEREST	20171118 20171118	1	30000 1163,94
AA17001J21K8-DRINTEREST	20171118 20171118	1	30000
AA17001J21K8-DRINTEREST	20171118 20171118	1	30000 1163,94