Azure Data Factory - how to parse XML?

Question

I am struggling to understand how to parse a SQL Column which contains an XML formatted string. I have already investigated the tutorial here but it does not provide any useful examples for more complex structures, even something as basic as handling child nodes within the XML.

I have an XML that is similar to the below format, stored inside a varchar column in my table.
<RootNode>
<ChildNode> some value </ChildNode>
</RootNode>

Any material I have found on this subject seems to assume that all the values you need to access are at the root node level and the expression you need for the column structure is e.g.' (ChildNode as string)'. So how do I access child nodes? Am I missing something or is this feature incredibly limited? I have not even managed to find material on similar cases but in JSON format.

Answer 1

Hi @Simonas Stilius ,
Welcome to Microsoft Q&A platform and thankyou for posting your query.
As I understand your query here is that you want to parse the SQL column having XML content. Basically, you want to fetch the ChildNode value from the XML content. Please correct me if my understanding is wrong. Is there any more transformations you want to perform on top of that as I see you want to use Data flow, otherwise you can use ADF pipeline approach.

In the Copy activity , try the following Query in the Query option of Source setting :

    Select Y.value('(ChildNode)[1]','VARCHAR(20)') as ChildNode From    
    ( select cast(xmldata as xml) xmldata from xmldataTable )a  
    Outer Apply a.xmldata.nodes('RootNode') as X(Y)  

Please refer to the following blog post for more details : https://www.sqlshack.com/filtering-xml-columns-using-xquery-in-sql-server/

Hope this will help. Please let us know if any further queries.

------------------------------

• 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
• Want a reminder to come back and check responses? Here is how to subscribe to a notification
• If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators