Ingest multiple xml files through ADF which has different schemas

Question

Ingest multiple xml files through ADF which has different schemas

Raymond Mitchell 15

Hi all,

I have an Azure blob storage of xml files with at least two different schemas. I would like to ingest all of these files through ADF (probably using Dataflow) and only process one type of schema to sink to a SQL database. How is this done correctly as I'm unable to query any xml data (File contents in the data flow). As a backup, I can upload all data into a staging table and use sql and adf pipeline to deal with this but I feel that XML handling in ADF should be better. Thanks

Emilio Molina 0 Reputation points

2023-05-08T21:23:32.4333333+00:00

Have the same issue and been stuck with it for weeks. I have 1M files with 15 similar but different complex schemas. I’d like to store them in a parquet file (it generates properly a common schema when trying with a few of them). Has someone faced the same situation before?
ShaikMaheer-MSFT 38,631 Reputation points Microsoft Employee Moderator

2023-05-23T10:38:56.9966667+00:00

Hi Ray Mitchell, Just checking if below answer helps. If yes, please consider hitting Accept Answer button. Accepted answers help community as well. Please let me know if any further queries. Thank you.

1 answer

Your answer

Emilio Molina 0 Reputation points

2023-05-08T21:23:32.4333333+00:00

Have the same issue and been stuck with it for weeks. I have 1M files with 15 similar but different complex schemas. I’d like to store them in a parquet file (it generates properly a common schema when trying with a few of them). Has someone faced the same situation before?
ShaikMaheer-MSFT 38,631 Reputation points Microsoft Employee Moderator

2023-05-23T10:38:56.9966667+00:00

Hi Ray Mitchell, Just checking if below answer helps. If yes, please consider hitting Accept Answer button. Accepted answers help community as well. Please let me know if any further queries. Thank you.

Answer 1

ShaikMaheer-MSFT 38,631 Microsoft Employee Moderator

Hi Ray Mitchell,

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

You need to consider creating dataset of XML format pointing to you XML file and use that XML dataset in dataflows. You can consider using derived column transformation or filter transformation or select transformation as per need and to filter out not requirement data or columns.

Also, you rightly mentioned you can consider using staging table approach too.

Hope this helps. Please let me know how it goes or if you struck at any specific place then kindly share your implementation details along with sample input and output.

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

ShaikMaheer-MSFT 38,631 Reputation points Microsoft Employee Moderator

2023-05-17T16:18:54.0233333+00:00

Hi Ray Mitchell, Just checking if above answer helps. If yes, please consider hitting Accept Answer button. Accepted answers help community as well. Please let me know if any further queries. Thank you.

Share via

Ingest multiple xml files through ADF which has different schemas

1 answer

Your answer