FOR XML PATH 的使用

FOR XML PATH：将查询出来的结果集以 XML 的形式显示，使用 FOR XML PATH 可以简化我们的查询语句实现一些以前可能需要借助函数或存储过程来完成的工作。


创建测试表
IF NOT EXISTS (SELECT NAME FROM SYSOBJECTS WHERE NAME ='tbl_XMLPath' AND OBJECTPROPERTY(ID, 'ISTABLE') = 1)
BEGIN
  CREATE TABLE tbl_XMLPath(
    IID INT IDENTITY(1, 1) NOT NULL,
    cName VARCHAR(20), 
    cMemo VARCHAR(200)
  )
END;
GO


插入测试数据
INSERT INTO tbl_XMLPath(cName, cMemo)
SELECT '张三', 'AAA' 
UNION SELECT '李四', 'BBB' 
UNION SELECT '王五', 'CCC' 
UNION SELECT '赵六', 'DDD'
GO


查询1：简单的使用 FOR XML PATH
SELECT * FROM tbl_XMLPath FOR XML PATH 

/*
<row>
  <IID>1</IID>
  <cName>李四</cName>
  <cMemo>BBB</cMemo>
</row>
<row>
  <IID>2</IID>
  <cName>王五</cName>
  <cMemo>CCC</cMemo>
</row>
<row>
  <IID>3</IID>
  <cName>张三</cName>
  <cMemo>AAA</cMemo>
</row>
<row>
  <IID>4</IID>
  <cName>赵六</cName>
  <cMemo>DDD</cMemo>
</row>
*/

以下结果是以元素为中心的 XML，在该 XML 中，生成的行集中的每个列值都包在元素中。由于 SELECT 子句未指定任何列名别名，因此生成的子元素名称与 SELECT 子句中相应的列名相同。针对行集中的每一行，将添加一个 <row> 标记，<row>为默认标志。


查询2：下面的查询语句与上面查询1的结果是一样的
SELECT * FROM tbl_XMLPath FOR XML RAW, ELEMENTS;

查询3：可以选择指定行元素名称，以覆盖默认的 <row>。例如，以下查询将针对行集中的每一行返回相应的 <Person> 元素。
SELECT * FROM tbl_XMLPath FOR XML PATH('Person')

/*
<Person>
  <IID>1</IID>
  <cName>李四</cName>
  <cMemo>BBB</cMemo>
</Person>
<Person>
  <IID>2</IID>
  <cName>王五</cName>
  <cMemo>CCC</cMemo>
</Person>
<Person>
  <IID>3</IID>
  <cName>张三</cName>
  <cMemo>AAA</cMemo>
</Person>
<Person>
  <IID>4</IID>
  <cName>赵六</cName>
  <cMemo>DDD</cMemo>
</Person>
*/

查询4：如果PATH指定的是空值，将不生成行元素进行数据包装
SELECT * FROM tbl_XMLPath FOR XML PATH('')

/*
<IID>1</IID>
<cName>李四</cName>
<cMemo>BBB</cMemo>
<IID>2</IID>
<cName>王五</cName>
<cMemo>CCC</cMemo>
<IID>3</IID>
<cName>张三</cName>
<cMemo>AAA</cMemo>
<IID>4</IID>
<cName>赵六</cName>
<cMemo>DDD</cMemo>
*/

指定 XML 的节点名称，只需要在对应表列字段上进行 AS 重命名即可。若列名以 '@' 开头，且不包含斜杠标记（'/'），产生的结果是改列的值将作为该行节点的属性值
查询5：演示节点重命名
SELECT cName AS '@NAME', cMemo AS 'Descriptor' FROM tbl_XMLPath FOR XML PATH('Person')

/*
<Person NAME="李四">
  <Descriptor>BBB</Descriptor>
</Person>
<Person NAME="王五">
  <Descriptor>CCC</Descriptor>
</Person>
<Person NAME="张三">
  <Descriptor>AAA</Descriptor>
</Person>
<Person NAME="赵六">
  <Descriptor>DDD</Descriptor>
</Person>
*/

查询6：为 XML 添加一个顶级的节点名称
SELECT cName AS '@NAME', cMemo AS 'Descriptor' FROM tbl_XMLPath FOR XML PATH('Person'), ROOT('ROOT')

/*
<ROOT>
  <Person NAME="李四">
    <Descriptor>BBB</Descriptor>
  </Person>
  <Person NAME="王五">
    <Descriptor>CCC</Descriptor>
  </Person>
  <Person NAME="张三">
    <Descriptor>AAA</Descriptor>
  </Person>
  <Person NAME="赵六">
    <Descriptor>DDD</Descriptor>
  </Person>
</ROOT>
*/

查询7：将 cMemo 列的列名改为“Child/Descriptor”，就会获得具有层次结构的
SELECT cName AS '@NAME', cMemo AS 'Child/Descriptor' FROM tbl_XMLPath FOR XML PATH('Person')

/*
<Person NAME="李四">
  <Child>
    <Descriptor>BBB</Descriptor>
  </Child>
</Person>
<Person NAME="王五">
  <Child>
    <Descriptor>CCC</Descriptor>
  </Child>
</Person>
<Person NAME="张三">
  <Child>
    <Descriptor>AAA</Descriptor>
  </Child>
</Person>
<Person NAME="赵六">
  <Child>
    <Descriptor>DDD</Descriptor>
  </Child>
</Person>
*/

以上是 FOR XML PATH 的简单使用，以下结合示例进行演示


示例1：将表中 cName 的所有记录存储到一个变量中
DECLARE @Str VARCHAR(200)
SET @Str = ''
SET @Str = @Str + STUFF((SELECT ',' + cName FROM tbl_XMLPath FOR XML PATH('')), 1, 1, '')
PRINT @Str

/*
李四,王五,张三,赵六
*/

--示例2：将多条记录的值合并到一条记录
INSERT INTO tbl_XMLPath
SELECT '张三', '123' 
UNION SELECT '李四', '456' 
UNION SELECT '王五', '789' 
UNION SELECT '赵六', '012'
UNION SELECT '张三', 'QWE' 
UNION SELECT '李四', 'ASD' 
UNION SELECT '王五', 'ZXC' 
UNION SELECT '赵六', 'HJK'
GO


SELECT cName,
       STUFF((SELECT ',' + cMemo FROM tbl_XMLPath WHERE cName = A.cName FOR XML PATH('')), 1, 1, '') AS cMemos
FROM tbl_XMLPath A GROUP BY cName

/*
cName                cMemos
-------------------- -----------------------
李四                   BBB,456,ASD
王五                   CCC,789,ZXC
张三                   AAA,123,QWE
赵六                   DDD,012,HJK
(4 行受影响)
*/

--删除测试表
DROP TABLE tbl_XMLPath