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子句的差別
看自己喜歡哪種寫法