SQL - for xml raw

  • 3081
  • 0
  • SQL
  • 2015-03-25

摘要:sql - for xml raw

如果你的客戶要求你做出的顯示方式如下,一般我們在寫程式時,我們會如何做 ?

 一定是用個While loop 迴圈去把DataTable內的資料一個一個串起來,然後再丟

到Datagrid 或是 Repaeater.   其實有個很簡單的做法,一個SQL就搞定了

 

If  your customer requre the data display must show like below. How will you do ?

I think I will use While loop to do it.  And bind with Datagrid or Repeater ...etc.

Now there is a nice sql script can help us do it easier.  Please see my sample.

 

【Target】

 

 

 

【解決方式(solution)】

SELECT Row_number()
OVER (
ORDER BY verb) AS Column1,
TB2.contain AS Column2
FROM (SELECT *
FROM (SELECT 'I' AS Suject,
'LOVE' AS Verb,
'U' AS Noun) A) TB1
OUTER apply (SELECT Contain=(SELECT *
FROM ((SELECT *
FROM (SELECT 'LOVE' AS
Verb,
'Promise' AS Do,
'Girl Friend' AS
Target,
'Happiness' AS
Finally) A
WHERE A.verb = TB1.verb))B
FOR xml raw)) TB2

 

 

【Result】

 

這樣的XML格式,再從後端將欄位值讀取出後加工就完成了,對於開發者來說

真的是非常的省時又省力的語法,CP值非常的高,下篇文章我會分享在.NET如何

讀取XML

 

And U can coding a function to read this  value of Column and show it  on web page.

It's very useful.  I like it. N next article I will share how to read this type of column in .NET.