前言：在工作中遇到了FOR XML PATH其实它就是将查询结果集以XML形式展现，有了它我们可以简化我们的查询语句，实现一些以前可能需要借助函数或存储过程来完成的工作

一、FOR XML PATH函数用法

1. FOR XML PATH 简单介绍

1.1 首先来介绍一下FOR XML PATH ，先建一张科目表来存放科目

1 CREATE TABLE SubjectInfo
2 (
3     Id INT IDENTITY(1,1) PRIMARY KEY,
4     Name NVARCHAR(50) NULL
5 )
6 
7 INSERT INTO SubjectInfo 
8 VALUES('语文'),('数学'),('英语')

1.2 接下来我们来看应用FOR XML PATH的查询结果（由此可见FOR XML PATH 可以将查询结果根据行输出成XML各式）

SELECT * FROM SubjectInfo FOR XML PATH

1.3 如何改变XML行节点的名称呢，代码如下：

SELECT * FROM SubjectInfo FOR XML PATH('MySubject')

1.4 那么列节点如何改变呢，还记的给列起别名的关键字AS吗？对了就是用它，代码如下：

SELECT Id AS MyCode,Name AS MyName FROM SubjectInfo FOR XML PATH('MySubject')

1.5 既然行的节点与列的节点我们都可以自定义，那么肯定是可以构建我们喜欢的输出方式，代码如下：

SELECT '{'+CAST(Id AS NVARCHAR(2))+'}','['+Name+']' FROM SubjectInfo FOR XML PATH('')

{1}[语文]{2}[数学]{3}[英语]

1.6 FOR XML PATH就基本介绍到这里吧，所有代码如下，更多关于FOR XML的知识请查阅帮助文档（https://docs.microsoft.com/zh-cn/sql/relational-databases/xml/xml-data-sql-server?view=sql-server-ver15）

 1 --1.1 建一张科目表来存放科目
 2 CREATE TABLE SubjectInfo
 3 (
 4     Id INT IDENTITY(1,1) PRIMARY KEY,
 5     Name NVARCHAR(50) NULL
 6 )
 7 
 8 INSERT INTO SubjectInfo 
 9 VALUES('语文'),('数学'),('英语')
10 
11 --1.2 应用FOR XML PATH的查询结果
12 SELECT * FROM SubjectInfo FOR XML PATH
13 
14 --1.3 改变XML行节点的名称
15 SELECT * FROM SubjectInfo FOR XML PATH('MySubject')
16 
17 --1.4 改变XML列节点的名称
18 SELECT Id AS MyCode,Name AS MyName FROM SubjectInfo FOR XML PATH('MySubject')
19 
20 --1.5 构建喜欢的输出方式
21 SELECT '{'+CAST(Id AS NVARCHAR(2))+'}','['+Name+']' FROM SubjectInfo FOR XML PATH('')

2. FOR XML PATH的应用场景

2.1 先建一张学生表来存放学生和科目信息，表结构和数据如下

 1 CREATE TABLE StudentInfo
 2 (
 3     Id INT IDENTITY(1,1) PRIMARY KEY,
 4     Name NVARCHAR(50) NULL,
 5     Subject NVARCHAR(50) NULL
 6 )
 7 
 8 INSERT INTO StudentInfo 
 9 VALUES('张三','地理'),('张三','生物'),('张三','政治'),
10 ('李四','语文'),('李四','数学'),('李四','英语'),
11 ('王五','历史'),('王五','音乐'),('王五','体育')

2.2 这时，我们的要求是查询学生表，显示所有学生的科目结果集，代码如下：

SELECT B.Name,LEFT(B.SubjectList,LEN(B.SubjectList)-1) AS Subjects FROM (SELECT A.Name,(SELECT Subject+',' FROM StudentInfo WHERE Name = A.Name FOR XML PATH('')) AS SubjectList FROM dbo.StudentInfo A GROUP BY A.Name) B

2.3 通过Stuff函数来优化上面的结果集（删除多余的分割符，没必要通过这种嵌套函数子查询来实现LEFT(B.SubjectList,LEN(B.SubjectList)-1)）

SELECT A.Name,STUFF((SELECT ','+Subject FROM StudentInfo WHERE Name = A.Name FOR XML PATH('')),1,1,'') AS SubjectList FROM dbo.StudentInfo A GROUP BY A.Name

2.4 所有代码如下

 1 --2.1 建一张学生表
 2 CREATE TABLE StudentInfo
 3 (
 4     Id INT IDENTITY(1,1) PRIMARY KEY,
 5     Name NVARCHAR(50) NULL,
 6     Subject NVARCHAR(50) NULL
 7 )
 8 
 9 INSERT INTO StudentInfo 
10 VALUES('张三','地理'),('张三','生物'),('张三','政治'),
11 ('李四','语文'),('李四','数学'),('李四','英语'),
12 ('王五','历史'),('王五','音乐'),('王五','体育')
13 
14 --2.2 显示所有学生的科目的结果集
15 SELECT B.Name,LEFT(B.SubjectList,LEN(B.SubjectList)-1) AS Subjects FROM (SELECT A.Name,(SELECT Subject+',' FROM StudentInfo WHERE Name = A.Name FOR XML PATH('')) AS SubjectList FROM dbo.StudentInfo A GROUP BY A.Name) B
16 
17 -- 2.3 通过Stuff函数来优化上面的结果集
18 SELECT A.Name,STUFF((SELECT ','+Subject FROM StudentInfo WHERE Name = A.Name FOR XML PATH('')),1,1,'') AS SubjectList FROM dbo.StudentInfo A GROUP BY A.Name

posted on 2021-07-30 17:18 JamelAr 阅读(2444) 评论(0) 编辑收藏举报