How to connect the values of peer nodes in t-sql
declare @x as xml ='<root> <n1>hello world</n1> <n2>A0001</n2> <n2>A0002</n2> <n2>A0003</n2> <n2>A0004</n2> </root>' select xroot.value('(n1)[1]', 'varchar(255)') as n1, xroot.query('n2/text()') as n2 from @x.nodes('/root') as xmlt1(xroot) The result of this query is
n1 | n2 hello world | A0001A0002A0003A0004 But I want the following result, how can I write t-sql
n1 | n2 hello world | A0001,A0002,A0003,A0004 
You can try this:
declare @x as xml ='<root> <n1>hello world</n1> <n2>A0001</n2> <n2>A0002</n2> <n2>A0003</n2> <n2>A0004</n2> </root>'; SELECT @x.value('(/root/n1/text())[1]','nvarchar(max)') AS n1 ,@x.query('data(/root/n2)').value('.','nvarchar(max)'); It’s a pitty, that data() does not allow to specify the delimiter. It will always be a blank. But you can use REPLACE():
SELECT @x.value('(/root/n1/text())[1]','nvarchar(max)') AS n1 ,REPLACE(@x.query('data(/root/n2)').value('.','nvarchar(max)'),' ',','); The backdraw: If your values may include a blank, this will fail…
You can use XQuery instead:
SELECT @x.value('(/root/n1/text())[1]','nvarchar(max)') AS n1 ,STUFF( @x.query('for $n2 in /root/n2/text() return <x>{concat(",",$n2)}</x>').value('.','nvarchar(max)'),1,1,''); In this approach we use a FLWOR-query to run through <n2> elements and create a new XML where the content is extended with a comma:
<x>,A0001</x> <x>,A0002</x> <x>,A0003</x> <x>,A0004</x> This we can read as one with the XQuery path '.'. Removing the leading comma uses STUFF() (as similar string aggregation approaches do). We could use XQuery sub-string() too:
SELECT @x.value('(/root/n1/text())[1]','nvarchar(max)') AS n1 ,@x.query('for $n2 in /root/n2/text() return <x>{concat(",",$n2)}</x>') .value('substring(.,2,1000)','nvarchar(max)') 
Try the following. Get the values and then concatenate them:
declare @x as xml ='<root> <n1>hello world</n1> <n2>A0001</n2> <n2>A0002</n2> <n2>A0003</n2> <n2>A0004</n2> </root>'; WITH DataSource (n1, n2, n2order) AS ( select Tn1.c.value('local-name(.)', 'varchar(128)') ,Tn2.c.value('(.)[1]', 'varchar(128)') ,ROW_NUMBER() OVER (ORDER BY Tn2.c ASC) from @x.nodes('root/n1') Tn1(c) CROSS APPLY @x.nodes('root/n2') Tn2(c) ) SELECT DISTINCT DS1.n1 ,DS.n2 FROM DataSource DS1 CROSS APPLY ( SELECT STUFF ( ( SELECT ',' + n2 FROM DataSource DS2 WHERE DS2.n1 = DS1.n1 ORDER BY n2order FOR XML PATH(''), TYPE ).value('.', 'VARCHAR(MAX)') ,1 ,1 ,'' ) ) DS (n2); If you are using SQL Server 2017+, you can use:
WITH DataSource (n1, n2) AS ( select Tn1.c.value('local-name(.)', 'varchar(128)') ,Tn2.c.value('(.)[1]', 'varchar(128)') from @x.nodes('root/n1') Tn1(c) CROSS APPLY @x.nodes('root/n2') Tn2(c) ) SELECT n1 ,STRING_AGG(n2, ',') as n2 FROM DataSource GROUP BY n1; but the order of nodes here is not guaranteed during the concatenation.
