FOR XML PATH 应用，解析XML数据

SQL Server 中 XML 数据操作

[TOC]

## 1. FOR XML PATH 应用

### 1.1. 数据源

准备好的表及数据（Customer表）

![](/UploadFiles/images/20201217/6374382061643382478761918.png)

### 1.2. XML 根节点命名

#### 1.2.1. 默认 row 根节点

PATH函数不传任何参数则默认 row 为每行数据的根节点

**示例代码：**

```SQL
SELECT * FROM dbo.Customer WHERE Id IN (1,2) FOR XML PATH
```

**结果：**将查询到表中所有行以及字段拼接成一个xml结构的数据，每行是一个节点 `row`，字段是xml节点下的元素

```XML
<row>
  <Id>1</Id>
  <Name>刘备</Name>
  <Age>59</Age>
  <Sex>1</Sex>
  <CreateTime>2020-12-17T14:22:33</CreateTime>
  <Intention>蜀汉</Intention>
</row>
<row>
  <Id>2</Id>
  <Name>关羽</Name>
  <Age>52</Age>
  <Sex>1</Sex>
  <CreateTime>2020-12-17T14:22:33</CreateTime>
  <Intention>荆州</Intention>
</row>
```

#### 1.2.2. 无跟节点

PATH()函数传 `''` 空字符时，将无根节点

**示例代码：**

```SQL
SELECT * FROM dbo.Customer FOR XML PATH('')
```

**结果：**将查询到表中所有行以及字段拼接成一个xml结构的数据，这里与上面的差异在于没有节点 `row`，是直接将字段连续拼接成xml的,个人感觉查询单条记录使用为好

```XML
<Id>1</Id>
<Name>刘备</Name>
<Age>59</Age>
<Sex>1</Sex>
<CreateTime>2020-12-17T14:22:33</CreateTime>
<Intention>蜀汉</Intention>
<Id>2</Id>
<Name>关羽</Name>
<Age>52</Age>
<Sex>1</Sex>
<CreateTime>2020-12-17T14:22:33</CreateTime>
<Intention>荆州</Intention>
```

#### 1.2.3. 自定义根节点

PATH()函数传传入指定字符串时，传入的字符串则为根节点的名称

**示例代码：**

```SQL
SELECT * FROM dbo.Customer FOR XML PATH ('Customer')
```

**结果：**从结果可以看出，这里执行的结果用Customer代替了默认的row节点名称

```XML
<Customer>
  <Id>1</Id>
  <Name>刘备</Name>
  <Age>59</Age>
  <Sex>1</Sex>
  <CreateTime>2020-12-17T14:22:33</CreateTime>
  <Intention>蜀汉</Intention>
</Customer>
<Customer>
  <Id>2</Id>
  <Name>关羽</Name>
  <Age>52</Age>
  <Sex>1</Sex>
  <CreateTime>2020-12-17T14:22:33</CreateTime>
  <Intention>荆州</Intention>
</Customer>
```

### 1.3. 自定义子节点

所谓的自定义子节点名称，其实就是利用了 SQL 中的`字段别名`

**示例代码：**

```SQL
SELECT Id AS CusId ,Name CusName FROM dbo.Customer FOR XML PATH ('Customer')
```

**结果：**此语句与上面的对比就是我们将xml元素名称通过as的方式修改成了我们自定义别名的元素名称了

```XML
<Customer>
  <CusId>1</CusId>
  <CusName>刘备</CusName>
</Customer>
<Customer>
  <CusId>2</CusId>
  <CusName>关羽</CusName>
</Customer>
```

### 1.4. 无XML节点

使用函数来实现无 XML 结构的字符串拼接效果，最后结果将会是一串字符串，没有XML节点，可以自己定义字符来区分行数据

**示例代码：**

```SQL
SELECT CAST(Id AS VARCHAR(50)) + ',' FROM dbo.Customer FOR XML PATH ('')
-- 结果:1,2,3,4,5,6,7,8,
SELECT '{' + CAST(Id AS VARCHAR(50)) + '},[' + Name + '],' FROM  dbo.Customer FOR XML PATH ('')
-- 结果:{1},[刘备],{2},[关羽],{3},[赵云],{4},[张飞],{5},[糜竺],{6},[孙尚香],{7},[甘夫人],{8},[刘禅],
```

### 1.7. 通配符拼接的结果可以使用节点来区分每行的结果

**示例代码：**

