SQL Server : FOR XML PATH 函数用法以及转义字符正常显示
简介For XML PATH
For XML,就是将查询结果作为XML来检索。
XML有4中模式，官方文档上有这4种模式的使用方法。
| 模式 |
描述 |
| RAW | SELECT 语句返回的行集中为每个行生成一 <个行> 元素 |
| AUTO | 将基于指定 SELECT 语句的方式来使用试探性方法在 XML 结果中生成嵌套。（说的是什么？是能够生成嵌套的XML吗？） |
| EXPICIT | 模式允许对 XML 的形状进行更多控制。 |
| PATH | 任务比较简单的方式。 |
仅当执行设置了这些模式的查询时，这些模式才有效。 它们不会影响以后执行的任何查询的结果。
接下来我们只讲Path模式（事实上，我在网上看到最多的也是PATH模式的例子）。
很多问题等遇上了，再说，先说说FOR XML PATH的基本用法。
假设有一张兴趣爱好表（t_bd_hobby）,表内容如下：
接下来，我们执行FOR XML语句：
SELECT * FROM dbo.t_bd_hobby FOR XML PATH
返回结果如下
1 <row> 2 <ID>1</ID> 3 <hobby>爬山</hobby> 4 </row> 5 <row> 6 <ID>2</ID> 7 <hobby>游泳</hobby> 8 </row> 9 <row> 10 <ID>3</ID> 11 <hobby>美食</hobby> 12 </row>
因此，FOR XML PATH可以将查询结果依据行输出成XML格式。
自定义节点名称
如何改变节点的名称呢？
SELECT * FROM dbo.t_bd_hobby FOR XML PATH('Myhobby')
输出的XML如下：
1 <Myhobby> 2 <ID>1</ID> 3 <hobby>爬山</hobby> 4 </Myhobby> 5 <Myhobby> 6 <ID>2</ID> 7 <hobby>游泳</hobby> 8 </Myhobby> 9 <Myhobby> 10 <ID>3</ID> 11 <hobby>美食</hobby> 12 </Myhobby>
自定义列名称
当然，我们也可以自定义列名称。
SELECT ID AS 编码, hobby AS 兴趣 FROM dbo.t_bd_hobby FOR XML PATH
输出的XML如下：
1 <row> 2 <编码>1</编码> 3 <兴趣>爬山</兴趣> 4 </row> 5 <row> 6 <编码>2</编码> 7 <兴趣>游泳</兴趣> 8 </row> 9 <row> 10 <编码>3</编码> 11 <兴趣>美食</兴趣> 12 </row>
For XML Path的应用场景——多行转一行
既然节点和列都可以被自定义，那么我们是不是可以构建我们喜欢的输出方式呢？
SELECT '[ '+hobby+' ]' FROM t_bd_hobby FOR XML PATH('')
输出如下:
[ 游泳 ][ 爬山 ][ 美食 ]
可以改成用逗号分割
SELECT ',' + hobby FROM t_bd_hobby FOR XML PATH('')
这样，多行合并为一行，每个字段用逗号隔开。
,游泳,爬山,美食,
有没有一个函数截取字符串，把前面一个逗号去掉?这样就可以转换为我们常见的形式。stuff就是这么用的。
SELECT stuff( ( SELECT '，' + hobby FROM t_bd_hobby FOR XML PATH('')) ,1,1 ,'')
输出结果如下：
游泳,爬山,美食
For XML Path的应用场景——分组的多行转一行
既然我们能够将多行合并为一行，那么我们能不能完成如下功能。首先有一张学生表，数据如下：
我们要以名字为列，将爱好按逗号隔开，如下所示：
该如何做呢？执行语句的总体思想如下：
1. 按照fname分类。所以使用grou by语句。
2、使用上文已知的字符串截取方法，将fhobby视为被分组的聚合函数，这里先用1表示。
SELECT fname , '1' AS hobby FROM tb_personhobbys GROUP BY fname
显示结果如下：
3.没问题后，我们将'1'替换为for xml path的多行转一行的形式。
1 SELECT fname , (STUFF(( SELECT + ',' + fhobby FROM tb_personhobbys FOR XML PATH('')) ,1,1, '' )) AS hobby 2 FROM tb_personhobbys 3 GROUP BY fname
这样的结果，将所有的hobby都传达进来了。
显然不是我们想要的结果，能将hobby局行列传递条件吗？当然可以。
3. 聚合列内传递fname条件。这里，为了区分，需要给外面的表（tb_personhobbys）命名。
1 SELECT fname , (STUFF(( SELECT + ',' + fhobby FROM tb_personhobbys WHERE fname = a.fname FOR XML PATH('')) ,1,1, '' )) AS hobby 2 FROM tb_personhobbys a 3 GROUP BY fname
这样，我们就完成了分组的多行转一行了。
