Parse Web activity XML reponse and filter

Question

I have a web activity to read blob properies, the output is an XML Response.

The XML response looks like this,

{
"Response":
"\
",
"ADFWebActivityResponseHeaders": {
"Transfer-Encoding": "chunked",
"x-ms-request-id": "fd1c17ec-801e-006f-613a-686455000000",
"x-ms-version": "2020-04-08",
"Date": "Sun, 15 May 2022 09:01:47 GMT",
"Server": "Windows-Azure-Blob/1.0;Microsoft-HTTPAPI/2.0",
"Content-Type": "application/xml"
},
"effectiveIntegrationRuntime": "AutoResolveIntegrationRuntime (East US)",
"executionDuration": 0,
"durationInQueue": {
"integrationRuntimeQueue": 0
},
"billingReference": {
"activityType": "ExternalActivity",
"billableDuration": [
{
"meterType": "AzureIR",
"duration": 0.016666666666666666,
"unit": "Hours"
}
]
}
}

I want to filter blobs with Access tier Cool\Hot.
I want to ignore Archive tier blobs.

How can I filter the blob name alone which is in Cool or Hot tier?

Please advise.

Answer 1

Hi @Suba Balaji ,

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

Here in your response your xml data actually coming as string. Response is json key, in which you have your entire XML as string.

In this case to work with XML string we can consider using xpath() function in azure data factory expressions.

xpath() function check XML for nodes or values that match an XPath (XML Path Language) expression, and return the matching nodes or values. An XPath expression, or just "XPath", helps you navigate an XML document structure so that you can select nodes or compute values in the XML content.

Kindly check below xpath() function documentation which explains very detail about its usage along with examples.
https://learn.microsoft.com/en-us/azure/data-factory/control-flow-expression-language-functions#xpath

Another work around I can think of is load this XML string to some SQL table column. In SQL we can play with XML strings.

Kindly check below sample article which gives idea about working with XML strings in SQL.
https://www.sqlshack.com/working-with-xml-data-in-sql-server/

Hope this helps. Please let me know if any queries.

----------

Please consider hitting Accept Answer. Accepted answers help community as well.