converting XML to CSV where different tags in XML and their respective sub tags become line items of the CSV

Question

I am trying to get the attribute tags within accessory tag to be line items of a CSV file

I have tried to iterate using map function but im just getting one value at a time.What i want is there are two Accesory Type tag in this XML i want their respective attribute tags to become each line item in the CSV

 <Accessory Type="Panel">
    <PartNumber>9021447</PartNumber>
    <Attribute Name="DescCode">Operating Panel, White/PI White, Low E SmartSun Tempered Glass</Attribute>
    <Attribute Name="Mark_ID">41424</Attribute>
    <Attribute Name="Order_Quantity">1</Attribute>
    <Attribute Name="QUOTE_QUANTITY">1</Attribute>
    <Attribute Name="Quote_Quantity_Extended">1</Attribute>
    <Attribute Name="MarkName">0001</Attribute>
    <Attribute Name="US_ENERGY_STAR_CLIMATE_ZONE">Northern</Attribute>
    <Attribute Name="SubUnitID">1</Attribute>
    <Attribute Name="AW_Control_2">47AoYY</Attribute>
    <Attribute Name="ReferenceID">3</Attribute>
    <Attribute Name="OrderPONum"/>
    </Accessory>
    <Accessory Type="Screen">
    <PartNumber>2505930</PartNumber>
    <Attribute Name="Total_Pre_Price_in_USD">208.70</Attribute>
    <Attribute Name="Total_Price_in_USD">208.700</Attribute>
    <Attribute Name="QUOTE_QUANTITY">1</Attribute>
    <Attribute Name="Quote_Quantity_Extended">1</Attribute>
    <Attribute Name="DescCode">Insect Screen, Gliding, White</Attribute>
    <Attribute Name="Mark_ID">41424</Attribute>
    <Attribute Name="Order_Quantity">1</Attribute>
    </Accessory>

I have tried to make the attributes under accessory tag to become line items.

Expected output: 9021447|Operating Panel, White/PI White, Low E SmartSun Tempered Glass|41424 //and in the next line 25005930|208.70|1 //and so on

This is the code that i have tried %dw 2.0 output application/json --- payload.m2oFile.m2oOrder.Unit.*Accessory map (value, index) -> { (index) : value.*Attribute filter($.@Name == "MarkID"), (index) : value.*Attribute filter($.@Name == "PRODTYPE"), (index) : value.*Attribute filter($.@Name == "UNIT_CODE") }

Salim Khan Salim Khan · Accepted Answer · 2019-09-06T08:32:03

Try with this approach..

 %dw 2.0 
 output application/csv header=false
 --- 

 (payload.a.*Accessory map {
      PN: $.PartNumber,
      ($.*Attribute map  {
        (($$) : $)  if (isEmpty($) == false)
 })
 }
 )

Should give you the output as follows:

PS: i had to add "a" as the root to the xml as otherwise the xml would not be well formed..

converting XML to CSV where different tags in XML and their respective sub tags become line items of the CSV

1 Answers