Data Factory read multilevel XML to csv

Question

Partly answer: https://learn.microsoft.com/en-us/answers/questions/76815/data-factory-multiple-collection-reference-for-xml.html

I have the following XML that I'm trying to parse into CSV using a Copy Task.

This is the CSV I like to have:

row number, idExportMeldung, zeitQuittiert  
1, 72737488, 20220403064519  
2, 72737489, 20220403064603  
3, 72737490, 20220403064516  
4, 72737491, 20220403061933  

Answer 1

Hi @Erlandsson, Fredrik ,
Welcome to Microsoft Q&A platform and thanks for posting your question.
As I understand your ask , you are trying to transform XML data into CSV in the mentioned format. Please let me know if my understanding is incorrect.

The above requirement is difficult to be handled using copy activity in ADF , you can achieve that using mapping dataflow. Here is how to do that:

1. Use source transformation and point the dataset to the XML file and preview the data. Each cell value is coming as an item of the array.

2. Use flatten transformation to convert items of array into individual rows.

3. Use Pivot transformation to convert column values into columnnames. In group by tab, select 'row number' column to group by. In pivot key tab, select 'id' as the pivot key . In pivoted columns , use 'max(value)' as the expression .

Check the data preview tab:

4. Use sink transformation and point the dataset to .csv file and run the dataflow activity in ADF.

Hope this will help. Please let us know if any further queries.

------------------------------

• Please don't forget to click on or upvote button whenever the information provided helps you.
  Original posters help the community find answers faster by identifying the correct answer. Here is how
• Want a reminder to come back and check responses? Here is how to subscribe to a notification
• If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators