How to connect the values of peer nodes in t-sql

user_10518284 0
Viewed 31
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
xquery-sql xml tsql
2 Answers

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.

user_10803540

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)')
user_50892040
Related