Parse XML string in Azure Dataflow

Question

I have an XML file stored as a string as follow

<offices result="1">      
<office name="Company A" shortname="A">ABC01</office>   
<office name="Company B" shortname="B">ABC02</office>   
<office name="Company C" shortname="C">ABC03</office>   
</offices>

I want to parse this string using Parse step in Azure Dataflow into 3 columns consecutively, name, shortname, and ID (which is the ABC01 code). At the moment I can only take 2 columns name and shortname using this expression in Output column type

(offices as ({@result} as integer,
        office as ({@name} as string,
                    {@shortname} as string)[]))

How can I edit this to add the code in as well?

Answer 1

For the provided XML content, you can use an expression that adds the content of the <office> tag to the parsed output.

Your expression might look something like this:

(offices as ({@result} as integer,
        office as ({@name} as string,
                    {@shortname} as string,
                    $value as string)[]))

Here $value is used to grab the content of the <office> tag, which should give you the desired code (for example"ABC01").

Answer 2

Hi Quyen Dang ,

Thankyou for using Microsoft Q&A platform and thanks for posting your question here.

As per my understanding, you want to convert xml properties into columns using dataflow. Please let me know if that is not the case.

Instead of parse , you can use flatten transformation and unroll by 'office' array and select the columns from the dropdown and give suitable names for the flattened columns

Learn more about parse and flatten transformation here:

Parse Transformation in Mapping Data Flow in Azure Data Factory

Flatten Transformation in Mapping Data Flow in Azure Data Factory

Hope it helps. Please accept the answer by clicking on Accept answer button. Thankyou