xml file returns only first row

Question

Hello

I am trying to transfer files from xml file (http request) to sql server,

the problem is that it returns only the first row.
for example:
for each row i got only the first parameter.

observation tag and station tag works fine, but parameter tag returns only the first row.

this is my mapping: (i dont need the Originator tag)
ParameterShortName: ['Parameter'][0]['ParameterShortName']

ParameterValue:['Parameter'][0]['ParameterValue']
I tried to change the [0] to [1] and it returns the second row, how can i get all the rows?
Thank you.

Answer 1

@OoBiDev Welcome to Microsoft Q&A forum and thanks for reaching out here.

As your HTTP source returns XML format data, you cannot use REST connector in ADF to read the data as it expects the API response to be JSON format. Hence the only available option is to:

1. Use HTTP connector in Copy activity source and copy the XML data as a file to interim staging Azure Blob storage or a Azure Data Lake Gen2 folder.
2. Then use a mapping data flow activity in your ADF pipeline and point the mapping data flow source to the XML file that you downloaded/copied using previous copy activity.
3. Next as @Subashri Vasdudevan mentioned use a flatten transformation in data flow to flatten the nested array XML
4. Then finally use a sink transformation to copy the data to your Azure SQL server.

As your XML has a complex structure and needs multiple flattening, copy activity is not suitable here.

Hope this info helps. Do let me know how it goes.

---

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.

Answer 2

Hi @OoBiDev Thanks for your question and using MS q&a portal.

Please try to use just the property names like below instead of indexing the properties

['ParameterShortName'] ['parametervalue']

Please try and let us know.

Thanks