FOR XML PATH
,其实它就是将查询结果集以XML形式展现,将多行的结果,展示在同一行。

转载:

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

CREATE
 FUNCTION [dbo].[Getxxxxxxxxx]
    (
      @productCategoryId INT,
      @SplitChar varchar
    )
RETURNS NVARCHAR(MAX)
AS
    BEGIN

        DECLARE @returnProductCategory NVARCHAR(MAX)=N''

        /*-- 注意,CTE 表达式前面必须要用分号“;”把其代码分开,建议在WITH前面加分号“;” --*/
        ;WITH cte AS (
            SELECT
                ProductCategoryID
            FROM dbo.xxxx_xxxx parent(NOLOCK) WHERE ProductCategoryID=@productCategoryId

            UNION ALL

            SELECT
                child.ProductCategoryID
            FROM xxx_xxx child(NOLOCK) INNER JOIN cte ON child.ParentID=cte.ProductCategoryID
        )

        SELECT @returnProductCategory = (
            SELECT  (
                SELECT   
                    CAST(cte.ProductCategoryID AS NVARCHAR(10))+','
                FROM  cte FOR XML PATH('')
            ) AS temp 
        )

        RETURN @returnProductCategory
    END
GO

在SQL Server中利用 FOR XML PATH
语句能够把查询的数据生成XML数据,下面是它的一些应用示例。

    下面我们来写一个例子:

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

        一.FOR XML PATH 简单介绍

  

view
sourceprint?

        假设我们有个工作流程表:

        一.FOR XML PATH 简单介绍

             那么还是首先来介绍一下FOR XML PATH
,假设现在有一张兴趣爱好表(hobby)用来存放兴趣爱好,表结构如下:澳门皇冠金沙网站 1

1 DECLARE @TempTable table(UserID int , UserName nvarchar(50));

    澳门皇冠金沙网站 2澳门皇冠金沙网站 3

             那么还是首先来介绍一下FOR XML PATH
,假设现在有一张兴趣爱好表(hobby)用来存放兴趣爱好,表结构如下:澳门皇冠金沙网站 1

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

2 insert into @TempTable (UserID,UserName) values (1,'a')
CREATE TABLE [dbo].[Workflow_Action](
    [WorkflowSchema] [nvarchar](128) NULL,
    [ActionSchema] [nvarchar](128) NULL,
    [ActionName] [nvarchar](64) NULL
)

INSERT INTO [dbo].[Workflow_Action] ([WorkflowSchema],[ActionSchema],[ActionName]) VALUES ('material-price','confirm','审核通过')
INSERT INTO [dbo].[Workflow_Action] ([WorkflowSchema],[ActionSchema],[ActionName]) VALUES ('material-price','reject','审核驳回')
INSERT INTO [dbo].[Workflow_Action] ([WorkflowSchema],[ActionSchema],[ActionName]) VALUES ('material-price','executing','执行价格')
INSERT INTO [dbo].[Workflow_Action] ([WorkflowSchema],[ActionSchema],[ActionName]) VALUES ('material-price','non-executing','不执行价格')
INSERT INTO [dbo].[Workflow_Action] ([WorkflowSchema],[ActionSchema],[ActionName]) VALUES ('oa-meeting-apply','confirm','审核通过')
INSERT INTO [dbo].[Workflow_Action] ([WorkflowSchema],[ActionSchema],[ActionName]) VALUES ('oa-meeting-apply','reject','审核驳回')
INSERT INTO [dbo].[Workflow_Action] ([WorkflowSchema],[ActionSchema],[ActionName]) VALUES ('oa-officialSeal-apply','confirm','审核通过')
INSERT INTO [dbo].[Workflow_Action] ([WorkflowSchema],[ActionSchema],[ActionName]) VALUES ('oa-officialSeal-apply','reject','审核驳回')
INSERT INTO [dbo].[Workflow_Action] ([WorkflowSchema],[ActionSchema],[ActionName]) VALUES ('oa-officialSeal-apply','returned','归还公章')
INSERT INTO [dbo].[Workflow_Action] ([WorkflowSchema],[ActionSchema],[ActionName]) VALUES ('purchase','commit','提交审核')
INSERT INTO [dbo].[Workflow_Action] ([WorkflowSchema],[ActionSchema],[ActionName]) VALUES ('purchase','reject','采购驳回')
INSERT INTO [dbo].[Workflow_Action] ([WorkflowSchema],[ActionSchema],[ActionName]) VALUES ('purchase','confirm','审核通过')
INSERT INTO [dbo].[Workflow_Action] ([WorkflowSchema],[ActionSchema],[ActionName]) VALUES ('purchase','order','采购下单')
INSERT INTO [dbo].[Workflow_Action] ([WorkflowSchema],[ActionSchema],[ActionName]) VALUES ('purchase','recommit','重新提交审核')
INSERT INTO [dbo].[Workflow_Action] ([WorkflowSchema],[ActionSchema],[ActionName]) VALUES ('purchase','part-consignment','部分收货')
INSERT INTO [dbo].[Workflow_Action] ([WorkflowSchema],[ActionSchema],[ActionName]) VALUES ('purchase','consignment','完成收货')
INSERT INTO [dbo].[Workflow_Action] ([WorkflowSchema],[ActionSchema],[ActionName]) VALUES ('purchase-request','commit','提交审核')
INSERT INTO [dbo].[Workflow_Action] ([WorkflowSchema],[ActionSchema],[ActionName]) VALUES ('purchase-request','confirm','审核通过')
INSERT INTO [dbo].[Workflow_Action] ([WorkflowSchema],[ActionSchema],[ActionName]) VALUES ('purchase-request','reject','申请驳回')
INSERT INTO [dbo].[Workflow_Action] ([WorkflowSchema],[ActionSchema],[ActionName]) VALUES ('purchase-request','recommit','重新提交审核')

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

