XML to CSV

Question

I have an scenario like, I will get the XML data by hitting the API using SHIR. Now i need to convert the XML into CSV using azure data factory. Here the data was not in well organised schema. Please help me how achieve it

Answer 1

Hi Bhajanthri Bala Sujay,

Thanks for reaching out to Microsoft Q&A.

Try using mapping data flow in ADF. As your XML data doesn't follow a well-organized schema, try with the following steps:

1. Source Transformation:
   • Point the dataset to your XML file and preview the data.
   • Note that each cell value is coming as an item of an array.
2. Flatten Transformation:
   • Convert the items of the array into individual rows.
   • This helps to organize the data for further processing.
3. Pivot Transformation:
   • Convert column values into column names.
   • Group by the "row number" column.
   • Use the "id" as the pivot key.
   • In the pivoted columns, use "max(value)" as the expression.
4. Sink Transformation:
   • Point the dataset to a CSV file.
   • Run the dataflow activity in ADF.

Note that this approach leverages mapping dataflows rather than the traditional copy activity in ADF.

Please 'Upvote'(Thumbs-up) and 'Accept' as an answer if the reply was helpful. This will benefit other community members who face the same issue.