如何使用 SQL XQuery 将所有元素的特定属性连接在一起?
我有一个这样的表:
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"/>
并需要这个结果=>
InvoiceId IdDetails
--------- ---------------------
1001 101,102,103
1002 201,202
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
仅具有唯一值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
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)