Getting data from XML API fails in copy data but works in web activity

[Jorn_vb]

I am trying to get the data from an XML based API into a datalake. Using web activities I succeeded retrieving the data, but I couldn't find a way to load that output into a datalake. I tried the copy data with a rest connection as source, this resulted in a 500 error. Most of the solutions I could find were options I don't see in my UI (I will attach some screenshots).
The most important thing is just to get the data in datalake. It doesn't matter what activities I use. If possible, I like to use the output of a web activity (which like I mentioned does retrieve data).

[nilendraFabric]

Hi @Jorn_vb 

To retrieve XML data from an API and load it into a Microsoft Fabric Lakehouse, you can use a combination of Web Activity and Copy Activity in your data pipeline. Here's a step-by-step approach to accomplish this:

Use Web Activity to Retrieve XML Data

1. Add a Web Activity to your pipeline to make the API call and retrieve the XML data.
2. Configure the Web Activity with the appropriate URL, method, headers, and any required authentication.
3. The Web Activity will successfully retrieve the XML data as you've mentioned.

Process Web Activity Output

1. Add a Set Variable Activity after the Web Activity.
2. Configure the Set Variable Activity to store the Web Activity's output in a variable.
3. Use the following expression to capture the Web Activity's output:
   @activity('Web1').output.Response
   (Replace 'Web1' with your Web Activity's name)

Use Copy Activity to Load Data into Lakehouse

1. Add a Copy Activity after the Set Variable Activity.
2. For the source:
   • Choose "REST" as the source type.
   • Set the Request Method to "GET".
   • In the Additional Headers, add a Content-Type header with value "application/xml".
   • In the Request Body, use the variable you set in the previous step.
3. For the sink:
   • Choose your Lakehouse as the destination.
   • Select the appropriate file format (e.g., XML, JSON, or Parquet, depending on your needs).
4. In the Copy Activity's settings, enable "First row as header" if applicable.
   

Please accept this solution and give kudos , if this helps .

Thanks