question

David-5689 avatar image
0 Votes"
David-5689 asked ShaikMaheer-MSFT commented

Encoding issue with xml_soap_request in copy data pipeline activity

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://docs.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
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

The deleted xml is (minus tags):

soapenv:Envelope xmlns:soapenv=http://schemas.xmlsoap.org/soap/envelope/ xmlns:xsd=http://www.w3.org/2001/XMLSchema xmlns:xsi=http://www.w3.org/2001/XMLSchema-instance
soapenv:Body
GET_NewDataResponse xmlns="urn:uniface:applic:wrapped:services:ETWS002SS"
return 0 return
OUTPUT ![CDATA[<?xml version="1.0" encoding="UTF-8"?

0 Votes 0 ·

Hi @David-5689 ,

Thank you for posting query in Microsoft Q&A Platform.

It seems problem is with your XML file, which is having some unwanted data at top lines.

Could you please let us know how you are storing your SOAP Post response as xml? Also, could you please try to save your SOAP POST by avoiding unwanted rows at the top?

It would be great if you could share more details on steps you followed so far, so that we can follow along to try same and repro issue.

When you say you are storing SOAP POST as AVRO, which type of dataset you are using in your Source Tab in Copy activity?

I don't see no where you are saving your SOAP POST data as XML file, So in which steps you are removing the problematic rows from XML file?

0 Votes 0 ·

1 Answer

David-5689 avatar image
0 Votes"
David-5689 answered ShaikMaheer-MSFT commented

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://docs.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

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @David-5689 ,

You can go with LogicApps workaround as suggested in your commented post. Thank you.

0 Votes 0 ·

Hi @David-5689 ,

You can mark your above one as accepted answer. Accepted answer will help community as well. Thank you. Please let us know if any further queries.

0 Votes 0 ·