```SQL
SELECT '{' + CAST(Id AS VARCHAR(50)) + '},[' + Name + ']' FROM  dbo.Customer FOR XML PATH ('Node')
```

**结果：**从输出中我们可以看到我们用node节点来分割出每行的数据了

```XML
<Node>{1},[刘备]</Node>
<Node>{2},[关羽]</Node>
<Node>{3},[赵云]</Node>
<Node>{4},[张飞]</Node>
<Node>{5},[糜竺]</Node>
<Node>{6},[孙尚香]</Node>
<Node>{7},[甘夫人]</Node>
<Node>{8},[刘禅]</Node>
```

### 1.8. 使用别名修改输出字段

从上面的结果我们可以看出输出的列表的名称都是很长的一段编码，我们也可以通过别名的方式来决定我们列的名称

**示例代码：**

```SQL
SELECT(SELECT CAST(Id AS VARCHAR(50)) + ',' FROM dbo.Customer FOR XML PATH ('')) AS Node
-- 结果:1,2,3,4,5,6,7,8,
```

**结果：**我们用 Node 代替了 `XML_F52E2B61-18A1-11d1-B105-00805F49916B`

![](/UploadFiles/images/20201217/6374382256774094794627959.png)

> Tips：
总结：我们使用for xml path可以将我们查询到的结果汇总成一个字段，多行多字段以一个字段输出结果，我们可用通过select来控制字段的组成和xml的元素名称

### 1.9. 综合示例

下面以一个列子来应用一下for xml path，再准备两张表

**User人员表**

![](/UploadFiles/images/20201217/6374382387650181053432645.png)

**UserCustomerRelation人员客户关联表**

![](/UploadFiles/images/20201217/6374382391048641856793940.png)

**示例代码：**

```SQL
SELECT U.Name,
       U.Alias,
       U.Position,
       STUFF((
           SELECT ',' + C.Name
           FROM dbo.Customer AS C
           WHERE C.Id IN (
                             SELECT CustomerId FROM dbo.UserCustomerRelation WHERE UserId = U.Id
                         )
           FOR XML PATH('')
       ),1,1,'') AS CustomerName
FROM dbo.[User] AS U
```

**结果：**

![](/UploadFiles/images/20201217/6374382399817252299670962.png)