SELECT * FROM @hobby FOR XML PATH

3 insert into @TempTable (UserID,UserName) values (2,'b')

数据表和数据

SELECT * FROM @hobby FOR XML PATH

       结果:

4    

    澳门皇冠金沙网站 5

       结果:

澳门皇冠金沙网站 6

5 select UserID,UserName from @TempTable FOR XML PATH

 

澳门皇冠金沙网站 7

<row>
  <hobbyID>1</hobbyID>
  <hName>爬山</hName>
</row>
<row>
  <hobbyID>2</hobbyID>
  <hName>游泳</hName>
</row>
<row>
  <hobbyID>3</hobbyID>
  <hName>美食</hName>
</row>

运行这段脚本,将生成如下结果:

    一、简单介绍

<row>
  <hobbyID>1</hobbyID>
  <hName>爬山</hName>
</row>
<row>
  <hobbyID>2</hobbyID>
  <hName>游泳</hName>
</row>
<row>
  <hobbyID>3</hobbyID>
  <hName>美食</hName>
</row>

澳门皇冠金沙网站 8

1 <row>

      接下来,我们用这个方法查询这个表的数据。

澳门皇冠金沙网站 9

      由此可见FOR XML PATH 可以将查询结果根据行输出成XML各式!

2   <UserID>1</UserID>
    select * from [dbo].[Workflow_Action] for xml path
      它可以将查询结果转换为一段XML格式的代码

      由此可见FOR XML PATH 可以将查询结果根据行输出成XML各式!

      那么,如何改变XML行节点的名称呢?代码如下:     

3   <UserName>a</UserName>

    澳门皇冠金沙网站 10

      那么,如何改变XML行节点的名称呢?代码如下:     

SELECT * FROM @hobby FOR XML PATH(‘MyHobby’)

4 </row>
   --for xml path 后面可以写东西,for xml path(Schema),这样写的话,可以将节点<row>变成<Schema>
    select WorkflowSchema as WS,ActionSchema as SC,ActionName as AN from [dbo].[Workflow_Action] for xml path('Schema')

SELECT * FROM @hobby FOR XML PATH(‘MyHobby’)

 

5 <row>

     
 澳门皇冠金沙网站 11

 

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

6   <UserID>2</UserID>
    --我们还可以单独输出某一个字段的值
    SELECT '[ '+ActionName+' ]' FROM [dbo].[Workflow_Action] FOR XML PATH('')

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

澳门皇冠金沙网站 12

7   <UserName>b</UserName>

    澳门皇冠金沙网站 13

澳门皇冠金沙网站 14

<MyHobby>
  <hobbyID>1</hobbyID>
  <hName>爬山</hName>
</MyHobby>
<MyHobby>
澳门皇冠金沙网站,  <hobbyID>2</hobbyID>
  <hName>游泳</hName>
</MyHobby>
<MyHobby>
  <hobbyID>3</hobbyID>
  <hName>美食</hName>
</MyHobby>

8 </row>

 

