大家都知道在SQL Server中利用 FOR XML PATH 语句能够把查询的数据生成XML数据&＃xff0c;下面是它的一些应用示例。
DECLARE &＃64;TempTable table(UserID int , UserName nvarchar(50));
insert into &＃64;TempTable (UserID,UserName) values (1,&＃39;a&＃39;)
insert into &＃64;TempTable (UserID,UserName) values (2,&＃39;b&＃39;)
select UserID,UserName from &＃64;TempTable FOR XML PATH
insert into &＃64;TempTable (UserID,UserName) values (1,&＃39;a&＃39;)
insert into &＃64;TempTable (UserID,UserName) values (2,&＃39;b&＃39;)
select UserID,UserName from &＃64;TempTable FOR XML PATH
运行这段脚本&＃xff0c;将生成如下结果&＃xff1a;
<row>
<UserID>1UserID>
<UserName>aUserName>
row>
<row>
<UserID>2UserID>
<UserName>bUserName>
row>
<UserID>1UserID>
<UserName>aUserName>
row>
<row>
<UserID>2UserID>
<UserName>bUserName>
row>
大家可以看到两行数据生成了两个节点&＃xff0c;修改一下PATH的参数&＃xff1a;
select UserID,UserName from &＃64;TempTable FOR XML PATH(&＃39;lzy&＃39;)
再次运行上述脚本&＃xff0c;将生成如下的结果&＃xff1a;
<lzy>
<UserID>1UserID>
<UserName>aUserName>
lzy>
<lzy>
<UserID>2UserID>
<UserName>bUserName>
lzy>
<UserID>1UserID>
<UserName>aUserName>
lzy>
<lzy>
<UserID>2UserID>
<UserName>bUserName>
lzy>
可以看到节点变成&＃xff0c;其实PATH() 括号内的参数是控制节点名称的&＃xff0c;这样的话大家可以看一下如果是空字符串&＃xff08;不是没有参数&＃xff09;会是什么结果?
select UserID,UserName from &＃64;TempTable FOR XML PATH(&＃39;&＃39;)
执行上面这段脚本将生成结果&＃xff1a;
<UserID>1UserID>
<UserName>aUserName>
<UserID>2UserID>
<UserName>bUserName>
<UserName>aUserName>
<UserID>2UserID>
<UserName>bUserName>
这样就不显示上级节点了&＃xff0c;大家知道在 PATH 模式中&＃xff0c;列名或列别名被作为 XPath 表达式来处理&＃xff0c;也就是说&＃xff0c;是列的名字&＃xff0c;这样大胆试验一下不给指定列名和别名会是怎么样&＃xff1f;
select CAST(UserID AS varchar) &＃43; &＃39;&＃39;,UserName &＃43; &＃39;&＃39; from &＃64;TempTable FOR XML PATH(&＃39;&＃39;)
运行上面这句将生成结果
1a2b
所有数据都生成一行&＃xff0c;而且还没有连接字符&＃xff0c;这样的数据可能对大家没有用处&＃xff0c;还可以再变化一下&＃xff1a;
select CAST(UserID AS varchar) &＃43; &＃39;,&＃39;,UserName &＃43; &＃39;&＃39;,&＃39;;&＃39; from &＃64;TempTable FOR XML PATH(&＃39;&＃39;)
生成结果
1,a;2,b;
大家现在明白了吧&＃xff0c;可以通过控制参数来生成自己想要的结果&＃xff0c;例如&＃xff1a;
select &＃39;{&＃39; &＃43; CAST(UserID AS varchar) &＃43; &＃39;,&＃39;,&＃39;"&＃39; &＃43;UserName &＃43; &＃39;"&＃39;,&＃39;}&＃39; from &＃64;TempTable FOR XML PATH(&＃39;&＃39;)
生成结果
{1,"a"}{2,"b"}
还可以生成其他格式&＃xff0c;大家可以根据自己需要的格式进行组合。
下面是一个数据统计的应用&＃xff0c;希望大家可以通过下面的实例想到更多的应用
DECLARE &＃64;T1 table(UserID int , UserName nvarchar(50),CityName nvarchar(50));
insert into &＃64;T1 (UserID,UserName,CityName) values (1,&＃39;a&＃39;,&＃39;上海&＃39;)
insert into &＃64;T1 (UserID,UserName,CityName) values (2,&＃39;b&＃39;,&＃39;北京&＃39;)
insert into &＃64;T1 (UserID,UserName,CityName) values (3,&＃39;c&＃39;,&＃39;上海&＃39;)
insert into &＃64;T1 (UserID,UserName,CityName) values (4,&＃39;d&＃39;,&＃39;北京&＃39;)
insert into &＃64;T1 (UserID,UserName,CityName) values (5,&＃39;e&＃39;,&＃39;上海&＃39;)
SELECT B.CityName,LEFT(UserList,LEN(UserList)-1) FROM (
SELECT CityName,
(SELECT UserName&＃43;&＃39;,&＃39; FROM &＃64;T1 WHERE CityName&＃61;A.CityName FOR XML PATH(&＃39;&＃39;)) AS UserList
FROM &＃64;T1 A
GROUP BY CityName
) B
insert into &＃64;T1 (UserID,UserName,CityName) values (1,&＃39;a&＃39;,&＃39;上海&＃39;)
insert into &＃64;T1 (UserID,UserName,CityName) values (2,&＃39;b&＃39;,&＃39;北京&＃39;)
insert into &＃64;T1 (UserID,UserName,CityName) values (3,&＃39;c&＃39;,&＃39;上海&＃39;)
insert into &＃64;T1 (UserID,UserName,CityName) values (4,&＃39;d&＃39;,&＃39;北京&＃39;)
insert into &＃64;T1 (UserID,UserName,CityName) values (5,&＃39;e&＃39;,&＃39;上海&＃39;)
SELECT B.CityName,LEFT(UserList,LEN(UserList)-1) FROM (
SELECT CityName,
(SELECT UserName&＃43;&＃39;,&＃39; FROM &＃64;T1 WHERE CityName&＃61;A.CityName FOR XML PATH(&＃39;&＃39;)) AS UserList
FROM &＃64;T1 A
GROUP BY CityName
) B
生成结果&＃xff08;每个城市的用户名&＃xff09;
北京 b,d
上海 a,c,e
类别&＃xff1a;Sqlserver 查看评论
京公网安备 11010802041100号