Retreiving XML tab value from JSON response object in Azure Data Factory Web Activity

Question

Retreiving XML tab value from JSON response object in Azure Data Factory Web Activity

sachin gupta 376

Hello,

I have created a web activity which is calling the Bings API. The response is coming as a JSON. One of the JSON attribute value is an XML string and I need to retrieve the value of "ReportRequestId" from the attached response. I tried couple of things like converting the XML into JSON and then retrieving the nested values from the json object, but nothing worked. Below are the few expressions which I tried Can someone guide on how I can get this value?

@json (xml(activity('WebActivity').output.Response)).Envelope.Body.SubmitGenerateReportResponse.ReportRequestId

@json (replace(string(xml(activity('WebActivity').output.Response)), ':','')).Envelope.Body.SubmitGenerateReportResponse.ReportRequestId

sachin gupta 376 Reputation points

2021-12-22T13:49:15.907+00:00

@Saurabh Sharma
@ShaikMaheer-MSFT
@Sadiqh Ahmed

can you guys help on this issue?
Sankhe Sagar 6 Reputation points

2022-08-07T10:53:37.147+00:00

I was facing similar issue today. I tried using replace along with xml() and xpath() functions. They did not help.

finally I had luck with using substring() function. This function needs string, starting index and number of characters..

@substring(string(activity('GetToken').output.Response) //get token is web activity that gives token
,add(indexof(string(activity('GetToken').output.Response),'<P_TOKEN xsi:type="xsd:string">'),31) //31 is length of this search string.
,sub(indexof(string(activity('GetToken').output.Response),'</P_TOKEN>'),add(indexof(string(activity('GetToken').output.Response),'<P_TOKEN xsi:type="xsd:string">'),31))
) //to obtain number of characters.

I hope this helps you.

1 answer

Your answer

sachin gupta 376 Reputation points

2021-12-22T13:49:15.907+00:00

@Saurabh Sharma
@ShaikMaheer-MSFT
@Sadiqh Ahmed

can you guys help on this issue?
Sankhe Sagar 6 Reputation points

2022-08-07T10:53:37.147+00:00

I was facing similar issue today. I tried using replace along with xml() and xpath() functions. They did not help.

finally I had luck with using substring() function. This function needs string, starting index and number of characters..

@substring(string(activity('GetToken').output.Response) //get token is web activity that gives token
,add(indexof(string(activity('GetToken').output.Response),'<P_TOKEN xsi:type="xsd:string">'),31) //31 is length of this search string.
,sub(indexof(string(activity('GetToken').output.Response),'</P_TOKEN>'),add(indexof(string(activity('GetToken').output.Response),'<P_TOKEN xsi:type="xsd:string">'),31))
) //to obtain number of characters.

I hope this helps you.

Answer 1

svijay-MSFT 5,256 Microsoft Employee Moderator

Hello @sachin gupta ,

Thanks for the question and using MS Q&A platform.

You could try use the xpath

Reference : https://learn.microsoft.com/en-us/azure/data-factory/control-flow-expression-language-functions#xpath

xpath path function check XML for nodes or values that match an XPath (XML Path Language) expression, and return the matching nodes or values.

You could share me the sample xml output here as a text so that I could try it at my end.

svijay-MSFT 5,256 Reputation points Microsoft Employee Moderator

2022-01-05T06:14:12.277+00:00

Hello @sachin gupta ,

Just checking in to see if the above answer helped. If this answers your query, do click Accept Answer and Up-Vote for the same. And, if you have any further query do let us know.

Share via

Retreiving XML tab value from JSON response object in Azure Data Factory Web Activity

1 answer

Your answer