SQL - For XML Path (多行合併)

用SQL將多筆資料的內容做合併、格式化合併後的內容。

還記得之前有一篇文章在說明如何將資料轉為XML RAW,當時為了拆解xml raw還另外寫了

一篇 how to read sql xml raw in .NET,當初是因為資料是一對多,所以採取這種方式,

後來發現很多頁面都需要用到這種方式,如果沒有封裝此function,那很多頁面都要有一段

parse xml的function,但是後來又發現這個Function又不是都適用,因此內心有個疑問,

應該有人和我一樣,覺得每次合併資料又需要Parse來解xml,那不是很費工嗎 ? ,應該有更快的方法吧 ?

而且如果我在組合資料時就能一次到位了,根本不需要再花effort重新拆解。

 

因此爬文找到了SQL的FOR XML PATH用法,使用方式如下

 

[RawData  in #Temp Table]

 

[Final display on Gridview]

 

[Solution]

select ROW_NUMBER() over (ORDER BY FinalData) as NO, replace(replace(FinalData,'&lt;','<'),'&gt;','>') as FinalData from
(
select FinalData =
(
     SELECT  '<li>' + Sentence FROM 
     (
          select * from #temp
     ) AA for xml path(''),root('ul')
)
) FD
 
 

[Show as below]

 

這邊要提醒的是因為html透過SQL執行後會自動做Encode的動作,可以自己寫一隻Decode的function

解決,這邊我就不一一詳敘了,如果上敘紅色部分不處理的話,顯示會變得如下

 

最後.NET的Gridview要將資料顯示,記得做此設定,避免html資料又encode

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False">
            <Columns>
                <asp:BoundField DataField="No" HeaderText="NO" />
                <asp:BoundField DataField="FinalData" HeaderText="FinalData" HtmlEncode="False" />
            </Columns>
        </asp:GridView>

 

最後我稍作解釋一下,或者看倌可以自己上網爬使用方式,

fox xml path('') 可將資料作合併,而在後方加上的root('ul'), 這是可以替資料加入根節點

如果對for xml有興趣詳細了解的可參考此篇

 

優點:

  • 顯示一對多的資料,可快速完成。

缺點:

  • 如果將Html混在SQL Script中,將來不好維護(單純串字串則無需擔憂此點)。