FOR XML 各种用法

最近在项目中使用到了 FOR XML 的语法。查了联机帮助将理解的内容记录了下来。希望对日后的工作有所帮助。同时也是加深对 FOR XML 的理解。

 

---SQL中FOR XML子句的各种用法
1、AUTO模式：返回数据表为起表名的元素，每一列的值返回为属性；
SELECT *  FROM tb_test for xml auto
2、RAW模式：返回数据行为元素，每一列的值作为元素的属性；
SELECT *  FROM tb_test for xml raw('fsf')
3、PATH模式：通过简单的XPath语法来允许用户自定义嵌套的XML结构、元素、属性值
SELECT *  FROM tb_test for xml path('fsf')
4、EXPLICIT模式：通过SELECT语法定义输出XML的结构

 

----最常用的就是合并字符串 的案例
-----------------以某列为基准，将其他列的内容合并成指定格式（如：q,w,e,r,t)放到一列中
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[tb_test]')
AND type in (N'U'))

CREATE TABLE tb_test(id int, value varchar(10))
 INSERT tb_test SELECT 1, 'aa'
     UNION ALL SELECT 1, 'bb'
     UNION ALL SELECT 2, 'aaa'
     UNION ALL SELECT 2, 'bbb'
     UNION ALL SELECT 2, 'ccc'
     UNION ALL SELECT 3, 'ddd'
     UNION ALL SELECT 3, 'ddd'

--方法1
SELECT *
FROM (SELECT DISTINCT id FROM tb_test) A OUTER APPLY(
SELECT value= STUFF(REPLACE(REPLACE(
(
SELECT value FROM tb_test aa
WHERE id = A.id
FOR XML AUTO
), '<N value="', ','), '"/>', ''), 1, 1, '')
)aa
----方法2
SELECT id, value=STUFF((SELECT ','+value FROM tb_test t WHERE id=tb_test.id FOR XML PATH('')), 1, 1, '')
FROM tb_test
GROUP BY id
-----------------以某列为基准，将其他列的内容合并成指定格式（如：q,w,e,r,t)放到一列中