T SQL: stored procedure讀取XML

這裡記錄一下自己覺得好用的讀XML方法
首先，看一下要執行的SP及傳入的XML參數
Exec sp_TestXML
@prdxml ='<products>
<data>
<id>3</id>
<itemdata>
<itemname>notebook</itemname>
<amount>30000</amount>
</itemdata>
<itemdata>
<itemname>CPU</itemname>
<amount>5000</amount>
</itemdata>
</data>
</products>'


XML的格式說明:
<products>的tag裡，只能有一個<data> tag
但是<data>裡可以有多個<itemdata>
也就是master-detail的格式

再來看一下stroed procedure內容：

Create PROCEDURE [dbo].[sp_TestXML]
@prdxml xml

AS
BEGIN
Select x.value('.','VARCHAR(5)') as id
from @prdxml.nodes('/Products/data/id') as params(x)


Select Item.value('(./ItemName)[1]','VARCHAR(max)') as ItemName
,Item.value('(./Amount)[1]','int') as Amount
from @prdxml.nodes('/Products/data/ItemData') as params(x)


Select Item.value('ItemName[1]','VARCHAR(max)') as ItemName
,Item.value('Amount[1]','int') as Amount
from @prdxml.nodes('/Products/data/ItemData') as Product(Item)


RETURN

END

首先，參數要宣告成xml型態
裡面第一段寫法，是要讀XML的master部份
第二段和第三段寫法，都是要讀detail的部份
只是table的名稱有差異
可以比較一下這三段的From子句
以及Select子句的差別
看自己喜歡哪種寫法

SQL: ISNULL(), NVL(), IFNULL() 函數

在寫SQL語法時
若要將SELECT的欄位由NULL轉為空字串(empty string)
以我用過的三個資料庫的函數分別如下:
Oracle: NVL()
SQL Server: ISNULL()
MySQL: IFNULL()

以SQL Server為例，可以用這樣的語法來將null轉為你要替換的字串
SELECT ISNULL(Column,'')
FROM YourTable

不過，寫了這麼多年的SQL
很少遇到要將欄位的空字串轉為NULL
我是用REPLACE()來轉換
SQL Server的用法如下:
SELECT REPLACE(Column,'',NULL)
FROM YourTable



取得Identity的方法:@@IDENTITY, SCOPE_IDENTITY(), IDENT_CURRENT()

關於取得SQL Server的IDENTITY(識別值)，有以下三種方法(可點hyperlink連到MSDN看明細)

• @@IDENTITY
• SCOPE_IDENTITY()
• IDENT_CURRENT('TableName')

上面三種用法也不太一樣
我在寫stored procedure的時候
若需要寫入的資料是master-detail的話
比較常用的是@@IDENTITY
在寫入master之後
可用下面語法來取得寫入master Table的IDENTITY後，再寫入Detail Table
 DECLARE  @TableIdentity  BIGINT



SET @TableIdentity  = @@IDENTITY



若是除了寫入master-detail的table之外
還需要寫入log檔的table
那麼我會用下面語法來取得該Table最後的一筆IDENTITY
再將這個IDENTITY寫入到log檔



SET @TableIdentity  = IDENT_CURRENT('TableName')



因為master-detail有可能會寫入失敗
若使用@@IDENTITY，則會取到null值
而使用IDENT_CURRENT()，會取得原本應該產生的IDENTITY

@ @ IDENTITY

generate MD5 string with T-SQL

要將字串透過T-SQL的函數做MD5的編碼
一開始參考了MSDN的HashBytes說明

執行了下列語法
SELECT hashbytes('MD5','12345')

出來的結果是
0x827CCB0EEA8A706C4C34A16891F84E7B

嗯，我不要前面的0x
所以，再conver一下
SELECT CONVERT(VARCHAR(32),hashbytes('MD5','12345'),2)

輸出結果:
827CCB0EEA8A706C4C34A16891F84E7B