SQL Server XML數(shù)據(jù)解析

? ? ?很久就想寫總結(jié)一下SQL Server解析XML數(shù)據(jù)的常用方法了，下面就給出一些示例，有事沒(méi)事，你也可以參照著示例自己動(dòng)手嘗試著實(shí)現(xiàn)一下自己的需求。

示例1：從XML中解析數(shù)據(jù)到表變量

DECLARE?@ItemMessage?XML?
DECLARE?@ItemTable?TABLE(ItemNumber?INT?PRIMARY?KEY,ItemDescription?NVARCHAR(300))
SET?@ItemMessage=N''
INSERT?INTO?@ItemTable?
(?
ItemNumber,?
ItemDescription?
)?
SELECT?T.c.value('(ItemNumber/text())[1]','INT'),?
T.c.value('(ItemDescription/text())[1]','NVARCHAR(300)')?
FROM?@ItemMessage.nodes('/ItemList/Item')?AS?T(c)
SELECT?ItemNumber,?
ItemDescription?
FROM?@ItemTable

運(yùn)行結(jié)果：

示例二： 解析帶命名空間的XML數(shù)據(jù)

DECLARE?@ItemMessage?XML?
DECLARE?@ItemTable?TABLE(ItemNumber?INT?PRIMARY?KEY,ItemDescription?NVARCHAR(300))
SET?@ItemMessage=N''
;WITH?XMLNAMESPACES(DEFAULT?'http://cd.love.com/SOA')?
INSERT?INTO?@ItemTable?
(?
ItemNumber,?
ItemDescription?
)?
SELECT?T.c.value('(ItemNumber/text())[1]','INT'),?
T.c.value('(ItemDescription/text())[1]','NVARCHAR(300)')?
FROM?@ItemMessage.nodes('/ItemList/Item')?AS?T(c)
SELECT?ItemNumber,?
ItemDescription?
FROM?@ItemTable

運(yùn)行結(jié)果：