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);