<MyHobby>
  <hobbyID>1</hobbyID>
  <hName>爬山</hName>
</MyHobby>
<MyHobby>
  <hobbyID>2</hobbyID>
  <hName>游泳</hName>
</MyHobby>
<MyHobby>
  <hobbyID>3</hobbyID>
  <hName>美食</hName>
</MyHobby>

澳门皇冠金沙网站 15

1 大家可以看到两行数据生成了两个节点,修改一下PATH的参数:

    二、实际应用  

澳门皇冠金沙网站 16

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

1 select UserID,UserName from @TempTable FOR XML PATH('lzy')
    --我们看看一个操作对应的而多格流程
    --一共是两层,里面一层查出单独的ActionName,拼成一行,然后使用where条件连接外层
    SELECT WorkflowSchema,
      (SELECT ActionName+',' FROM [dbo].[Workflow_Action] 
      WHERE WorkflowSchema=A.WorkflowSchema    --必须加的条件
      FOR XML PATH('')) AS ActionList
    FROM [dbo].[Workflow_Action] A 
    GROUP BY WorkflowSchema
    

    --where 连接条件必须要,如果去掉,就会查出所有的ActionName,如同上面示例一样
    

    --现在我们优化一下格式,会发现最后多了一个‘,’符号,用LEFT函数去掉他,继续在外面接一层查询
    select B.WorkflowSchema,
        LEFT(B.ActionList,LEN(B.ActionList)-1) as ActionList 
           from (
                 SELECT WorkflowSchema,
                   (SELECT ActionName+',' FROM [dbo].[Workflow_Action] 
                      WHERE WorkflowSchema=A.WorkflowSchema 
                     FOR XML PATH('')) AS ActionList
                FROM [dbo].[Workflow_Action] A 
    GROUP BY WorkflowSchema) as B

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

SELECT hobbyID as ‘MyCode’,hName as ‘MyName’ FROM @hobby FOR XML PATH(‘MyHobby’)

再次运行上述脚本,将生成如下的结果:

     澳门皇冠金沙网站 17

SELECT hobbyID as ‘MyCode’,hName as ‘MyName’ FROM @hobby FOR XML PATH(‘MyHobby’)

 

1 <lzy>

 

 

      那么这个时候我们列的节点名称也会编程我们自定义的名称
<MyCode>与<MyName>结果如下:

2   <UserID>1</UserID>

   接下来,我们再讲一个其他的函数,实现同样的效果,STUFF函数。

      那么这个时候我们列的节点名称也会编程我们自定义的名称
<MyCode>与<MyName>结果如下:

澳门皇冠金沙网站 18

3   <UserName>a</UserName>

    sql
stuff函数用于删除指定长度的字符,并可以在制定的起点处插入另一组字符。sql
stuff函数中如果开始位置或长度值是负数,或者如果开始位置大于第一个字符串的长度,将返回空字符串。如果要删除的长度大于第一个字符串的长度,将删除到第一个字符串中的第一个字符。

澳门皇冠金沙网站 19

<MyHobby>
  <MyCode>1</MyCode>
  <MyName>爬山</MyName>
</MyHobby>
<MyHobby>
  <MyCode>2</MyCode>
  <MyName>游泳</MyName>
</MyHobby>
<MyHobby>
  <MyCode>3</MyCode>
  <MyName>美食</MyName>
</MyHobby>

4 </lzy>

    一、作用

<MyHobby>
  <MyCode>1</MyCode>
  <MyName>爬山</MyName>
</MyHobby>
<MyHobby>
  <MyCode>2</MyCode>
  <MyName>游泳</MyName>
</MyHobby>
<MyHobby>
  <MyCode>3</MyCode>
  <MyName>美食</MyName>
</MyHobby>

澳门皇冠金沙网站 20

5 <lzy>

      删除指定长度的字符,并在指定的起点处插入另一组字符。

澳门皇冠金沙网站 21

    噢!
既然行的节点与列的节点我们都可以自定义,我们是否可以构建我们喜欢的输出方式呢?还是看代码: 

6   <UserID>2</UserID>

    二、语法

    噢!
既然行的节点与列的节点我们都可以自定义,我们是否可以构建我们喜欢的输出方式呢?还是看代码: 

SELECT ‘[ ‘+hName+’ ]’ FROM @hobby FOR XML PATH(”)

7   <UserName>b</UserName>

      STUFF ( character_expression , start , length
,character_expression )

