Concatenate specific attribute of all elements together with xquery

Question

How can I concatenate a specific attribute of all elements together using SQL XQuery?

I have a table like this:

InvoiceId   Details
---------   -----------------------------------------------------------------
1001        <Stuff Id="101" p="3" q="5"/><Stuff Id="102" q="4"/><Stuff Id="103"/>
1002        <Stuff Id="201" /><Stuff Id="202" q="2"/>

and need this result =>

InvoiceId  IdDetails
---------   ---------------------
1001        101,102,103
1002        201,202

score 2 · Answer 1 · answered May 29 '11 at 10:00

2

Simplest way would be to use REPLACE.

SELECT
   InvoiceId,
   SUBSTRING(
        REPLACE(
          REPLACE(
            CAST(Details AS nvarchar(max))
                 , '<Stuff Id="', ',')
               , '"/>', '')
             ,2, 2000000000)
FROM
   MyTable

answered May 29 '11 at 10:00

gbn

422,506
82
585
676

we can't using Replace method,because the Stuff Element may have some other attributes in some cases! then how can get same result using XQuery?! – ARZ May 29 '11 at 10:08
@ARZ: Do you know what the elements are called? Is it a finite list or do you want to take any old XML and parse it...? – gbn May 29 '11 at 10:11
really it's not a finite list. – ARZ May 29 '11 at 10:23
@ARZ: Ok, so you don't know what the XML looks like? Also, I'm not sure this is valid XML in SQL Server terms: no root element. Is it stored in XML type or nvarchar? – gbn May 29 '11 at 10:26
Finally, to extract tag names, you may be able to adopt this http://stackoverflow.com/questions/6115397/use-xquery-to-get-at-this-data.- YMMV – gbn May 29 '11 at 10:30

Mikael Eriksson · Accepted Answer · 2011-05-29T13:09:17.640

declare @T table(InvoiceId int, Details xml)
insert into @T
select 1001, '<Stuff Id="101" p="3" q="5"/><Stuff Id="102" q="4"/><Stuff Id="103"/>' union all
select 1002, '<Stuff Id="201"/><Stuff Id="202" q="2"/>'

select InvoiceId,
       stuff((select ','+t.n.value('@Id', 'varchar(10)')
              from Details.nodes('Stuff') as t(n) 
              for xml path('')), 1, 1, '') as IdDetails  
from @T

Only unique values for Id.

select InvoiceId,
       stuff((select ','+s.id
              from Details.nodes('Stuff') as t(n)
                cross apply (select t.n.value('@Id', 'varchar(10)')) as s(id) 
              group by s.id
              for xml path('')), 1, 1, '') as IdDetails  
from @T

many many thanks. now, how can we distinct the IdDetails at the result?? — ARZ, May 29 '11 at 12:52

score 0 · Answer 3 · edited May 23 '17 at 11:44

Perhaps a nicer way is to use the query function in the xml processing instead of a subselect and stuff. Building on Mikael Eriksson's answer, I found the following from this question to be quicker and more concise/readble:

declare @T table(InvoiceId int, Details xml)
insert into @T
select 1001, '<Stuff Id="101" p="3" q="5"/><Stuff Id="102" q="4"/><Stuff Id="103"/>' union all
select 1002, '<Stuff Id="201"/><Stuff Id="202" q="2"/>'

select InvoiceId, 
    t.n.query('for $i in Stuff return concat($i/@Id, ",")').value('.', 'nvarchar(max)')
from @T
    cross apply Details.nodes('.') as t(n)

However, the resulting strings include delimiting spaces and a terminating comma.

Concatenate specific attribute of all elements together with xquery

3 Answers3