Load data from XML file to Azure SQL DB using Azure data factory

Question

Load data from XML file to Azure SQL DB using Azure data factory

amikm 11

I am trying to load the XML file (adf.xml) from blob storage to Azure SQL DB

From the attached file (mappingadfxml1.pdf), I am only looking for the columns that are checked and loaded into the respective columns in Azure SQL DB.

I have also attached the sample data (sampledatainfinaltable.txt) as my final output of what I am expecting

I was getting some issues with the structure so that I thought If we can create a temp table first to load the data from XML and then write a SQL script or stored procedure to fit the data into the final table in the desired format.

163658-sampledatainfinaltable.txt

163615-adf.xml[163616-mappingadfxml1.pdf][3]

163759-aaamappingadf.xml

ShaikMaheer-MSFT 38,546 Reputation points Microsoft Employee Moderator

2022-01-21T16:36:16.707+00:00

Hi @amikm ,

Following up to see if the below answer helped. If this answers your query, do click and upvote for the same. And, if you have any further query do let us know.

1 answer

Your answer

ShaikMaheer-MSFT 38,546 Reputation points Microsoft Employee Moderator

2022-01-21T16:36:16.707+00:00

Hi @amikm ,

Following up to see if the below answer helped. If this answers your query, do click and upvote for the same. And, if you have any further query do let us know.

Answer 1

ShaikMaheer-MSFT 38,546 Microsoft Employee Moderator

Hi @amikm ,

Thank you for posting query in Microsoft Q&A Platform.

In Copy activity, under mappings tab we can define mappings between source and sink columns. To get mappings under copy activity mappings tab, make sure your source and sink datasets has schema imported OR you can import schema directly under mappings tab as well.

Click here to know more about Schema and data type mapping in copy activity.

If your XML is not allowing to import mappings correctly to map columns between source and sink then as you said you can consider loading data to temp table and from there to actual table as well.

Between, feel free to share more details about issues which you are facing with structure.

Hope this will help. Please let us know if any further queries.

-----------------

Please consider hitting Accept Answer button. Accepted answers helps community as well.

ShaikMaheer-MSFT 38,546 Reputation points Microsoft Employee Moderator

2022-01-19T13:40:35.947+00:00

Hi @amikm ,

Just checking in to see if the below answer helped. If this answers your query, do click and upvote for the same. And, if you have any further query do let us know.
amikm 11 Reputation points

2022-01-21T17:35:31.567+00:00

Hi @ShaikMaheer-MSFT , I am not able to get the expected output. I did import schema for the sample XML file that I have attached, but I am not able to get data in a proper format.
Could you Please find the structure of source data an expected output data and show me the steps how we can unroll multiple array and get values in a single row instead of multiple row
ShaikMaheer-MSFT 38,546 Reputation points Microsoft Employee Moderator

2022-01-24T16:57:56.533+00:00
Hi @amikm , Sorry to here to you are unable to resolve your issue. Kindly share detailed steps of your implementations to understand case better.

Source data set details along with schema tab, what it shows under schema tab of dataset.

Sink dataset details along with schema tab, what it shows under schema tab of dataset.

Copy activity Mappings tab screenshot.
ShaikMaheer-MSFT 38,546 Reputation points Microsoft Employee Moderator

2022-02-01T16:09:01.493+00:00

Hi @amikm - Checking if you get chance to share details of above?

Share via

Load data from XML file to Azure SQL DB using Azure data factory

1 answer

Your answer