How to keep or add quotation marks in dynamic content (Azure Data Factory) without escape characters being added?

Question

I have a third party API I need to call in a copy activity in Data Factory. I use dynamic content for the request body, because i need one field to be the output of a prior lookup in the pipeline.

The part of the request body needs to look like this:

< Filter>[List]="536,323,235,674"< /Filter>

The problem is that the request body needs the quotation marks to work, and i cannot run it without escape characters being added automatically. The value of the list comes from a lookup activity in an SQL database, so my dynamic request body looks like this:

< Filter>[List]=',activity('Lookup1').output.value.list,'< /Filter>

Where activity('Lookup1').output.value.list = "536,323,235,674" OR activity('Lookup1').output.value.list = 536,323,235,674

I've tried different variations of working around the escape characters using '\"' and similar. I've also tried with functions such as substring and replace to prevent the request body to add escape characters, but nothing works.

The input of the copy data activity always comes out like this: < Filter>[List]=\"536,323,235,674\"< /Filter>

How can i prevent these backslashes?

I really appreciate any help here.

Picture steps:

I can edit my query to retrieve the exact string i need

When i click preview data, it looks perfect.

However, the lookup activity adds backslashes in the input

And so my output looks like this, and is useless in the copy data activity

I don't know, but it looks to me like i cannot fix this issue in the lookup-activity, but rather have to do it in the dynamic content field of the request body. Not sure of course, im pretty new to data factory

Answer 1

In string type, Azure data factory will add '\' to escape double quotes, we've tried several ways to remove it but failed.Now we've posted this issue in Microsofts Q&A and we are waiting for reply.