for xml path 用于合并与拆分
1、合并
很多时候需要在SQL Server中创建逗号分隔列表。这可以使用SQL Server的DOR XML PATH功能完成。与select语句一起使用时，FOR XML PATH生成一个xml。其示例如下所示。
|
1
2
3
4
5
6
|
DECLARE @tb TABLE(sno int identity,col1 varchar(10))
INSERT INTO @tb VALUES('a'),('b'),('c'),('d')
SELECT ',' + col1 from @tb
for xml path('')
|
上面的查询创建了一个表变量，并使用FOR XML PATH对其进行简单的选择。我们案例中的分隔符是“，”。上述查询的输出如下所示。
我们得到一个逗号分隔列表，但是我们仍然需要摆脱“a”之前的第一个逗号。这可以使用STUFF功能完成，如下所示。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
DECLARE @tb TABLE(sno int identity,
col1 varchar(10))
INSERT INTO @tb VALUES('a'),('b'),('c'),('d')
SELECT
STUFF(
(SELECT ',' + col1 from @tb
for xml path('')),
1,
1,
''
) AS Comma_separated_list
|
上面的查询删除了烦人的“，”并返回以下结果。
2、拆分
这是将列表转换为表的快速SQL函数。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
-- sql function to convert list to table
CREATE FUNCTION fn_listtotable
(
@list nvarchar(max),
@delimeter nvarchar(100)
)
RETURNS @split TABLE
(
sno int identity,
value varchar(max)
)
BEGIN
Declare @xml XML
select @xml = cast('<A>'+ replace(@list,@delimeter,
'</A><A>')+ '</A>' as xml)
INSERT INTO @split
select t.value('.','varchar') as inVal
from @xml.nodes('/A') as x(t)
RETURN
END
|
上述查询利用SQL Server的Xquery功能来分割分隔值。该功能可以如下所示使用
