How to remove escaped character when parsing xml to json with copy data activity in Azure Data Facto-CodePudding

I have an ADF pipeline exporting from xml dataset (ADLS) to json dataset (ADLS) with a copy Data activity. Due to the complex xml structure, I need to parse the nested xml to nested json then use T-SQL to parse the nested json into Synapse table.

However, the output nested has double backslash (It seems like escape characters) at nodes which have comma in it. You can check a sample of xml input and json output below:

xml input

<Address2>Centro, COLONIA CENTRO DE LA CIUDAD DE</Address2>

json output

"Address2":"Centro\\, COLONIA CENTRO DE LA CIUDAD DE"

How can I remove the double backslash in the output json with copy data activity in Azure Data Factory ?

CodePudding user response：

Unfortunately there is no such provision in CopyData Activity.

However, I just tried with just the lines you provided as sample source and sink with CopyData Activity and it just copies as is. I don't see any \\. Perhaps you could share the exact pipeline you have, with details of the nested XML, JSON and T-SQL that you are using.

Repro: (with all default settings and properties)