Parsing more than one value from XML element-CodePudding

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?

CodePudding user response：

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

CodePudding user response：

Please try the following solution.

XQuery is very powerful language. It is very easy to get a comma separated list out of XML in one shot.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, CustomFieldsXML xml);
INSERT INTO @tbl ([CustomFieldsXML])
VALUES
('<values>
    <SomeFieldName>
    <item>
      <Key>ph</Key>
      <Value>Philippines</Value>
    </item>
    <item>
      <Key>my</Key>
      <Value>Malaysia</Value>
    </item>
  </SomeFieldName>
</values>');
-- DDL and sample data population, end

SELECT t.*
    , REPLACE(c.query('data(item/Value/text())')
        .value('text()[1]', 'NVARCHAR(MAX)')
        , SPACE(1), ',') AS countries
FROM @tbl AS t
    CROSS APPLY CustomFieldsXML.nodes('/values/SomeFieldName') AS t1(c);

Output

 ---- ---------------------- 
| ID |      countries       |
 ---- ---------------------- 
|  1 | Philippines,Malaysia |
 ---- ----------------------