示例1:从XML中解析数据到表变量
DECLARE @ItemMessage XML
DECLARE @ItemTable TABLE(ItemNumber INT PRIMARY KEY,ItemDescription NVARCHAR(300))
SET @ItemMessage=N'<ItemList>
<Item>
<ItemNumber>1</ItemNumber>
<ItemDescription>customer1</ItemDescription>
</Item>
<Item>
<ItemNumber>2</ItemNumber>
<ItemDescription>customer2</ItemDescription>
</Item>
</ItemList>'
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
示例二: 解析带命名空间的XML数据
DECLARE @ItemMessage XML
DECLARE @ItemTable TABLE(ItemNumber INT PRIMARY KEY,ItemDescription NVARCHAR(300))
SET @ItemMessage=N'<ItemList xmlns="http://cd.love.com/SOA">
<Item>
<ItemNumber>1</ItemNumber>
<ItemDescription>customer1</ItemDescription>
</Item>
<Item>
<ItemNumber>2</ItemNumber>
<ItemDescription>customer2</ItemDescription>
</Item>
</ItemList>'
;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