SQL-学习使用FOR XML PATH

前言:本人SQL技术很烂,然后工作时间也不久,许多东西都还在学习中,说的不好的地方尽请谅解.

首先跟大家说一下我今天遇到的问题吧.

查出的数据有三列,第一列存放的是32位的GUID,Res_Name存放的是一个物资类型.Res_Data存放的是部门的GUID.我现在需要得到的数据是这样的.

SQL-学习使用FOR XML PATH

首先大家可以看到.第一张图的Res_Data中有多个部门的GUID,中间用逗号隔开的.

我当时想到的愚蠢的办法就是

 @MaterialTypeName nvarchar(200),

 @CentralizedName nvarchar(200),

 @start int,

 @limit int,

 @totalCount int output

 AS

 BEGIN

     SET NOCOUNT ON;

     select

         ROW_NUMBER() over (order by res_id asc) as RowNumber,

         *

     into #List

     from

         UBIPlatform..T_RESOURCE

     WHERE Res_Parent_Code='741cdd2bef2e479f8c5dd35cf6e8bf2a'

     declare @i int,@count int;

     declare @Centralized nvarchar(200);

     declare @List1 table(id int, ResId nvarchar(50),ResName nvarchar(50),ResData nvarchar(50));

     select @count=COUNT(*) from UBIPlatform..T_RESOURCE WHERE Res_Parent_Code='741cdd2bef2e479f8c5dd35cf6e8bf2a'

     set @i=1

     while @i<=@count

     begin

         if @i in (select RowNumber from #List)

         begin

             set @Centralized='';

             select

                 @Centralized=@Centralized+','+LTRIM(Res_Name)

             from UBIPlatform.dbo.FN_GETMultiValTable(

                 (select

                     Res_Data

                 from

                     UBIPlatform..T_RESOURCE

                 where

                     Res_Id=(select Res_Id from #List where RowNumber=@i))) ge

             inner join UBIPlatform..T_RESOURCE r on r.Res_Id=ge.nvalue

             if @Centralized!=''

             begin

                 insert into

                     @List1

                 select

                     @i,

                     Res_Id,

                     Res_Name,

                     (RIGHT(@Centralized,LEN(@Centralized)-1))

                 from

                     UBIPlatform..T_RESOURCE

                 where Res_Id=(select Res_Id from #List where RowNumber=@i)

             end

             else

             begin

                 insert into

                     @List1

                 select

                     @i,

                     Res_Id,

                     Res_Name,

                     @Centralized

                 from

                     UBIPlatform..T_RESOURCE

                 where Res_Id=(select Res_Id from #List where RowNumber=@i)

             end

         end

         set @i=@i+1

     end

     select ROW_NUMBER() over (order by id asc) as RowNumber,* into #List2 from @List1 where

     (@MaterialTypeName is null or @MaterialTypeName = '' or ResName  like '%'+@MaterialTypeName+'%')

     and (@CentralizedName is null or @CentralizedName = '' or ResData  like '%'+@CentralizedName+'%')

     select top(@limit) * from #List2 where RowNumber > @start order by RowNumber asc

     select @totalCount =COUNT(1) from @List1

 END

这个是我开始写出来的一个.烂到不行.虽然是解决了我的需求.但是显而易见这种办法是不可取的.后来请教同事,跟我介绍了FOR XML PATH,我查阅了一下就是将查询结果集以XML形式展现.

select Res_Id,Res_Name,Res_Data from UBIPlatform..T_RESOURCE where Res_Parent_Code='741cdd2bef2e479f8c5dd35cf6e8bf2a' FOR XML PATH

结果:

 <row>

   <Res_Id>239dbe35bd8446afb262f62712d8eb1b</Res_Id>

   <Res_Name>修理费-设备备件</Res_Name>

   <Res_Data>4BD2D7C9D91546B09BA4438EE583F682</Res_Data>

 </row>

 <row>

   <Res_Id>4d35c89868854d649963410a126b4c30</Res_Id>

   <Res_Name>低值易耗-计量器具</Res_Name>

   <Res_Data>4ADEEC453DE04910B0136593CBB4187C</Res_Data>

 </row>

 <row>

   <Res_Id>4e74469a37894ea8a7ddd5e356433119</Res_Id>

   <Res_Name>物料消耗-计算机耗材</Res_Name>

   <Res_Data>4BD2D7C9D91546B09BA4438EE583F682,9C87FFAFD8D24B5BBEA3BF1221DD507B</Res_Data>

 </row>

 <row>

   <Res_Id>608f30860c16430aa8b13f98df0ca9f3</Res_Id>

   <Res_Name>物料消耗-水票</Res_Name>

   <Res_Data></Res_Data>

 </row>

 <row>

   <Res_Id>87a4cefa112241c1b648454e7b3682d9</Res_Id>

   <Res_Name>低值易耗-工具及其他</Res_Name>

   <Res_Data></Res_Data>

 </row>

 <row>

   <Res_Id>c2908fe510dd476aa878622dd9d07c83</Res_Id>

   <Res_Name>物料消耗-杂品</Res_Name>

   <Res_Data></Res_Data>

 </row>

 <row>

   <Res_Id>c9014727c9804c6e9df4cc1bc1487a84</Res_Id>

   <Res_Name>劳动保护费-劳保用品</Res_Name>

   <Res_Data>D5566FDCDBB448FAB4A48D20A2492626</Res_Data>

 </row>

 <row>

   <Res_Id>d3397fdcb454440f88c9c4f9432b3f40</Res_Id>

   <Res_Name>低值易耗-办公设施</Res_Name>

   <Res_Data>9C87FFAFD8D24B5BBEA3BF1221DD507B</Res_Data>

 </row>

 <row>

   <Res_Id>d8222bcaeaba460db945324cb0a93a23</Res_Id>

   <Res_Name>修理费-计算机备件</Res_Name>

   <Res_Data>4BD2D7C9D91546B09BA4438EE583F682</Res_Data>

 </row>

那么，如何改变XML行节点的名称呢？代码如下：

select Res_Id,Res_Name,Res_Data from UBIPlatform..T_RESOURCE where Res_Parent_Code='741cdd2bef2e479f8c5dd35cf6e8bf2a' FOR XML PATH('RESOURCE')

原来的行节点<row> 变成了我们在PATH后面括号()中，自定义的名称<RESOURCE>,结果如下：

<RESOURCE>

  <Res_Id>239dbe35bd8446afb262f62712d8eb1b</Res_Id>

  <Res_Name>修理费-设备备件</Res_Name>

  <Res_Data>4BD2D7C9D91546B09BA4438EE583F682</Res_Data>

</RESOURCE>

<RESOURCE>

  <Res_Id>4d35c89868854d649963410a126b4c30</Res_Id>

  <Res_Name>低值易耗-计量器具</Res_Name>

  <Res_Data>4ADEEC453DE04910B0136593CBB4187C</Res_Data>

</RESOURCE>

<RESOURCE>

  <Res_Id>4e74469a37894ea8a7ddd5e356433119</Res_Id>

  <Res_Name>物料消耗-计算机耗材</Res_Name>

  <Res_Data>4BD2D7C9D91546B09BA4438EE583F682,9C87FFAFD8D24B5BBEA3BF1221DD507B</Res_Data>

</RESOURCE>

<RESOURCE>

  <Res_Id>608f30860c16430aa8b13f98df0ca9f3</Res_Id>

  <Res_Name>物料消耗-水票</Res_Name>

  <Res_Data></Res_Data>

</RESOURCE>

<RESOURCE>

  <Res_Id>87a4cefa112241c1b648454e7b3682d9</Res_Id>

  <Res_Name>低值易耗-工具及其他</Res_Name>

  <Res_Data></Res_Data>

</RESOURCE>

<RESOURCE>

  <Res_Id>c2908fe510dd476aa878622dd9d07c83</Res_Id>

  <Res_Name>物料消耗-杂品</Res_Name>

  <Res_Data></Res_Data>

</RESOURCE>

<RESOURCE>

  <Res_Id>c9014727c9804c6e9df4cc1bc1487a84</Res_Id>

  <Res_Name>劳动保护费-劳保用品</Res_Name>

  <Res_Data>D5566FDCDBB448FAB4A48D20A2492626</Res_Data>

</RESOURCE>

<RESOURCE>

  <Res_Id>d3397fdcb454440f88c9c4f9432b3f40</Res_Id>

  <Res_Name>低值易耗-办公设施</Res_Name>

  <Res_Data>9C87FFAFD8D24B5BBEA3BF1221DD507B</Res_Data>

</RESOURCE>

<RESOURCE>

  <Res_Id>d8222bcaeaba460db945324cb0a93a23</Res_Id>

  <Res_Name>修理费-计算机备件</Res_Name>

  <Res_Data>4BD2D7C9D91546B09BA4438EE583F682</Res_Data>

</RESOURCE>

OK,接下来我就用这个FOR XML PATH用在我的问题上,

@MaterialTypeName nvarchar(200),

@CentralizedName nvarchar(200),

@start int,

@limit int,

@totalCount int output

AS

BEGIN

    SET NOCOUNT ON;

    select

        t_r.Res_Id as ResId,

        t_r.Res_Name as ResName,

        STUFF((select ','+t_r1.Res_Name

    from UBIPlatform.dbo.T_RESOURCE t_r1 where t_r1.Res_Id in

        (select nvalue from UBIPlatform.dbo.FN_GETMultiValTable(t_r.Res_Data))

        for xml path('')),1,1,'') as ResData

    into

        #List1

    from

        UBIPlatform..T_RESOURCE t_r

    where Res_Parent_Code='741cdd2bef2e479f8c5dd35cf6e8bf2a'

    select

        ROW_NUMBER() over (order by ResId asc) as RowNumber,

        * into #List2 from #List1

    where (@MaterialTypeName is null or @MaterialTypeName = '' or ResName  like '%'+@MaterialTypeName+'%')

    and (@CentralizedName is null or @CentralizedName = '' or ResData  like '%'+@CentralizedName+'%')

    select top(@limit) * from #List2 where RowNumber > @start order by RowNumber asc

    select @totalCount =COUNT(1) from #List2

END

就这样,才几行的SQL就把我的问题解决了.

秒客网

SQL-学习使用FOR XML PATH

相关文章