'Azure Synapse Copy pipeline for ingesting complex XML

I have a copy pipeline set up that connects to an SFTP server (on Azure Synapse). I have used it to copy csv files and this works fine, but now I have the complex task to pull in a multi-layered xml file and convert it to something usable in the Gen2 data lake (csv or parquet). I have created my copy pipeline and used various settings but each time, I only get the first record through. Has anyone experienced this before or have any thoughts on how did to resolve this?

Emily

xmlazure-synapseazure-data-lake-gen2ingest


Solution 1:[1]

you need to specify a collection reference in the mapping section of the pipeline.

Example:

<AllEvents>
  <Event>
    <Count>1</Count>
    <Name>John</Name>
  </Event>
  <Event>
    <Count>2</Count>
    <Name>Jane</Name>
  </Event>
</AllEvents>

Then specify '$['AllEvents']['Event']' in the Collection reference field, click on 'Import Schemas' once again and redo the mapping. When you run the pipeline again it should import all the rows.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Jan-Erik Öhman

Related Questions