Parsing more than one value from XML element

August 3, 2022

I have a XML column in a SQL Server 2012 database table, and one particular element in the XML stores multiple values. The issue I have is that I don’t know what the max values of those child elements are. It could be 2, 3 or 10 etc.

I need a way to extract the multiple values in the XML element SomeFieldName and return it in one row. I have a simplified example of my issue below:

IF OBJECT_ID('tempdb..#temptable') IS NOT NULL 
     DROP TABLE #temptable

CREATE TABLE #temptable ([CustomFieldsXML] xml)

INSERT INTO #temptable ([CustomFieldsXML])
VALUES
('<values>
    <SomeFieldName>
    <item>
      <Key>ph</Key>
      <Value>Philippines</Value>
    </item>
    <item>
      <Key>my</Key>
      <Value>Malaysia</Value>
    </item>
  </SomeFieldName>
</values>' )

SELECT
    c.CustomFieldsXML,
    c.CustomFieldsXML.value('(/values/SomeFieldName/item/Value)[1]', 'NVARCHAR(MAX)') AS 'Value1',
    c.CustomFieldsXML.value('(/values/SomeFieldName/item/Value)[2]', 'NVARCHAR(MAX)') AS 'Value2'
FROM 
    #temptable AS c

DROP TABLE #temptable;

You can see in my example I know how to statically get the values out one by one into different columns. Is there a way to output these values into one row, without knowing the max number of child elements in the tag "SomeFieldName".

My example only shows two, but I would like to know how to deal with an unknown number of values and concatenate into one row, with one column (so that it returns Philippines, Malaysia). ….Perhaps using a CROSS APPLY? Or is this better suited using XPATH of some kind?

>Solution :

Use the nodes method to get one row per item node, and then you can get the value:

SELECT tt.CustomFieldsXML,
       SFN.i.value('(Key/text())[1]','nvarchar(2)') AS [Key], --Why use MAX here? Can the string really be more than 4,000 characters long?
       SFN.i.value('(Value/text())[1]','nvarchar(50)') AS [Value] --Don't use single quotes for aliases; it's a bad habit and can have unexpected behaviour
FROM #temptable AS tt
     CROSS APPLY tt.CustomFieldsXML.nodes('/values/SomeFieldName/item')SFN(i);