SQL SERVER 解析XML

示例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