SQL Server FOR XML PATH 和 STUFF函数的用法

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

　　　　下面我们来写一个例子：

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

　　　　

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','重新提交审核')

数据表和数据

　　　　

 

　　　　一、简单介绍

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

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

　　　　

　　　--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 '[ '+ActionName+' ]' FROM [dbo].[Workflow_Action] FOR XML PATH('')

　　　　

 

　　　　二、实际应用　　

　　  --我们看看一个操作对应的而多格流程
　　  --一共是两层，里面一层查出单独的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

　　　　 

 

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

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

　　　　一、作用

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

　　　　二、语法

　　　　　　STUFF ( character_expression , start , length ,character_expression )

　　　　　　　　参数

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

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

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

　　　　　　　　返回类型　　

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

　　　　三、备注

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

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

　　　　四、sql stuff函数

　　　 --实例一
　　   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

　　　　　

　　　　--实例三、最后我们实现，上面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

　　　　 

 

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