locked
How To Read XML files using the Copy Activity - Or - How to exceed DelimitedText file row limit of 2MB RRS feed

  • Question

  • When reading XML files into SQL Server using Azure Data Factory you gotta be creative. I used a DelimitedText dataset as a wrapper around XML, but that only works when XML file is 2MB or smaller. I guess the max row length of the dataset is the limit.

    I wrote a post about my approach here: https://medium.com/@OneBitAhead/how-to-not-read-xml-into-sql-server-using-azure-data-factory-ef3afad66c6e

    Is there any way to get around that?

    Thanks
    Stefan

    Tuesday, May 12, 2020 7:39 AM

All replies

  • Hi Stefan,

    Unfortunately as of today, copying data from an XML source is not supported in-house. Good news ! The product team has heard feedback on the same and has started work in that direction. ADF will soon support XML :

    Ref - https://feedback.azure.com/forums/270578-data-factory/suggestions/17508058-xml-file-type-in-copy-activity-along-with-xml-sc

    Tuesday, May 12, 2020 10:38 AM
  • To answer your second question, the 2MB limitation is only for the Lookup activity. For copy activity, there's no such limit.
    Tuesday, May 12, 2020 10:38 AM
  • Good news that native XML support is coming.

    But regarding the process I decribed on medium (https://medium.com/@OneBitAhead/how-to-not-read-xml-into-sql-server-using-azure-data-factory-ef3afad66c6e), where exactly is the length of the data capped to 2MB? I can see that all bytes are read from the source file / dataset, but the XML column in the sink database table only contains part of the data. I tried to use a nvarchar(max) column as target instead, but with the same result: only 2MB of the incoming string is inserted into DB.

    Wednesday, May 13, 2020 12:11 PM