Parse XML string in ADF

Question

Parse XML string in ADF

TC 1

Hi,
Looking for help to parse an XML string in azure data factory.

the simple example provided here does not include how to extract element attributes
https://learn.microsoft.com/en-gb/azure/data-factory/data-flow-parse

What would the expression be to extract id, name and address from the below XML.

2 answers

Your answer

Answer 1

MarkKromer-MSFT 5,226 Microsoft Employee Moderator

When you go into the expression builder, say from a derived column, do you see the input schema like this?

TC 1 Reputation point

2022-03-15T14:47:03.307+00:00

Thanks for the reply! No I don't see the input schema like that. The XML is a string, so I need to use the parse transformation right?

My source data for the test is:

Select 123 as id, '<Customers>
<Customer id="12222">
<CompanyName Name="Great Lakes Food Market" Address="123"</>
</Customer>
</Customers>' as XMLstring

this is my setup for the parse transformation:
MarkKromer-MSFT 5,226 Reputation points Microsoft Employee Moderator

2022-03-15T21:51:01.3+00:00

That is correct. If your XML is a string inside a text column, then you need to use Parse to extract it. The issue is in your output column type. It should look something like this:

(Customer as ({@id} as integer, CompanyName as ({@Address} as integer, {@DeezNutz } as string)))[]

You can ask ADF to try to infer the output format for your embedded structured strings in the expression builder. There is a "detect type" button on the top when you open the Output column type expression builder.
TC 1 Reputation point

2022-03-16T15:33:42.437+00:00

Thanks a lot, working for me now!
fyi the "detect type" button only appears for me when the parse format is JSON. With XML I don't see it.
MartinJaffer-MSFT 26,236 Reputation points

2022-03-16T18:15:07.813+00:00

Hello @TC . Thank you for letting us know MarkKromer-MSFT 's response helped.
If it solved your issue, please don't forget to click on or upvote button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how

Answer 2

Hi,
Would I be getting any inputs to parse a xml within a string ?
<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">

soap:Body

</DataTable> </DocumentElement></ExtractDataResult>

</ExtractDataResponse>

</soap:Body>

</soap:Envelope>

over here, the xml is encapsulated within a string !
and the data in the landing is also a string !
below is the output in my blob storage. is there a way I can parse this ?
Thanks !

"<DocumentElement>
  <DataTable>
    <someID>1237563</someID>
    <someother>24139</someother>
  </DataTable>
</DocumentElement>"

Share via

Parse XML string in ADF

2 answers

Your answer