Azure Data Factory XML Parsing - How to access child nodes?

Question

I am trying to parse an XML formatted string inside a column in a SQL database but I am struggling to define the actual node I want to extract in the expression. I have followed the tutorial in this Microsoft tutorial but the examples only consider nodes at the top level.

To clarify this is the expression in the Output column type field where you specify the value you want to extract from the source column that contains the string format of an XML/JSON.

What do I do when I have an XML which is simply like this? :

<XMLRoot>
  <ChildNode> child note string </ChildNode>
</XMLRoot>

Answer 1

SQL Server is quite capable of working with XML, so if you already have one you would be best off manipulating the XML there. A simple example:

DECLARE @xml XML = '<XMLRoot>
  <ChildNode> child note string </ChildNode>
</XMLRoot>'

SELECT @xml.value('(XMLRoot/ChildNode/text())[1]', 'VARCHAR(100)' ) x

Or an example with a table:

DROP TABLE IF EXISTS #tmp;

CREATE TABLE #tmp (
    someValue XML 
);  
    
INSERT INTO #tmp SELECT '<XMLRoot>
  <ChildNode> child note string </ChildNode>
</XMLRoot>';

SELECT x.c.value('.', 'VARCHAR(100)') q
FROM #tmp t
    CROSS APPLY someValue.nodes('(XMLRoot/ChildNode/text())') x(c);