What is the best way to input an xml file into sql Server database

Question

The format of the Xml file is the same but there are many more

<id>220</id>
<direction>1</direction>
<count>1</count>
<date>2017-10-05T00:00:00</date> in the actual Xml file

Here is the Xml file

<?xml version="1.0" encoding="utf-8"?>
<counts>
<count>
<id>220</id>
<direction>1</direction>
<count>1</count>
<date>2017-10-05T00:00:00</date>
</count>
<count>
<id>220</id>
<direction>2</direction>
<count>0</count>
<date>2017-10-05T00:00:00</date>
</count>
<counts>

I first tried to copy this Xml file into Json format and this work fine creating this json file
{"counts":{"count":[
{"id":220,"direction":1,"count":1,"date":"2017-10-05T00:00:00"},
{"id":220,"direction":2,"count":0,"date":"2017-10-05T00:00:00"},
]}}

The problem start when I try to map because the map in data factory looks like this enter image description here So only one record from json is added to the database table. So I wonder what is the best way to go. null

Both the xml file and the json file is a blob in different container in azure. So If it's possible to change the blob json file how could I do that. Many thanks in advance

Answer 1

Hi @Tony Johansson , Welcome to Microsoft Q&A forum and thanks for reaching out here.

As per my understanding, the issue is with your mapping section in copy activity.

Please set the collection reference as shown below which will help you resolve the issue and copy all records form the counts array.

Here is another thread where a similar issue was discussed: Data Factory XML validation(Copy Activity) throwing User Configuration error Hope this helps.

---

Please don’t forget to Accept Answer and Yes for "was this answer helpful" wherever the information provided helps you, this can be beneficial to other community members.