Azure Synapse Copy pipeline for ingesting complex XML

Question

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

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