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