Blog Post

Azure Data Factory Blog
1 MIN READ

Azure Data Factory adds support for XML format

Linda_Wang's avatar
Linda_Wang
Icon for Microsoft rankMicrosoft
Jul 17, 2020

We are glad to announce that now in Azure Data Factory, you can extract data from XML files by using copy activity and mapping data flow. With such capability, you can either directly load XML data to another data store/file format, or transform your XML data and then store the results in the lake or database.

 

XML format is supported on all the file-based connectors as source. You can find the new "XML" option in the available format list:

 

When reading data from XML files, you have the flexibility to define whether to apply schema validation, to specify how to handle namespaces, and to map selective fields or reshape the hierarchy to match downstream data need.

 

Copy activity source example:

 

In mapping data flow, you can use XML as source either via an XML dataset or via inline dataset.

Data flow source example with XML dataset:

 

To learn more about XML support in Azure Data Factory, visit XML format in the documentation site.

Updated Jul 17, 2020
Version 1.0
Azure Data Integration
Copy Activity
Mapping Data Flows

34 Comments

Show More
  • sandeepthachan's avatar
    sandeepthachan
    Copper Contributor
    Jul 29, 2020

    Hi Linda_Wang ,

     

       Is there any limitation to convert the xml files to csv using the copy activity? 1 GB file gets converted within 2 hour without any parallelism/DIU/blocks, but a 2GB file fails with a memory exception.

     

    Any suggestion to process a large xml file.

     

    Thank you

  • WillT985's avatar
    WillT985
    Copper Contributor
    Jul 28, 2020

    As Linda_Wang  described, you can copy the results of the http to data lake. I got this to work with binary and XML. For complex XML support I was able to copy it to a SQL server instance where you can use TSQL XML functions to parse the results, I used binary dataset and copy the results of data lake and copy it to a string in a table, but XML should work too with an XML data type. I haven't tried that yet, but that may be a good solution for complex XML parsing and processing in line instead of saving it to a table. In my case I had to create a loop because there is a limit of rows returned on the soap envelope. But that is another story. Hope this helps.

     

     

     

     

  • Mysticode's avatar
    Mysticode
    Copper Contributor
    Jul 28, 2020

    Linda_Wang Do you have an example of this? I can't find a file type that works with saving the XML to the Sink source. I need it to be something that I can parse through.

  • Linda_Wang's avatar
    Linda_Wang
    Icon for Microsoft rankMicrosoft
    Jul 28, 2020
    Mysticode as it's SOAP API, you can use copy activity with XML format on top of HTTP connector as source, in copy activity "mapping" to map the fields directly to sink database. If you need complex transformation in-between, where Data Flow fits, you can use copy activity to copy to lake then apply Data Flow- config both copy source and sink with XML Binary format, one for HTTP connector one for data lake connector.
  • Mysticode's avatar
    Mysticode
    Copper Contributor
    Jul 27, 2020

    Does anyone have a method to save/sink this retrieved XML data from the HTTP connection? Binary isn't an option, needs to be something that can be parsed out into a database.

  • Mysticode's avatar
    Mysticode
    Copper Contributor
    Jul 24, 2020

    Linda_Wang I am retrieving an XML file from an HTTP connection in Data Factory (from a SOAP API). What is the supported method to sink/save this data to Data Lake in order to run Data Flow transformations on the data?

  • BijuNambiarC's avatar
    BijuNambiarC
    Copper Contributor
    Jul 22, 2020

    Excellent,i was trying to convert an xml file to csv through a Copy Activity pipe line but getting an error ,' Could not find the schema information for the element'.

     

    Any idea about this,can some one help me.

     

    THank you 🙂