SELECT ‘[ ‘+hName+’ ]’ FROM @hobby FOR XML PATH(”)

   
没错我们还可以通过符号+号,来对字符串类型字段的输出格式进行定义。结果如下:

8 </lzy>

        参数

   
没错我们还可以通过符号+号,来对字符串类型字段的输出格式进行定义。结果如下:

[ 爬山 ][ 游泳 ][ 美食 ]

1 可以看到节点变成,其实PATH() 括号内的参数是控制节点名称的,这样的话大家可以看一下如果是空字符串(不是没有参数)会是什么结果?

        character_expression  一个字符数据表达式。character_expression
可以是常量、变量,也可以是字符列或二进制数据列。

[ 爬山 ][ 游泳 ][ 美食 ]

    那么其他类型的列怎么自定义?
没关系,我们将它们转换成字符串类型就行啦!例如:

1 select UserID,UserName from @TempTable FOR XML PATH('')

        start   一个整数值,指定删除和插入的开始位置。如果
start 或 length 为负,则返回空字符串。如果 start 比第一个
character_expression 长,则返回空字符串。start 可以是 bigint 类型。

    那么其他类型的列怎么自定义?
没关系,我们将它们转换成字符串类型就行啦!例如:

SELECT ‘{‘+STR(hobbyID)+’}’,'[ ‘+hName+’ ]’ FROM @hobby FOR XML PATH(”)

执行上面这段脚本将生成结果:

        length  一个整数,指定要删除的字符数。如果 length
比第一个 character_expression 长,则最多删除到最后一个
character_expression 中的最后一个字符。length 可以是 bigint 类型。

SELECT ‘{‘+STR(hobbyID)+’}’,'[ ‘+hName+’ ]’ FROM @hobby FOR XML PATH(”)

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

1 <UserID>1</UserID>

        返回类型  

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

    接下来我们来看一个FOR XML PATH的应用场景吧!那么开始吧。。。。。。

2 <UserName>a</UserName>

        如果 character_expression
是受支持的字符数据类型,则返回字符数据。如果 character_expression
是一个受支持的 binary 数据类型,则返回二进制数据。

    接下来我们来看一个FOR XML PATH的应用场景吧!那么开始吧。。。。。。

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

3 <UserID>2</UserID>

    三、备注

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

        首先呢!我们在增加一张学生表,列分别为(stuID,sName,hobby),stuID代表学生编号,sName代表学生姓名,hobby列存学生的爱好!那么现在表结构如下:

4 <UserName>b</UserName>

      1、如果开始位置或长度值是负数,或者如果开始位置大于第一个字符串的长度,将返回空字符串。如果要删除的长度大于第一个字符串的长度,将删除到第一个字符串中的第一个字符。

        首先呢!我们在增加一张学生表,列分别为(stuID,sName,hobby),stuID代表学生编号,sName代表学生姓名,hobby列存学生的爱好!那么现在表结构如下:

           澳门皇冠金沙网站 22

这样就不显示上级节点了,大家知道在 PATH 模式中,列名或列别名被作为 XPath
表达式来处理,也就是说,是列的名字,这样大胆试验一下不给指定列名和别名会是怎么样?

      2、如果结果值大于返回类型支持的最大值,则产生错误。

           澳门皇冠金沙网站 22

       
这时,我们的要求是查询学生表,显示所有学生的爱好的结果集,代码如下:

1 select CAST(UserID AS varchar) + '',UserName + '' from @TempTable FOR XML PATH('')

    四、sql stuff函数

       
这时,我们的要求是查询学生表,显示所有学生的爱好的结果集,代码如下:

澳门皇冠金沙网站 24

运行上面这句将生成结果

    --实例一
     select STUFF('abcdefg',1,0,'1234')       --结果为'1234abcdefg'
     select STUFF('abcdefg',1,1,'1234')       --结果为'1234bcdefg'
     select STUFF('abcdefg',2,1,'1234')       --结果为'a1234cdefg'
     select STUFF('abcdefg',2,2,'1234')       --结果为'a1234defg'

    --实例二、SQL 将列转成字符串并用逗号分隔
    --同样的,我们也用到了for xml path这个方法
    SELECT STUFF((SELECT ',' + ActionName FROM [dbo].[Workflow_Action] FOR XML PATH('')),1,1,'') AS WA

澳门皇冠金沙网站 25

