SQL SERVER FOR XML PATH

前言

  FOR XML PATH 有的人可能知道有的人可能不知道，其实它就是将查询结果集以XML形式展现，有了它我们可以简化我们的查询语句实现一些以前可能需要借助函数活存储过程来完成的工作。那么以一个实例为主.

一.FOR XML PATH 简单介绍

 那么还是首先来介绍一下FOR XML PATH ，假设现在有一张兴趣爱好表（hobby）用来存放兴趣爱好，表结构如下：

接下来我们来看应用FOR XML PATH的查询结果语句如下：

select * from test FOR XML PATH

结果：

<row>
  <ID>1</ID>
  <NAME>abc32</NAME>
</row>
<row>
  <ID>2</ID>
  <NAME>abd32</NAME>
</row>
<row>
  <ID>3</ID>
  <NAME>abe32</NAME>
</row>
<row>
  <ID>4</ID>
  <NAME>xiaofei</NAME>
</row>
<row>
  <ID>5</ID>
  <NAME>xiaofei</NAME>
</row>
<row>
  <ID>6</ID>
  <NAME>xiaofei</NAME>
</row>

　　

  由此可见FOR XML PATH 可以将查询结果根据行输出成XML各式, 那么，如何改变XML行跟节点的名称呢？代码如下：     

select * from test FOR XML PATH('MyName')

结果一定也可想而知了吧？没错原来的行节点<row> 变成了我们在PATH后面括号()中，自定义的名称<MyName>,结果如下：

<MyName>
  <ID>1</ID>
  <NAME>abc32</NAME>
</MyName>
<MyName>
  <ID>2</ID>
  <NAME>abd32</NAME>
</MyName>
<MyName>
  <ID>3</ID>
  <NAME>abe32</NAME>
</MyName>
<MyName>
  <ID>4</ID>
  <NAME>xiaofei</NAME>
</MyName>
<MyName>
  <ID>5</ID>
  <NAME>xiaofei</NAME>
</MyName>
<MyName>
  <ID>6</ID>
  <NAME>xiaofei</NAME>
</MyName>

这个时候细心的朋友一定又会问那么列节点如何改变呢？还记的给列起别名的关键字AS吗？对了就是用它!代码如下：

select id as idd,name as youname from test FOR XML PATH('MyName')

结果：

<MyName>
  <idd>1</idd>
  <youname>abc32</youname>
</MyName>
<MyName>
  <idd>2</idd>
  <youname>abd32</youname>
</MyName>
<MyName>
  <idd>3</idd>
  <youname>abe32</youname>
</MyName>
<MyName>
  <idd>4</idd>
  <youname>xiaofei</youname>
</MyName>
<MyName>
  <idd>5</idd>
  <youname>xiaofei</youname>
</MyName>
<MyName>
  <idd>6</idd>
  <youname>xiaofei</youname>
</MyName>

既然行的节点与列的节点我们都可以自定义，我们是否可以构建我们喜欢的输出方式呢？还是看代码： 

SELECT '[ '+name+' ]' FROM TEST FOR XML PATH('')

结果

　　

那么其他类型的列怎么自定义？ 没关系，我们将它们转换成字符串类型就行啦！例如：

SELECT '{'+STR(ID)+'}','[ '+Name+' ]' FROM TEST FOR XML PATH('')

好的 FOR XML PATH就基本介绍到这里吧，更多关于FOR XML的知识请查阅帮助文档！

    接下来我们来看一个FOR XML PATH的应用场景吧，开干！

  二.一个应用场景与FOR XML PATH应用

 

  首先呢，我们在增加一张商品表，列分别为（ID,Name,Color）,id商品编号，name名称，color颜色 那么现在表结构如下：

USE [test]
GO
/****** Object:  Table [dbo].[shagnpin]    Script Date: 2018/6/4 17:23:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[shagnpin](
    [Id] [int] NULL,
    [name] [varchar](50) NULL,
    [color] [nvarchar](50) NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[shagnpin] ([Id], [name], [color]) VALUES (1, N'手机', N'red')
GO
INSERT [dbo].[shagnpin] ([Id], [name], [color]) VALUES (2, N'电脑', N'blue')
GO
INSERT [dbo].[shagnpin] ([Id], [name], [color]) VALUES (3, N'数据线', N'black')
GO
INSERT [dbo].[shagnpin] ([Id], [name], [color]) VALUES (4, N'电视机', N'gray')
GO
INSERT [dbo].[shagnpin] ([Id], [name], [color]) VALUES (5, N'口香糖', N'Pink')
GO
INSERT [dbo].[shagnpin] ([Id], [name], [color]) VALUES (6, N'手机', N'red')
GO
INSERT [dbo].[shagnpin] ([Id], [name], [color]) VALUES (7, N'电脑', N'blue')
GO
INSERT [dbo].[shagnpin] ([Id], [name], [color]) VALUES (8, N'数据线', N'black')
GO
INSERT [dbo].[shagnpin] ([Id], [name], [color]) VALUES (9, N'电视机', N'gray')
GO
INSERT [dbo].[shagnpin] ([Id], [name], [color]) VALUES (10, N'口香糖', N'Pink')
GO

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

SELECT B.name,LEFT(ColorList,LEN(ColorList)-1) as name FROM (
SELECT name,
(SELECT color+',' FROM shagnpin 
  WHERE name=A.name 
  FOR XML PATH('')) AS ColorList
FROM shagnpin A 
GROUP BY name
) B 

 

 分析： 好的，那么我们来分析一下，首先看这句：

句是通过FOR XML PATH 将某一商品如电脑的颜色，显示成格式为：“ blue,blue”的格式！

那么接着看：

SELECT B.name,LEFT(ColorList,LEN(ColorList)-1) as name FROM (
SELECT name,
(SELECT color+',' FROM shagnpin 
  WHERE name=A.name 
  FOR XML PATH('')) AS ColorList
FROM shagnpin A 
GROUP BY name
) B

剩下的代码首先是将表分组，在执行FOR XML PATH 格式化，这时当还没有执行最外层的SELECT时查询出的结构为:

 

可以看到StuList列里面的数据都会多出一个逗号，这时随外层的语句:SELECT B.Name,LEFT(ColorList,LEN(ColorList)-1) as COLOR就是来去掉逗号，并赋予有意义的列明！

要是没明白就在看小示例：

SELECT cid ,
        LEFT(ID, LEN(ID) - 1)
 FROM   ( SELECT    cid ,
                    ( SELECT    ID + ','
                      FROM      TEST
                      WHERE     T1.cid = cid
                    FOR
                      XML PATH('')
                    ) AS ID
          FROM      TEST AS T1
          GROUP BY  CID
        ) b  
解析：
1、首先把每行的字段拼接起来条件是外层的ID
 SELECT    ID + ',' FROM  test WHERE     cid = t1.cid   FOR  XML PATH('')  
2、查询两个列去掉重复
SELECT cid , ( SELECT ID + ','   FROM  TEST  WHERE T1.cid = cid  FOR  XML PATH('') ) AS ID FROM TEST AS T1  GROUP BY  CID 
3、在最外层查询两个列吧第二列的最后一个逗号去掉
LEFT(字符创，前几位) 
substring（字符创，开始位置，结束位置）
SELECT cid , LEFT(ID, LEN(ID) - 1) FROM   ( SELECT    cid , ( SELECT    ID + ',' FROM      TEST WHERE  T1.cid = cid  FOR XML PATH('')
  ) AS ID FROM      TEST AS T1 GROUP BY  CID   ) b  

PS:ppl乃是本尊朴朴乐！