sql解析xml

sql解析xml，学习重点建议先　对xml结构的了解，和对xpath路径的熟悉，等等，xpath据说是w3c标准的，有兴趣可以重点了解下

除了下面的（１）

另附，（２）sqlXML操作，http://www.cnblogs.com/l1pe1/archive/2010/07/28/1787254.html

（３）xml xpath的介绍，http://www.cnblogs.com/skyblue/archive/2008/06/19/900187.html

 

－－－－－－－－－

（１）转：http://www.cnblogs.com/fuhongwei041/archive/2010/09/04/1818213.html

示例1：从XML中解析数据到表变量

DECLARE @ItemMessage XML
DECLARE @ItemTable TABLE(ItemNumber INT PRIMARY KEY,ItemDescription NVARCHAR(300))
SET @ItemMessage=N'<ItemList>
<Item>
<ItemNumber>1</ItemNumber>
<ItemDescription>XBox 360,超值</ItemDescription>
</Item>
<Item>
<ItemNumber>2</ItemNumber>
<ItemDescription>Windows Phone7,快来尝鲜吧</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

运行结果：
ItemNumber ItemDescription
1 XBos 360超值
2 Windows Phone7 快来尝鲜吧

 

示例二： 解析带命名空间的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>XBox 360,超值</ItemDescription>
</Item>
<Item>
<ItemNumber>2</ItemNumber>
<ItemDescription>Windows Phone7,快来尝鲜吧</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

运行结果：(同示例一)