XML Values to retrieve in single Column with Any(,) Delimiter (2)

Question

declare @x xml =
'<Detials xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <Tests>
  <Test Name="Test1" TotalMarks="100">95</Test>
  <Test Name="Test2" TotalMarks="200">65</Test>
  <Test Name="Test3" TotalMarks="150">95</Test>
  <Test Name="Test4" TotalMarks="150"></Test>
   </Tests>
   <Tests>
  <Test Name="Test1" TotalMarks="100">95</Test>
  <Test Name="Test2" TotalMarks="200">65</Test>
  <Test Name="Test3" TotalMarks="150">95</Test>
  <Test Name="Test4" TotalMarks="150"></Test>
    </Tests>
</Detials>'

When i queried like this

SELECT STUFF(
    @x.query('for $a in (*:Detials/Tests/Test/@Name) 
        return <a>{concat(",", $a)}</a>')
    .value('.', 'NVARCHAR(MAX)'), 
    1, 1, '') AS ListOfName

I get like this

ListofName
Test1,Test2,Test3,Test4,Test1,Test2,Test3,Test4

But Want to shred the xml based on the <Test> Which can give the result like this below

ListofName
Test1,Test2,Test3,Test4
Test1,Test2,Test3,Test4

Please help me here to shred original XML into separate rows before string concatenation with query() and value()

Thanks in Advance ,Jayendran

Bogdan Sahlean Bogdan Sahlean · Accepted Answer · 2017-06-09T08:12:23

Try this solution:

declare @x xml =
'<Detials xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <Tests>
  <Test Name="Test1" TotalMarks="100">95</Test>
  <Test Name="Test2" TotalMarks="200">65</Test>
  <Test Name="Test3" TotalMarks="150">95</Test>
  <Test Name="Test4" TotalMarks="150"></Test>
   </Tests>
   <Tests>
  <Test Name="Test1" TotalMarks="100">95</Test>
  <Test Name="Test2" TotalMarks="200">65</Test>
  <Test Name="Test3" TotalMarks="150">95</Test>
  <Test Name="Test4" TotalMarks="150"></Test>
    </Tests>
</Detials>'

-- Edit 1
SELECT  XmlTable.OriginalID, ROW_NUMBER() OVER(ORDER BY x.XmlCol) AS RowNum, y.ListOfName
FROM (SELECT 1 AS OriginalID, @x AS XmlCol) AS XmlTable -- Edit 2
CROSS APPLY XMLTable.XmlCol.nodes('*:Detials/Tests') AS x(XmlCol)
CROSS APPLY(
    SELECT STUFF(x.XmlCol.query('for $a in (Test/@Name) return <a>{concat(",", $a)}</a>').value('.', 'NVARCHAR(MAX)'), 1, 1, '')
) AS y(ListOfName)
-- End of Edit 1

Demo

XML Values to retrieve in single Column with Any(,) Delimiter (2)

1 Answers