FOR XML PATH 简单介绍
FOR XML PATH 语句是将查询结果集以XML形式展现,通常情况下最常见的用法就是将多行的结果,拼接展示在同一行。
首先新建一张测试表并插入数据:
CREATE TABLE #Test (
Name varchar(70),
Hobby varchar(70)
);
insert #Test
select '张三','游泳' union
select '张三','爬山' union
select '张三','游戏' union
select '李四','唱歌' union
select '李四','睡觉'
select * from #Test;
查询结果如下:
接下来应用FOR XML PATH的查询结果语句如下:
SELECT * FROM #Test FOR XML PATH
在查询结果中会出现一个可点击的XML链接,点击后结果如下:
<row>
<Name>李四</Name>
<Hobby>唱歌</Hobby>
</row>
<row>
<Name>李四</Name>
<Hobby>睡觉</Hobby>
</row>
<row>
<Name>张三</Name>
<Hobby>爬山</Hobby>
</row>
<row>
<Name>张三</Name>
<Hobby>游戏</Hobby>
</row>
<row>
<Name>张三</Name>
<Hobby>游泳</Hobby>
</row>
可见FOR XML PATH 可以将查询后结果根据行输出成XML格式。那么变为XML格式有什么好处呢?我们一般在使用select 列 from 表
语句时查询出来的结果都是按照列输出的,如果要得到列中的一个数据就需要加各种筛选条件。而XML格式可以通过<>中的节点来筛选出数据,从而由select语句的列级别操作变为类似于Excel基于行列定位的单元格级别操作
FOR XML PATH应用
1.分组拼接
如果要显示所有人的爱好的结果集,并且要每个人的所有兴趣显示在一行中,代码如下:
SELECT
Name,
STUFF((SELECT ','+Hobby FROM #Test WHERE Name=T.Name FOR XML PATH('')),1,1,'') AS All_Hobby
FROM #Test T GROUP BY T.Name
结果如下:
对上述语句逐步分析一下,首先看这句:
SELECT ','+Hobby FROM #Test WHERE Name=T.Name FOR XML PATH('')
这句是通过FOR XML PATH 将某一姓名如张三的爱好,显示成格式为:“ ,爱好1,爱好2,爱好3,…”的格式,WHERE Name=T.Name这一个条件用来判断筛选对应姓名的爱好,如果没有WHERE Name=T.Name这一个条件的话就会连接整个表中所有的爱好,无法分组
接着用STUFF函数将拼接完成后字符第一个’,'替换成空,STUFF函数第一个参数为要操作的字符串,第二个为从第几个开始替换下标从1开始,第三个参数为替换的个数,第四个参数为替换的字符)
简单举几个例子:
select STUFF('abc',1,1,'*') 执行结果为:*abc
select STUFF('abc',1,0,'*') 执行结果为:*bc
select STUFF('abc',2,2,'*') 执行结果为:a**
最终输出上述按照姓名分组后每个人对应的爱好
在MySQL 中可以用group_concat这个函数来实现这一功能
多行数据拼接
FOR XML PATH可以实现对一列中多行数据进行拼接,可以使用行节点与列节点并自定义输出格式:
-- 还可以通过符号+号来对字符串类型字段的输出格式进行修改
SELECT '[ '+Hobby+' ]' FROM #Test FOR XML PATH('')
--输出结果为: [ 唱歌 ][ 睡觉 ][ 爬山 ][ 游戏 ][ 游泳 ]
--如有其他数据类型的列转换成字符串类型再拼接
SELECT '{'+STR(ID)+'}','[ '+Hobby+' ]' FROM #Test FOR XML PATH('')
JSON格式输出
STUFF结合FOR XML PATH可以用来拼接JSON字符串,
select '['+ stuff((select ',{"name": "' + name + '","subjects": "' + Hobby + '"}'
from #Test for xml path('')),1,1,'') +']'
--[{"name": "李四","subjects": "唱歌"},{"name": "李四","subjects": "睡觉"},{"name": "张三","subjects": "爬山"},{"name": "张三","subjects": "游戏"},{"name": "张三","subjects": "游泳"}]
节点名称的修改
再来看一下如何改变XML行节点名称,如果我们想修改对应数据的节点名称,代码如下:
SELECT * FROM #Test FOR XML PATH('MyHobby')
运行后输出结果把原来的行节点 变成了在PATH后面括号()中自定义的名称,结果如下:
<MyHobby>
<Name>李四</Name>
<Hobby>唱歌</Hobby>
</MyHobby>
<MyHobby>
<Name>李四</Name>
<Hobby>睡觉</Hobby>
</MyHobby>
<MyHobby>
<Name>张三</Name>
<Hobby>爬山</Hobby>
</MyHobby>
<MyHobby>
<Name>张三</Name>
<Hobby>游戏</Hobby>
</MyHobby>
<MyHobby>
<Name>张三</Name>
<Hobby>游泳</Hobby>
那么列节点如何改变呢?可以使用关键字AS修改列节点名称,代码如下:
SELECT Name as 'MyName',Hobby as 'MyHobby' FROM #Test FOR XML PATH('MyHobby')
结果如下:文章来源:https://www.toymoban.com/news/detail-819275.html
<MyHobby>
<MyName>李四</MyName>
<MyHobby>唱歌</MyHobby>
</MyHobby>
<MyHobby>
<MyName>李四</MyName>
<MyHobby>睡觉</MyHobby>
</MyHobby>
<MyHobby>
<MyName>张三</MyName>
<MyHobby>爬山</MyHobby>
</MyHobby>
<MyHobby>
<MyName>张三</MyName>
<MyHobby>游戏</MyHobby>
</MyHobby>
<MyHobby>
<MyName>张三</MyName>
<MyHobby>游泳</MyHobby>
</MyHobby>
参考文章:
https://www.cnblogs.com/yasuo2/p/6433697.html
文章来源地址https://www.toymoban.com/news/detail-819275.html
到了这里,关于FOR XML PATH 函数与同一分组下的字符串拼接的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!