这里用到了一个STUFF函数，这个函数是用来截取字符串的长度的，使用方法可参见[【SQL Server截取字符串的几种方法】](http://www.cylyccjy.com/blog/info.html/554C94CC2AA06103?type=40)

## 2. 解析XML数据

在 SQL SERVER 中解析 XML 数据需要使用到 XPath 相关的语法和知识点

> Tips：学习 Xpath 点击：https://www.runoob.com/xpath/xpath-tutorial.html

### 2.1. 数据来源【User】表

![](http://www.cylyccjy.com/UploadFiles/images/20201217/6374382387650181053432645.png)

#### 2.1.1. 准备测试数据

准备要解析的XML数据，使用 FOR XML PATH 将查询到的数据转成 XML 格式

```SQL
DECLARE @UserInfo XML
SET @UserInfo = (SELECT   TOP 3
                          Id, [Name], Sex, CONVERT(VARCHAR(20), Birthday, 120) Birthday
                 FROM     dbo.[User]
                 ORDER BY Id DESC
                FOR XML PATH)
SELECT @UserInfo
```

上述代码先将表数据存放到数据类型为XML的@UserInfo变量中结果是一个XML结构数据

**结果：**

```XML
<row>
  <Id>8</Id>
  <Name>荀彧</Name>
  <Sex>1</Sex>
  <Birthday>2020-12-17 14:22:33</Birthday>
</row>
<row>
  <Id>7</Id>
  <Name>郭女王</Name>
  <Sex>1</Sex>
  <Birthday>2020-12-17 14:22:33</Birthday>
</row>
<row>
  <Id>6</Id>
  <Name>甄宓</Name>
  <Sex>0</Sex>
  <Birthday>2020-12-17 14:22:33</Birthday>
</row>
```

### 2.2. 将XML数据解析成表

**示例代码：**

使用SQL解析XML数据结构获取节点下的值

```SQL
SELECT c.value('(Id/text())[1]', 'INT') AS Id,
       c.value('(Name/text())[1]', 'NVARCHAR(50)') AS [Name],
       c.value('(Sex/text())[1]', 'INT') AS Sex,
       c.value('(Birthday/text())[1]', 'DATETIME') AS Birthday
FROM   @UserInfo.nodes('/row') AS T(c)

-- 上面查询方法的简写
SELECT c.value('Id[1]', 'INT') AS Id,
       c.value('Name[1]', 'NVARCHAR(50)') AS [Name],
       c.value('Sex[1]', 'INT') AS Sex,
       c.value('Birthday[1]', 'DATETIME') AS Birthday
FROM   @UserInfo.nodes('row') AS T(c)
```

**解析结果：**

上述两种查询的记过是一样的,推荐使用简写的查询方式，阅读更加的直观

| Id| Name| Sex| Birthday|
|--|--|--|--|
|8|荀彧|1|2020-12-17 14:22:33.000|
|7|郭女王|1|2020-12-17 14:22:33.000|
|6|甄宓|0|2020-12-17 14:22:33.000|

1、下面为多种方法从XML中读取EMAIL

```SQL
DECLARE @x XMLSELECT @x = '<People>
  <dongsheng>
    <Info Name="Email">dongsheng@xxyy.com</Info>
    <Info Name="Phone">678945546</Info>
    <Info Name="qq">36575</Info>
  </dongsheng>
</People>'
-- 方法1
SELECT @x.value('data(/People/dongsheng/Info[@Name="Email"])[1]', 'varchar(30)')
-- 方法2
SELECT @x.value('(/People/dongsheng/Info[@Name="Email"])[1]', 'varchar(30)')
-- 方法3
SELECT C.value('.','varchar(30)') FROM @x.nodes('/People/dongsheng/Info[@Name="Email"]') T(C)
-- 方法4
SELECT C.value('(Info[@Name="Email"])[1]','varchar(30)') FROM @x.nodes('/People/dongsheng') T(C)
-- 方法5
SELECT C.value('(dongsheng/Info[@Name="Email"])[1]','varchar(30)') FROM @x.nodes('/People') T(C)
-- 方法6
SELECT C.value('.','varchar(30)') FROM @x.nodes('/People/dongsheng/Info') T(C) 
WHERE C.value('(.[@Name="Email"])[1]','varchar(30)') IS NOT NULL
-- 方法7
SELECT C.value('.','varchar(30)') FROM @x.nodes('/People/dongsheng/Info') T(C)WHERE C.exist('(.[@Name="Email"])[1]') = 1
```

2、读取XML中的属性值

```SQL
DECLARE @x XML
SELECT @x =
'<Peoples>
  <People Name="tudou" Sex="女" />
  <People Name="choushuigou" Sex="女"/>
  <People Name="dongsheng" Sex="男" />
</Peoples>'
SELECT v.value('@Name[1]','VARCHAR(20)') AS Name, v.value('@Sex[1]','VARCHAR(20)') AS Sex
FROM @x.nodes('/Peoples/People') x(v)
```

3、属性过滤

```SQL
DECLARE @x XML
SELECT @x = '
<Employees>
 <Employee id="1234" dept="IT" type="合同工">
  <Info NAME="dongsheng" SEX="男" QQ="5454545454"/>
 </Employee>
 <Employee id="5656" dept="IT" type="临时工">
  <Info NAME="土豆" SEX="女" QQ="5345454554"/>
 </Employee>
 <Employee id="3242" dept="市场" type="合同工">
  <Info NAME="choushuigou" SEX="女" QQ="54543545"/>
 </Employee>
</Employees>'
--查询dept为IT的人员信息
  --方法1
  SELECT
    C.value('@NAME[1]','VARCHAR(10)') AS NAME,
    C.value('@SEX[1]','VARCHAR(10)') AS SEX,
    C.value('@QQ[1]','VARCHAR(20)') AS QQ
  FROM @x.nodes('/Employees/Employee[@dept="IT"]/Info') T(C)
  --方法2
  SELECT
    C.value('@NAME[1]','VARCHAR(10)') AS NAME,
    C.value('@SEX[1]','VARCHAR(10)') AS SEX,
    C.value('@QQ[1]','VARCHAR(20)') AS QQ
  FROM @x.nodes('//Employee[@dept="IT"]/*') T(C)
--查询出IT部门type为Permanent的员工
SELECT
  C.value('@NAME[1]','VARCHAR(10)') AS NAME,
  C.value('@SEX[1]','VARCHAR(10)') AS SEX,
  C.value('@QQ[1]','VARCHAR(20)') AS QQ
FROM @x.nodes('//Employee[@dept="IT"][@type="合同工"]/*') T(C)
```

> Tips：
参考引用：https://www.cnblogs.com/zhaoshujie/p/9594660.html