SELECT B.sName,LEFT(StuList,LEN(StuList)-1) as hobby FROM (
SELECT sName,
(SELECT hobby+’,’ FROM student 
  WHERE sName=A.sName 
  FOR XML PATH(”)) AS StuList
FROM student A 
GROUP BY sName
) B 

1a2b

     澳门皇冠金沙网站 26

SELECT B.sName,LEFT(StuList,LEN(StuList)-1) as hobby FROM (
SELECT sName,
(SELECT hobby+’,’ FROM student 
  WHERE sName=A.sName 
  FOR XML PATH(”)) AS StuList
FROM student A 
GROUP BY sName
) B 

澳门皇冠金沙网站 27

所有数据都生成一行,而且还没有连接字符,这样的数据可能对大家没有用处,还可以再变化一下:

    --实例三、最后我们实现,上面for xml path的功能
    --先查出两个字段,然后对ActionName这个字段进行转化,where条件记得加上,不加就会显示出所有的ActionName
    select WorkflowSchema,
           ActionName=(STUFF((select ',' + ActionName 
                              from [dbo].[Workflow_Action] a 
                              where a.WorkflowSchema=b.WorkflowSchema for xml path('')),1,1,''))    --where条件必须加上 
    from [dbo].[Workflow_Action] b group by WorkflowSchema

澳门皇冠金沙网站 28

         结果如下:澳门皇冠金沙网站 29

1 select CAST(UserID AS varchar) + ',',UserName + '',';' from @TempTable FOR XML PATH('')

     澳门皇冠金沙网站 30

         结果如下:澳门皇冠金沙网站 29

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

生成结果

 

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

SELECT hobby+’,’ FROM student 
  WHERE sName=A.sName 
  FOR XML PATH(”)

1,a;2,b;

    对比以上两种做法,可以自行比较哪种方式更加简便。

SELECT hobby+’,’ FROM student 
  WHERE sName=A.sName 
  FOR XML PATH(”)

这句是通过FOR XML PATH 将某一姓名如张三的爱好,显示成格式为:“
爱好1,爱好2,爱好3,”的格式!

大家现在明白了吧,可以通过控制参数来生成自己想要的结果,例如:

这句是通过FOR XML PATH 将某一姓名如张三的爱好,显示成格式为:“
爱好1,爱好2,爱好3,”的格式!

那么接着看:

1 select '{' + CAST(UserID AS varchar) + ',','"' +UserName + '"','}' from @TempTable FOR XML PATH('')

那么接着看:

澳门皇冠金沙网站 32

生成结果

澳门皇冠金沙网站 33

SELECT B.sName,LEFT(StuList,LEN(StuList)-1) as hobby FROM (
SELECT sName,
(SELECT hobby+’,’ FROM student 
  WHERE sName=A.sName 
  FOR XML PATH(”)) AS StuList
FROM student A 
GROUP BY sName
) B  

{1,”a”}{2,”b”}

SELECT B.sName,LEFT(StuList,LEN(StuList)-1) as hobby FROM (
SELECT sName,
(SELECT hobby+’,’ FROM student 
  WHERE sName=A.sName 
  FOR XML PATH(”)) AS StuList
FROM student A 
GROUP BY sName
) B  

澳门皇冠金沙网站 34

还可以生成其他格式,大家可以根据自己需要的格式进行组合。

澳门皇冠金沙网站 35

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

下面是一个数据统计的应用,希望大家可以通过下面的实例想到更多的应用

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

澳门皇冠金沙网站 36

01 DECLARE @T1 table(UserID int , UserName nvarchar(50),CityName nvarchar(50));

澳门皇冠金沙网站 36

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

02 insert into @T1 (UserID,UserName,CityName) values (1,'a','上海')

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

03 insert into @T1 (UserID,UserName,CityName) values (2,'b','北京')
04 insert into @T1 (UserID,UserName,CityName) values (3,'c','上海')
05 insert into @T1 (UserID,UserName,CityName) values (4,'d','北京')
06 insert into @T1 (UserID,UserName,CityName) values (5,'e','上海')
07    
08 SELECT B.CityName,LEFT(UserList,LEN(UserList)-1) FROM (
09 SELECT CityName,
10     (SELECT UserName+',' FROM @T1 WHERE CityName=A.CityName  FOR XML PATH('')) AS UserList
11 FROM @T1 A 
12 GROUP BY CityName
13 ) B

生成结果(每个城市的用户名)

北京 b,d
上海 a,c,e

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图