Parse Web activity XML reponse and filter

Subashri Vasudevan 11,226 Reputation points
2022-05-15T09:17:03.477+00:00

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.

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
11,623 questions
0 comments No comments
{count} votes

Accepted answer
  1. ShaikMaheer-MSFT 38,546 Reputation points Microsoft Employee Moderator
    2022-05-17T09:18:13.307+00:00

    Hi @Subashri Vasudevan ,

    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.
    202620-image.png

    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.

    1 person found this answer helpful.

0 additional answers

Sort by: Most helpful

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.