============ TSQL 中将XML列转换成逗号分隔的字符串 ===========

Question

有以下tsqldeclare @category table
(
id int,
categoryname nvarchar(50),
allow xml
);
insert into @category values(1,'分类1','<r><u name=''user1'' /><u name=''user2'' /></r>');
insert into @category values(2,'分类3','<r><u name=''user1'' /><u name=''user2'' /><u name=''user3'' /></r>');select * from @category;--我想得到下面的结果，请问该如何写tsql
--也就是将allow字段下的u节点的name属性用逗号拼起来1,'分类1','user1,user2'
2,'分类3','user1,user2,user3'

Answer 1

declare @category table
(
id int,
categoryname nvarchar(50),
allow xml
)insert into @category values(1,'分类1','<r><u name=''user1'' /><u name=''user2'' /></r>');
insert into @category values(2,'分类3','<r><u name=''user1'' /><u name=''user2'' /><u name=''user3'' /></r>');
with t as
(select id,categoryname,
 o.value('@name','varchar(10)') as name
 from @category a
 cross apply allow.nodes('/r/u') x(o)
)
select t1.id,t1.categoryname,
       stuff((select ','+name from t t2 
              where t2.id=t1.id and t2.categoryname=t1.categoryname 
              for xml path('')),1,1,'') name
from t t1
group by t1.id,t1.categoryname/*
id          categoryname   name
----------- ------------ ---------------------
1           分类1          user1,user2
2           分类3          user1,user2,user3(2 row(s) affected)
*/

Answer 2

--> 测试数据：[tb]
IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]
GO 
CREATE TABLE [tb]([id] INT,[title] VARCHAR(100),[keywords] XML)
INSERT [tb]
SELECT 1,'叙利亚“救危”总理叛逃对阿萨德政权影响几何','<r><i>叙利亚</i><i>阿萨德</i></r>' UNION ALL
SELECT 2,'难民大量涌入宗教冲突恶化——叙利亚危机殃及周边国家','<r><i>叙利亚</i></r>' UNION ALL
SELECT 3,'以色列执意打击伊朗','<r><i>伊朗</i></r>' UNION ALL
SELECT 4,'利比亚即将尘埃落定,叙利亚何去何从','<r><i>叙利亚</i><i>利比亚</i></r>' UNION ALL
SELECT 5,'国际人权组织就叙利亚阿勒颇轰炸发出警告','<r><i>阿萨德</i></r>'
--------------开始查询--------------------------declare @id int;
set @id=1;SELECT b.id FROM 
(
SELECT [id],t.value('(.)[1]', 'varchar(50)') [keywords] FROM tb CROSS
APPLY [keywords].nodes('/r/i') AS R(t)where id=1
) AS a
JOIN 
(
SELECT [id],t.value('(.)[1]', 'varchar(50)') [keywords] FROM tb CROSS
APPLY [keywords].nodes('/r/i') AS R(t)where id!=@id
) AS b
ON a.[keywords]=b.[keywords]

Answer 3

declare @id int;
set @id=1;SELECT t.* FROM 
(
SELECT [id],t.value('(.)[1]', 'varchar(50)') [keywords] FROM tb CROSS
APPLY [keywords].nodes('/r/i') AS R(t)where id=1
) AS a
JOIN 
(
SELECT [id],t.value('(.)[1]', 'varchar(50)') [keywords] FROM tb CROSS
APPLY [keywords].nodes('/r/i') AS R(t)where id!=@id
) AS b
ON a.[keywords]=b.[keywords]
JOIN tb AS t
ON b.id=t.id
 

Answer 4

declare @category table
(
id int,
categoryname nvarchar(50),
allow xml
);
insert into @category values(1,'分类1','<r><u name=''user1'' /><u name=''user2'' /></r>');
insert into @category values(2,'分类3','<r><u name=''user1'' /><u name=''user2'' /><u name=''user3'' /></r>');;WITH  cte
AS (
     SELECT id, categoryname, t.value('@name', 'varchar(50)') allow
     FROM   @category
     CROSS
APPLY allow.nodes('/r/u') AS R ( t )
   )
SELECT DISTINCT
    id, categoryname, allow = STUFF((
                                      SELECT ','+ allow FROM cte WHERE id= t.id AND categoryname= t.categoryname 
                                      FOR XML PATH('')
                                    ), 1, 1, '')
FROM   cte AS t