很久就想写总结一下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>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
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
运行结果:
示例二: 解析带命名空间的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
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
运行结果:
——致力于微软企业解决方案、项目管理及技术培训