complex multivalued xml

[khanparwaz]

Hi ,
i have a complex xml file to read , i have got only xml file as requirement.

my xml is below :

<row id="LC.1.TR.SAR.23102.3100.1830.SA.3M.SA..30.183203..">
<c1>LC</c1>
<c2>1</c2>
<c3>TR</c3>
<c4>SAR</c4>
<c5>23102</c5>
<c6>3100</c6>
<c7>1830</c7>
<c8>SA</c8>
<c9>3M</c9>
<c10>SA</c10>
<c12>30</c12>
<c13>183203</c13>
<c17>20090219</c17>
<c18>ACPTBANK</c18>
<c18 m="2">DEFPAY</c18>
<c18 m="3">ISSUE</c18>
<c19>2004620</c19>
<c19 m="2">-2004620</c19>
<c19 m="3">584124</c19>
<c20 m="3">-518864</c20>
<c21 m="3"/>
<c22 m="2"/>
<c23 m="3"/>
<c24 m="3"/>
<c25>20090227</c25>
<c25 m="1" s="2">20090329</c25>
<c25 m="1" s="3">20090401</c25>
<c25 m="1" s="4">20090501</c25>
<c25 m="2">20090227</c25>
<c25 m="2" s="2">20090329</c25>
<c25 m="2" s="3">20090401</c25>
<c25 m="2" s="4">20090501</c25>
<c25 m="3">20090322</c25>
<c26 m="1" s="4"/>
<c26 m="2" s="4"/>
<c26 m="3"/>
<c27 m="1" s="4"/>
<c27 m="2" s="4"/>
<c27 m="3"/>
<c28 m="1" s="4"/>
<c28 m="2" s="4"/>
<c28 m="3"/>
<c29 m="1" s="4"/>
<c29 m="2" s="4"/>
<c29 m="3"/>
<c30 m="1" s="4"/>
<c30 m="2" s="4"/>
<c30 m="3"/>
<c31>703200</c31>
<c31 m="1" s="2">360000</c31>
<c31 m="1" s="3">701420</c31>
<c31 m="1" s="4">240000</c31>
<c31 m="2">-703200</c31>
<c31 m="2" s="2">-360000</c31>
<c31 m="2" s="3">-701420</c31>
<c31 m="2" s="4">-240000</c31>
<c31 m="3">65260</c31>
<c32 m="1" s="4"/>
<c32 m="2" s="4"/>
<c32 m="3"/>
</row>


i need to take data right from ID field to c32 into oracle table. Problem i am facing is tht when i consider c31 as lowest value and make it as key the other value of repetitive element is not coming correctly , i am getting 1st value repeating in for every c31 occurence.

pls can any one suggest me a way to desing this job which wud populate data in correct manner.


Thanks in advance

[chulett]

Why not show us what these flattened out Oracle records would need to look like, ideally using the same data you posting in your XML.

[khanparwaz]

output should be:

ID|C1|C2|C3|C4|C5|C6|C7|C8|C9|C10|C12|C13|C17|C18|C19|C20|C25|C31 (column names)

LC.1.TR.SAR.23102.3100.1830.SA.3M.SA..30.183203..|LC|1|TR|SAR|23102|3100|1830|SA|3M|SA|30|183203|20090219|ACPTBANK|2004620|NULL|20090227|703200
LC.1.TR.SAR.23102.3100.1830.SA.3M.SA..30.183203..|LC|1|TR|SAR|23102|3100|1830|SA|3M|SA|30|183203|20090219|DEFPAY|-2004620|NULL|20090227|-703200
LC.1.TR.SAR.23102.3100.1830.SA.3M.SA..30.183203..|LC|1|TR|SAR|23102|3100|1830|SA|3M|SA|30|183203|20090219|ISSUE|584124|518864|20090322|65260


i am skipping some columns at the moment in the target coz of empty value . my main interest is in c18 ,c19,c20 ,c25 ,c31 . Offcourse it should generate some more row for m and s combination but i have given an example with three rows only . Let me know if you want me to generate more rows.

Thanks