Nested XML to CSV transformation
Hi All,
Apologies for yet another XML to CSV post - I see there are a lot of similar topics already but I havent seen any that would quite cover what I need to do.
This is a simplified snippet of my XML:
<REPORT>
<COLUMNS>
<COL_NAME name='Name'/>
<COL_OFFER_PRICE name='Offer'/>
<COL_CODE name='Code'/>
<COL_BID_PRICE name='Bid Price'/>
</COLUMNS>
<DATA_ROW>
<COL_NAME>Growth Fund</COL_NAME>
<COL_OFFER_PRICE nested='True'>
<PRICE itemDate='2001-01-01'>1.00</PRICE>
<PRICE itemDate='2001-01-02'>1.01</PRICE>
<PRICE itemDate='2001-01-03'>1.04</PRICE>
...........................
</COL_OFFER_PRICE>
<COL_CODE>12321</COL_CODE>
<COL_BID_PRICE nested='True'>
<PRICE itemDate='2001-01-01'>1.01</PRICE>
<PRICE itemDate='2001-01-02'>1.02</PRICE>
<PRICE itemDate='2001-01-03'>1.09</PRICE>
...........................
</COL_BID_PRICE>
</DATA_ROW>
<DATA_ROW>
...........................
</DATA_ROW>
</REPORT>
It represents a report, each <COLUMNS> item is a column heading & each <DATA_ROW> item is a row of data. There may be one or many <DATA_ROW> items.
Each <DATA_ROW> represents a row of data about a fund in this context.
Items within the <DATA_ROW> may be 'static data' which is not date sensitive such as 'fund name' or they may have a 'nested' attribute which means that they have sub-rows of data.
I need to transform the XML into a CSV file like:
Name, Offer, Code, Bid
Growth Fund, 1.00, 12321, 1.01
Growth Fund, 1.01, 12321, 1.02
Growth Fund, 1.04, 12321, 1.09
So for each nested item within a data row, I need to repeat the 'static' data for all of its children.
All nested items within a <DATA_ROW> will have the same amount of children.
Confused? I am...
Any help/guidance at all gratefully accepted.
Regards,
Patrick.
|