一.FOR XML PATH 基本介绍
语句SELECT * FROM MEMBER查询出来结果如上图所示,现加入FOR XML PATH看下会有什么变化,语句如下:
SELECT * FROM MEMBER FOR XML PATH
点击进入可查询详细情况
由此可见,FOR XML PATH 是可以将查询结果以XML格式输出。
那么,我们可以自定义节点吗?先试试
SELECT * FROM MEMBER FOR XML PATH ('m')
结果显示行节点变成了<m>,既然可以修改,那当然path()内的值可以修改成其他值。
如果是这样path('')则表示去掉行节点。
那列节点呢?回顾下SELECT *或 SELECT 列名 ,那也就是说可以从这里改变它
SELECT 编号 BH , 姓名 XM, 性别 sex,年龄 age ,爱好 AH
FROM MEMBER FOR XML PATH ('m')
但上面这样输出的形式还不是我们想要看到的,怎么能把一些符号可以去掉呢?
让我们自定义输出,语句如下:
SELECT '[' 爱好 ']' FROM MEMBER FOR XML PATH('')
输出结果:
因为path('')值为空,所以结果只会显示一行一个值记录
如果是int数据类型,如年龄,需要转换成字符串
SELECT '[' cast(年龄 as nvarchar) ']', '[' 爱好 ']'
FROM MEMBER FOR XML PATH ('')
FOR XML PATH基本使用就介绍到这里,下面看下如何运用
二、结合GROUP BY 实际应用,要求按下图方式显示
分析下看如何运用,除了与GROUP BY 结合使用外还用到STUFF函数,当然也可以用LEFT等函数,我这里只简单介绍STUFF函数的使用
STUFF函数作用:删除指定长度的字符,或在指定的起点处插入另一组字符。
STUFF ( character_expression , start , length , replaceWith_expression )
character_expression表示:字符数据的表达式
start表示:一个整数值,指定删除和插入的开始位置
length表示:一个整数值,指定要删除的字符数
replaceWith_expression表示:字符数据的表达式
先看以下语句:
select a.编号,a.姓名,爱好=(select ',' 爱好 from member where 编号=a.编号 for xml path(''))
from member a
group by a.编号,a.姓名
温馨提示:上述语句嵌套相关子查询将FOR XML PATH结果数据赋值给父查询“爱好”列
输出结果:
注意:必须要加上条件where 编号=a.编号,要不然会以下结果输出
现在要想办法去掉“爱好”列的第一个逗号,此时用到STUFF函数
select a.编号,a.姓名,爱好=STUFF((select ',' 爱好 from member where 编号=a.编号 for xml path('')),1,1,'')
from member a
GROUP BY a.编号,a.姓名
输出结果:
STUFF((select ',' 爱好 from member where 编号=a.编号 for xml path('')),1,1,'')
注释:STUFF函数将子查询“爱好”的字符串内容,从第1个字符开始截取,取长度是1,删除它,然后把结果赋值给父查询的列。
联系客服