Querying multiple rows of an XML column to extract child nodes into multiple rows

Question

In SQL server, I have a table with an XML column, and from each XML, I have nodes under a parent node and I want to select each value in the node into a separate row for each XML.

How do I accomplish that?

Below is a sample of how the table looks:

Name    | Message
John    | <User><Data><Valuelist><Value>123</Value><Value>456</Value><Value>789</Value><Value>654</Value></ValueList></Data></User>
Jack    | <User><Data><ValueList><Value>555</Value><Value>455</Value></ValueList></Data></User>
Jane    | <User><Data><Valuelist><Value>576</Value><Value>854</Value><Value>933</Value></ValueList></Data></User>

Below is a sample XML from the Message column I'm trying to query from, for better clarity:

<User>
     <Data>
          <ValueList>
              <Value>123</Value>
              <Value>456</Value>
              <Value>789</Value>
              <Value>654</Value>
          </ValueList>
     </Data>
</User>

And below is what I expect to see in the result:

Name    | Values
John    | 123
John    | 456
John    | 789
John    | 654
Jack    | 555
Jack    | 455
Jane    | 576
Jane    | 854
Jane    | 933

I have been attempting to use the below query:

select t.Name, x.y.value('(Value)', 'nvarchar(10)') [Values]
from TABLE t
cross apply t.Message.nodes('//ValueList') as x(y)

But it gives me the below error:

'value()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *'

I'm only able to select one of the values using:

select t.Name, x.y.value('(Value[1])', 'nvarchar(10)') [Values]
from TABLE t
cross apply t.Message.nodes('//ValueList') as x(y)

I have been googling around but couldn't quite find something for what I'm trying to do.

What should I do here?

My SQL server version is 2016 SP2.

Thanks in advance!

Answer 1

Please try the following solution.

SQL

-- DDL and sample data population, start
DECLARE @tbl table (ID INT IDENTITY PRIMARY KEY, Name VARCHAR(20), Message XML);
INSERT INTO @tbl (Name, Message) VALUES
('John', N'<User><Data><ValueList><Value>123</Value><Value>456</Value><Value>789</Value><Value>654</Value></ValueList></Data></User>'),
('Jack', N'<User><Data><ValueList><Value>555</Value><Value>455</Value></ValueList></Data></User>'),
('Jane', N'<User><Data><ValueList><Value>576</Value><Value>854</Value><Value>933</Value></ValueList></Data></User>');
-- DDL and sample data population, end

SELECT t.ID, t.Name
    , c.value('(./text())[1]', 'nvarchar(10)') AS [Values]
FROM @tbl AS t
CROSS APPLY Message.nodes('/User/Data/ValueList/Value') as t1(c);

Output

+----+------+--------+
| ID | Name | Values |
+----+------+--------+
|  1 | John |    123 |
|  1 | John |    456 |
|  1 | John |    789 |
|  1 | John |    654 |
|  2 | Jack |    555 |
|  2 | Jack |    455 |
|  3 | Jane |    576 |
|  3 | Jane |    854 |
|  3 | Jane |    933 |
+----+------+--------+