前言:本人SQL技術很爛,然后工作時間也不久,許多東西都還在學習中,說的不好的地方盡請諒解.

首先跟大家說一下我今天遇到的問題吧.

查出的數據有三列,第一列存放的是32位的GUID,Res_Name存放的是一個物資類型.Res_Data存放的是部門的GUID.我現在需要得到的數據是這樣的.

首先大家可以看到.第一張圖的Res_Data中有多個部門的GUID,中間用逗號隔開的.

我當時想到的愚蠢的辦法就是

 1 @MaterialTypeName nvarchar(200), 2 @CentralizedName nvarchar(200), 3 @start int, 4 @limit int, 5 @totalCount int output  6 AS 7 BEGIN 8     SET NOCOUNT ON; 9     select 10         ROW_NUMBER() over (order by res_id asc) as RowNumber,11         * 12     into #List 13     from 14         UBiplatform..T_RESOURCE 15     WHERE Res_Parent_Code='741cdd2bef2e479f8c5dd35cf6e8bf2a'16      17         18     declare @i int,@count int;19     declare @Centralized nvarchar(200);20     declare @List1 table(id int, ResId nvarchar(50),ResName nvarchar(50),ResData nvarchar(50));21     select @count=COUNT(*) from UBIPlatform..T_RESOURCE WHERE Res_Parent_Code='741cdd2bef2e479f8c5dd35cf6e8bf2a' 22     set @i=123     while @i<=@count24     begin25         if @i in (select RowNumber from #List)26         begin27             set @Centralized='';28             select 29                 @Centralized=@Centralized+','+LTRIM(Res_Name) 30             from UBIPlatform.dbo.FN_GETMultiValTable(31                 (select 32                     Res_Data 33                 from 34                     UBIPlatform..T_RESOURCE 35                 where 36                     Res_Id=(select Res_Id from #List where RowNumber=@i))) ge37             inner join UBIPlatform..T_RESOURCE r on r.Res_Id=ge.nvalue38             39             if @Centralized!=''40             begin41                 insert into 42                     @List1 43                 select 44                     @i,45                     Res_Id,46                     Res_Name,47                     (RIGHT(@Centralized,LEN(@Centralized)-1)) 48                 from 49                     UBIPlatform..T_RESOURCE 50                 where Res_Id=(select Res_Id from #List where RowNumber=@i)51             end52             else53             begin54                 insert into 55                     @List1 56                 select 57                     @i,58                     Res_Id,59                     Res_Name,60                     @Centralized61                 from 62                     UBIPlatform..T_RESOURCE 63                 where Res_Id=(select Res_Id from #List where RowNumber=@i)64             end    65         end66         set @i=@i+167     end68     69     select ROW_NUMBER() over (order by id asc) as RowNumber,* into #List2 from @List1 where 70     (@MaterialTypeName is null or @MaterialTypeName = '' or ResName  like '%'+@MaterialTypeName+'%')71     and (@CentralizedName is null or @CentralizedName = '' or ResData  like '%'+@CentralizedName+'%')72     73     select top(@limit) * from #List2 where RowNumber > @start order by RowNumber asc74     select @totalCount =COUNT(1) from @List175 END

這個是我開始寫出來的一個.爛到不行.雖然是解決了我的需求.但是顯而易見這種辦法是不可取的.后來請教同事,跟我介紹了FOR XML PATH,我查閱了一下就是將查詢結果集以XML形式展現.

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

結果:

 1 <row> 2   <Res_Id>239dbe35bd8446afb262f62712d8eb1b</Res_Id> 3   <Res_Name>修理費-設備備件</Res_Name> 4   <Res_Data>4BD2D7C9D91546B09BA4438EE583F682</Res_Data> 5 </row> 6 <row> 7   <Res_Id>4d35c89868854d649963410a126b4c30</Res_Id> 8   <Res_Name>低值易耗-計量器具</Res_Name> 9   <Res_Data>4ADEEC453DE04910B0136593CBB4187C</Res_Data>10 </row>11 <row>12   <Res_Id>4e74469a37894ea8a7ddd5e356433119</Res_Id>13   <Res_Name>物料消耗-計算機耗材</Res_Name>14   <Res_Data>4BD2D7C9D91546B09BA4438EE583F682,9C87FFAFD8D24B5BBEA3BF1221DD507B</Res_Data>15 </row>16 <row>17   <Res_Id>608f30860c16430aa8b13f98df0ca9f3</Res_Id>18   <Res_Name>物料消耗-水票</Res_Name>19   <Res_Data></Res_Data>20 </row>21 <row>22   <Res_Id>87a4cefa112241c1b648454e7b3682d9</Res_Id>23   <Res_Name>低值易耗-工具及其他</Res_Name>24   <Res_Data></Res_Data>25 </row>26 <row>27   <Res_Id>c2908fe510dd476aa878622dd9d07c83</Res_Id>28   <Res_Name>物料消耗-雜品</Res_Name>29   <Res_Data></Res_Data>30 </row>31 <row>32   <Res_Id>c9014727c9804c6e9df4cc1bc1487a84</Res_Id>33   <Res_Name>勞動保護費-勞保用品</Res_Name>34   <Res_Data>D5566FDCDBB448FAB4A48D20A2492626</Res_Data>35 </row>36 <row>37   <Res_Id>d3397fdcb454440f88c9c4f9432b3f40</Res_Id>38   <Res_Name>低值易耗-辦公設施</Res_Name>39   <Res_Data>9C87FFAFD8D24B5BBEA3BF1221DD507B</Res_Data>40 </row>41 <row>42   <Res_Id>d8222bcaeaba460db945324cb0a93a23</Res_Id>43   <Res_Name>修理費-計算機備件</Res_Name>44   <Res_Data>4BD2D7C9D91546B09BA4438EE583F682</Res_Data>45 </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>