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

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

Jorn_vb — Mon, 20 Jan 2025 09:27:35 GMT

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).

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

nilendraFabric — Mon, 20 Jan 2025 13:15:38 GMT

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

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

Process Web Activity Output

Add a Set Variable Activity after the Web Activity.
Configure the Set Variable Activity to store the Web Activity's output in a variable.
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

Add a Copy Activity after the Set Variable Activity.
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.
For the sink:
- Choose your Lakehouse as the destination.
- Select the appropriate file format (e.g., XML, JSON, or Parquet, depending on your needs).
In the Copy Activity's settings, enable "First row as header" if applicable.

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

Thanks

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

Salmatador — Thu, 30 Jan 2025 20:17:53 GMT

Can you explain: Choose "REST" as the source type. How can I set the Request body for a GET request? It only shows up for me when I switch it to POST?

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

nilendraFabric — Thu, 30 Jan 2025 20:37:59 GMT

https://learn.microsoft.com/en-us/fabric/data-factory/connector-rest-copy-activity

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

Jorn_vb — Fri, 31 Jan 2025 13:02:14 GMT

The screenshots on this page do not look like my UI (see screenshot in my post). With what I know it does not make sense to put the response (data I want) into the body of a request? But that is probaply just because what I see mismatches what you see. Do you know anything that can help me?

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

Salmatador — Fri, 31 Jan 2025 18:31:17 GMT

The screen shots do not match what I see either and I was at a loss on how to create a dummy request, I am new to this. I ended up using a notebook with the requests and pandas libraries to import the xml API data into my lakehouse tables

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

nilendraFabric — Fri, 31 Jan 2025 18:38:38 GMT

Hello Salmatador

Could you please share your approach here.

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

Salmatador — Fri, 31 Jan 2025 19:04:50 GMT

Here is what I did to test that I could get it to work.

from io import StringIO # https://docs.python.org/3/library/io.html#io.StringIO import pandas as pd # https://pandas.pydata.org/docs/reference/index.html import requests # https://requests.readthedocs.io/en/latest/ # Setup Request Variables. base_url = 'https://vsac.nlm.nih.gov/vsac/svs/' end_point = 'RetrieveMultipleValueSets' parameters = {'id':'2.16.840.1.113883.3.117.1.7.1.226','release':'eCQM Update 2020-05-07'} # Request - Basic Authentication values have been removed response = requests.get(f"{base_url}{end_point}",params=parameters,auth=('userName','password')) # Warns about using response.text directly using StringIO as suggested xml_io_concept = StringIO(response.text) # Define namespaces namespaces = { 'ns0': 'urn:ihe:iti:svs:2008', 'xsi': 'http://www.w3.org/2001/XMLSchema-instance' } # Parse the XML and extract Concept elements pandas_df = pd.read_xml( # https://pandas.pydata.org/docs/reference/api/pandas.read_xml.html xml_io_concept, xpath=".//ns0:Concept", namespaces=namespaces ) # Convert Pandas to PySpark spark_df = spark.createDataFrame(pandas_df) # Write to table in default Lakehouse spark_df.write.format("delta").mode("overwrite").option("overwriteSchema", "true").save(f"Tables/test_value_set") # Query Lakehouse Table df = spark.sql("SELECT * FROM ValueSet_Lake.test_value_set LIMIT 1000") display(df)

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

nilendraFabric — Fri, 31 Jan 2025 19:40:18 GMT

Thanks for sharing Salmatador

Jorn_vb see if this is helpful

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

Jorn_vb — Thu, 06 Feb 2025 09:08:39 GMT

I solved the copy data by using a http connector with basic auth instead of a REST connection.

This was not obvious because the API uses tokens for auth and a http connection anomynous failed.