Encoding issue with xml_soap_request in copy data pipeline activity

David 96 Reputation points
2021-08-16T13:51:49.257+00:00

We want to copy xml data from a soap post to sql server via a self hosted integration runtime.
As an interim stage we are saving to Avro blob storage from a soap post (we chose avro since xml blob storage can't be used as a sink https://learn.microsoft.com/en-us/azure/data-factory/connector-azure-blob-storage#copy-activity-properties) but the first part of the xml file has an encoding issue (url elements) which prevents this file from being used as an xml mapping source for a subsequent copy pipeline activity. If we manually remove the following problematic elements from the blob file the subsequent copy to sql works fine (mapping works).

How to fix this?
Thanks

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,702 questions
{count} votes

1 answer

Sort by: Most helpful
  1. David 96 Reputation points
    2021-08-17T16:03:52.01+00:00

    The SOAP POST response can't be altered and sinks to AVRO. The source tab in the second copy activity is blob, xml. The xml blob data is manually manipulated to remove the headers (data extracted from the AVRO blob)

    It looks like this is a CDATA incompatibility:
    https://learn.microsoft.com/en-us/answers/questions/76694/azure-data-factory-and-cdata-type-xml-incompatibil.html

    One suggestion in the forum was to use Azure Logic Apps, another was that a data flow could cope with the issue.
    Could you please advise the best way forward, thanks