某某茶叶有限公司欢迎您!
金沙棋牌在线 > 必赢棋牌官网 > SQL Server 【CTE + FOR XML PATH】使用笔记~

SQL Server 【CTE + FOR XML PATH】使用笔记~

时间:2019-11-30 11:30

   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

    下面我们来写一个例子:

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

  

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

        一.FOR XML PATH 简单介绍

    必赢棋牌官网 1必赢棋牌官网 2

             那么还是首先来介绍一下FOR XML PATH ,假设现在有一张兴趣爱好表(hobby)用来存放兴趣爱好,表结构如下:必赢棋牌官网 3

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

    必赢棋牌官网 4

       结果:

 

必赢棋牌官网 5

    一、简单介绍

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

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

必赢棋牌官网 6

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

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

必赢棋牌官网,    必赢棋牌官网 7

      那么,如何改变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 * FROM @hobby FOR XML PATH('MyHobby')

       必赢棋牌官网 8

 

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

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

    必赢棋牌官网 9

必赢棋牌官网 10

 

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