Azure Data Factory: Transforming XML Response to CSV in Blob Storage

Question

Azure Data Factory: Transforming XML Response to CSV in Blob Storage

Nalini Bhavaraju 110

Hi Team,

I am attempting to make a REST API call to retrieve data and store the response in Data Lake Storage using Azure Data Factory.

The REST API returns an XML response. Here's what has been done so far:

A Web activity was created to pull data from the REST API.
A Copy activity was set up, where an XML source dataset linked to a Blob storage was created, using a dummy XML data file.
In the sink, a CSV dataset linked to Blob storage was established.

During the process, I tried to import the schemas under mapping. Subsequently, I added a new column in the source under Additional columns with the value as @activity('Webactivityoutput').output.Response. However, when I attempted to import the schema again by passing a dummy value, it failed with the error: "The value of property 'additionalColumns' is in unexpected type 'IList`1'".

Need some assistance to successfully convert the XML response from the REST API into Data Blob Storage (.csv) using an ADF pipeline.

Thanks,

Nalini.

Chandra Boorla 15,455 Reputation points Microsoft External Staff Moderator

2025-05-22T20:42:28.3033333+00:00
@Nalini Bhavaraju

Thanks for sharing the details of your setup, you're very close to the right solution!

The issue you're seeing "The value of property 'additionalColumns' is in unexpected type 'IList`1'" occurs because the additionalColumns field in the Copy activity expects a list of key-value pairs, not a single raw XML string. Injecting the full response as a single value directly there doesn't work.

Recommended approach to handle XML and Convert to CSV:

Web Activity – Get the XML - Ensure your Web activity is successfully calling the REST API and retrieving the XML response. The response will be accessible via:

@activity('WebActivityName').output.Response

Use object parameter for Cleaner Handling - To handle this properly, you can use an object-type parameter and a child pipeline, which allows cleaner handling of the XML response and avoids misusing additionalColumns.

In the parent pipeline, use the Execute Pipeline activity and pass:

{ "xmlResponse": "@activity('WebActivityName').output.Response" }

In the child pipeline, define a parameter xmlResponse of type Object.
This keeps your design modular and avoids misusing additionalColumns.

Store XML as a File (Optional Preprocessing) - In the child pipeline:

Use a Copy activity with:

Source: Use an inline dataset with the content from @pipeline().parameters.xmlResponse

Sink: Write the raw XML content to a temporary .xml file in Blob Storage.

Transform XML to CSV Using Mapping Data Flow - Use a Data Flow to:

Source: Point to the .xml file stored in Blob

Flatten or parse the XML as needed

Sink: Write to a .csv file in Blob Storage

Reference - This issue around additionalColumns expecting a list of name-value pairs has been discussed in this Microsoft Q&A thread, which explains how Copy activity handles dynamic expressions incorrectly formatted as single string values.

Thread links:

https://learn.microsoft.com/en-us/answers/questions/795543/adf-copy-dynamic-upsert-column-key

https://learn.microsoft.com/en-us/answers/questions/203587/error-when-object-type-parameter-is-passed-through

I hope this information helps. Please do let us know if you have any further queries.

Kindly consider upvoting the comment if the information provided is helpful. This can assist other community members in resolving similar issues.

Thank you.
Chandra Boorla 15,455 Reputation points Microsoft External Staff Moderator

2025-05-26T17:04:52.0666667+00:00

@Nalini Bhavaraju

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution, please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Nalini Bhavaraju 110 Reputation points

2025-05-27T15:09:27.96+00:00
Hi Chandra,

Thanks for the response.

In regards to below suggestion, There is no option in the copy activity source to choose a inline dataset in Data Factory. Can you please point me to the right options?

Additionally, The sources you shared work with CSV and not XML.

Use a Copy activity with:

Source: Use an inline dataset with the content from @pipeline().parameters.xmlResponse

Sink: Write the raw XML content to a temporary .xml file in Blob Storage.

Thanks,

Nalini.
Chandra Boorla 15,455 Reputation points Microsoft External Staff Moderator

2025-05-27T21:06:18.8566667+00:00
@Nalini Bhavaraju

Thank you so much for your follow-up, and I appreciate your patience.

You're absolutely right, the Copy activity in Azure Data Factory does not support inline content (like raw XML responses) directly as a source. I want to sincerely apologize for the earlier confusion and thank you for pointing this out.

Updated Guidance - To handle an XML response from a REST API and convert it to CSV in Blob Storage using ADF, here’s the correct approach:

Step 1 - Write the XML Response to Blob Storage

Since the Copy activity can’t use inline content, you’ll need to first save the XML to a file:

Use an Azure Function, Logic App, or a custom Webhook activity to write the XML content retrieved from the Web activity to Blob Storage.

The response from your Web activity (e.g., @activity('WebActivityName').output.Response) can be passed to the function to create a file like rawResponse.xml.

Step 2 - Use Copy Activity with File-Based Source

Once the file is in Blob Storage:

Create an XML source dataset pointing to the saved file.

Create a DelimitedText (CSV) sink dataset.

Use the Copy Activity or a Mapping Data Flow to transform and map XML fields to CSV.

I hope this information helps.

Thank you.

Nalini Bhavaraju 110

Hi Chandra,

Thanks for the response.

I will give this approach a try and will update you.

quick question - My xml looks like this -

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<response status="Ok">
    <links>
        <link id="123" hint="Sample Office" linkDescription="This is the GET url for this Property." link="/property/123" httpMethod="GET"/>
        <link id="456" hint="Sample Office" linkDescription="This is the GET url for this Property." link="/property/456" httpMethod="GET"/>
        <link id="789" hint="Sample Warehouse" linkDescription="This is the GET url for this Property." link="/property/789" httpMethod="GET"/>
    </links>
</response>

How do I loop through to retrieve link id's , as I want to build a pipeline to loop through these link ids and retrieve data from individual links.

Nalini Bhavaraju 110 Reputation points

2025-05-28T15:07:38.4166667+00:00

Hi Chandra,

I am not sure if a webhook activity can write data to a sink or a Blob.

Can you please help me understand?

Thanks,

Nalini
Anonymous

2025-05-29T10:16:37.4166667+00:00
Hi @Nalini Bhavaraju, Thank you for your response.

You're absolutely right to ask for clarification. In Azure Data Factory (ADF), a Webhook activity is specifically designed to call an external service and then wait for a callback response. It’s commonly used for long-running tasks where ADF needs to pause and wait until that external service tells it the job is done. Because of how it works, a Webhook activity doesn’t have the ability to write data directly to Blob storage or any other sink.

If your goal is to write data like XML responses to Azure Blob Storage, the better approach is to use a Web activity. With this, you can call an Azure Function or a Logic App, passing the data you need to store. The function or logic app can then handle the task of saving that data to Blob storage.

This setup gives you full flexibility and works well with ADF pipelines, especially when you're dealing with dynamic content like XML from REST APIs.

If you have any questions, please free to ask happy to help you.
Nalini Bhavaraju 110 Reputation points

2025-05-29T15:20:08.76+00:00

Hi Chandra,

Thanks for the response.

I still not able to parse the xml as required. Can I handle it in any other Azure resources other than Azure Data Factory.

Thanks,

Nalini.
Anonymous

2025-06-03T12:04:44.9766667+00:00

@Nalini Bhavaraju

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution, please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help. Happy to help you.
Anonymous

2025-06-04T13:11:50.7433333+00:00

@Nalini Bhavaraju

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution, please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help. Happy to help you.

1 answer

Your answer

Chandra Boorla 15,455 Reputation points Microsoft External Staff Moderator

2025-05-26T17:04:52.0666667+00:00

@Nalini Bhavaraju

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution, please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Nalini Bhavaraju 110 Reputation points

2025-05-27T15:09:27.96+00:00

Hi Chandra,

Thanks for the response.

In regards to below suggestion, There is no option in the copy activity source to choose a inline dataset in Data Factory. Can you please point me to the right options?

Additionally, The sources you shared work with CSV and not XML.

Use a Copy activity with:

Source: Use an inline dataset with the content from @pipeline().parameters.xmlResponse

Sink: Write the raw XML content to a temporary .xml file in Blob Storage.

Thanks,

Nalini.
Chandra Boorla 15,455 Reputation points Microsoft External Staff Moderator

2025-05-27T21:06:18.8566667+00:00

@Nalini Bhavaraju

Thank you so much for your follow-up, and I appreciate your patience.

You're absolutely right, the Copy activity in Azure Data Factory does not support inline content (like raw XML responses) directly as a source. I want to sincerely apologize for the earlier confusion and thank you for pointing this out.

Updated Guidance - To handle an XML response from a REST API and convert it to CSV in Blob Storage using ADF, here’s the correct approach:

Step 1 - Write the XML Response to Blob Storage

Since the Copy activity can’t use inline content, you’ll need to first save the XML to a file:

Use an Azure Function, Logic App, or a custom Webhook activity to write the XML content retrieved from the Web activity to Blob Storage.

The response from your Web activity (e.g., @activity('WebActivityName').output.Response) can be passed to the function to create a file like rawResponse.xml.

Step 2 - Use Copy Activity with File-Based Source

Once the file is in Blob Storage:

Create an XML source dataset pointing to the saved file.

Create a DelimitedText (CSV) sink dataset.

Use the Copy Activity or a Mapping Data Flow to transform and map XML fields to CSV.

I hope this information helps.

Thank you.
Nalini Bhavaraju 110 Reputation points

2025-05-28T12:12:29.55+00:00

Hi Chandra,

Thanks for the response.

I will give this approach a try and will update you.

quick question - My xml looks like this -

<?xml version="1.0" encoding="UTF-8" standalone="yes"?> <response status="Ok"> <links> <link id="123" hint="Sample Office" linkDescription="This is the GET url for this Property." link="/property/123" httpMethod="GET"/> <link id="456" hint="Sample Office" linkDescription="This is the GET url for this Property." link="/property/456" httpMethod="GET"/> <link id="789" hint="Sample Warehouse" linkDescription="This is the GET url for this Property." link="/property/789" httpMethod="GET"/> </links> </response>

How do I loop through to retrieve link id's , as I want to build a pipeline to loop through these link ids and retrieve data from individual links.
Nalini Bhavaraju 110 Reputation points

2025-05-28T15:07:38.4166667+00:00

Hi Chandra,

I am not sure if a webhook activity can write data to a sink or a Blob.

Can you please help me understand?

Thanks,

Nalini
Anonymous

2025-05-29T10:16:37.4166667+00:00

Hi @Nalini Bhavaraju, Thank you for your response.

You're absolutely right to ask for clarification. In Azure Data Factory (ADF), a Webhook activity is specifically designed to call an external service and then wait for a callback response. It’s commonly used for long-running tasks where ADF needs to pause and wait until that external service tells it the job is done. Because of how it works, a Webhook activity doesn’t have the ability to write data directly to Blob storage or any other sink.

If your goal is to write data like XML responses to Azure Blob Storage, the better approach is to use a Web activity. With this, you can call an Azure Function or a Logic App, passing the data you need to store. The function or logic app can then handle the task of saving that data to Blob storage.

This setup gives you full flexibility and works well with ADF pipelines, especially when you're dealing with dynamic content like XML from REST APIs.

If you have any questions, please free to ask happy to help you.
Nalini Bhavaraju 110 Reputation points

2025-05-29T15:20:08.76+00:00

Hi Chandra,

Thanks for the response.

I still not able to parse the xml as required. Can I handle it in any other Azure resources other than Azure Data Factory.

Thanks,

Nalini.
Anonymous

2025-06-03T12:04:44.9766667+00:00

@Nalini Bhavaraju

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution, please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help. Happy to help you.
Anonymous

2025-06-04T13:11:50.7433333+00:00

@Nalini Bhavaraju

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution, please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help. Happy to help you.

Answer 1

Hi @Nalini Bhavaraju, Thank you for your response. I have tried a work around for your question.

In the Copy Data activity, the supported format for APIs is JSON only. However, if the API returns data in a different format (like XML), you can use a Web activity in combination with Copy Data to handle the response as shown below.

1: Using Web Activity to Call an XML API

I used a sample API that responds with data in XML format. This API was called using a Web activity, and the debug output looked like the following:

The entire XML response is returned as a plain string.

pcs1

2: Creating a Dummy Source

Next, I created a Copy Data activity. As the source, I used a CSV file (the actual content of the file is not important—just ensure it has at least one row, similar to the sample below):

pcs2

3: Configuring the Sink

For the destination (sink), choose the Delimited Text option. Name the file as op.xml.

Make sure you uncheck the "First row as header" option and set quote character to "No quote character."

Here is how the sink dataset is configured: pcs3

4: Mapping Configuration

In the mapping section, import the schema, and retain only the additional column you added manually for the XML content.

pcs4

Final Output

pcs5

This configuration generates an output file called op.xml in your destination storage location.

Post-Processing

If needed, you can use Data Flow to further transform this file and convert it into your desired format.

You can refer this QnA for transformation.

I hope this information helps you. Happy to help you always.

Kindly consider upvoting the answer if the information provided is helpful. This can assist other community members in resolving similar issues.

Share via

Azure Data Factory: Transforming XML Response to CSV in Blob Storage

1 answer

Your answer