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

Question

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

score 1 · Answer 1 · answered Jun 29 '20 at 05:54

1

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.

answered Jun 29 '20 at 05:54

gotqn

42,737
46
157
243

Is there no easier way? I know it is written like this, but it is too inelegant, I think there is something like `xroot.query('n2/text(,)')` or `xroot.query('n2/join(,)')` – Brucelin Michael Jun 29 '20 at 06:43
@BrucelinMichael As you can see there is not built-in string aggregation in the xquery - https://learn.microsoft.com/en-us/sql/xquery/xquery-language-reference-sql-server?view=sql-server-ver15 – gotqn Jun 29 '20 at 06:48
@gotqn, well, there is `data()`, which will combine all content with a blank as delimiter. It's a pitty, that the developers did not allow to specify this delimiter. And we might use FLWOR queries. – Shnugo Jun 29 '20 at 07:07
@Shnugo Xm.. never use it. What' if you add somehow nodes with the separator between the original ones and use the function? – gotqn Jun 29 '20 at 07:10
@gotqn, `data()` will just return the content in the given order. You can add separators in nodes, but you would get additional blanks. The OP shows, that a simple `.query()` will return the string *as one*, but without any separator. This is how string agg via XML works. `data()` is roughly the same, but it will add the blank between the items. As long as your content is free of blanks we can use this together with `REPLACE()` (as shown in my answer). – Shnugo Jun 29 '20 at 07:16

Shnugo · Accepted Answer · 2020-06-29T07:00:30.820

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

Thank you very much, for my current usage scenario, data() is enough. — Brucelin Michael, Jun 29 '20 at 07:29

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

2 Answers2