用SQL Server提供的函数处理XML文档

日期： 2010-01-06 作者：Denny Cherry翻译：冯昀晖 来源：TechTarget中国 英文

自SQL Server 2000发布以来，处理XML数据就成为了数据库管理员们经常讨论的一个话题。常见的用法是前端应用程序以入参方式传给存储过程XML文档。然而，有时你会遇到要处理一个文件夹中一组XML文件，将它们加载到数据库中并随后处理到SQL Server数据表的情况。这一点更困难，而且介绍如何来做的文档资料也很少。

　　你可以在几种技术中做选择，但这些技术大部分都十分复杂。我曾用过SQL Server集成服务(SSIS)，数据转换服务(DTS)和自己编写的Windows应用程序来读取和加载文件。然而，我最喜欢的技术是采用OPENROWSET函数来实现它。该函数提供了极大的灵活性，因为你可……

我们一直都在努力坚持原创.......请不要一声不吭，就悄悄拿走。

我原创，你原创，我们的内容世界才会更加精彩！

【所有原创内容版权均属TechTarget，欢迎大家转发分享。但未经授权，严禁任何媒体（平面媒体、网络媒体、自媒体等）以及微信公众号复制、转载、摘编或以其他方式进行使用。】

微信公众号

TechTarget微信公众号二维码

TechTarget

官方微博

TechTarget中国官方微博二维码

TechTarget中国

电子邮件地址不会被公开。 必填项已用*标注

敬请读者发表评论，本站保留删除与本文无关和不雅评论的权力。

自SQL Server 2000发布以来，处理XML数据就成为了数据库管理员们经常讨论的一个话题。常见的用法是前端应用程序以入参方式传给存储过程XML文档。然而，有时你会遇到要处理一个文件夹中一组XML文件，将它们加载到数据库中并随后处理到SQL Server数据表的情况。这一点更困难，而且介绍如何来做的文档资料也很少。

　　你可以在几种技术中做选择，但这些技术大部分都十分复杂。我曾用过SQL Server集成服务(SSIS)，数据转换服务(DTS)和自己编写的Windows应用程序来读取和加载文件。然而，我最喜欢的技术是采用OPENROWSET函数来实现它。该函数提供了极大的灵活性，因为你可以在T-SQL存储过程中从各个角度控制整个过程。

　　采用这种方法的第一步是创建一个只有一列的表，并把该列定义为XML数据类型。临时表也可以：

　　CREATE TABLE #WorkingTable

　　(Data XML)

　　然后，你可以使用OPENROWSET函数，把数据加载到这个表中的一行：

　　INSERT INTO #WorkingTable

　　SELECT * FROM OPENROWSET (BULK 'D:TempSample.xml', SINGLE_BLOB) AS data

　　现在，你可以把该表#WorkingTable数据列中的值放到一个变量里，然后调用OPENXML函数来解析XML文档，并根据需要使用这些XML数据。在本例中，XML文档内容非常简单，如下图：

　　OPENXML函数需要从临时表中读取这些数据，示例代码如下：

　　DECLARE @XML AS XML, @hDoc AS INT
　　SELECT @XML = Data FROM #WorkingTable
　　EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML
　　SELECT *
　　FROM OPENXML(@hDoc, '/root/data', 1)
　　WITH (Col1 VARCHAR(5) '@parm1',
　　Col2 VARCHAR(5) '@parm2')
　　EXEC sp_xml_removedocument @hDoc

　　这段查询的输出结果如下图所示：

　　这是所有OPENXML语句的基本语法。在这里，你可以给“root/data”段，“@parm1””和其他XPath表达式添加更复杂的XML内容。这样你可以处理更复杂的XML文档内容中的数据。下面就是一个XML内容更复杂一些的例子，其中列出了一些费用清单数据：

　　正如你所看到的，该XML文档有三级内容，需要的数据在“Customer ID”，“Order ID”和“Item ID”三个节点中。在示例代码中，你可以看到我们从“Item ID”节点开始往回解析，从而获取到“Customer ID”和“Order ID”的值：

　　DECLARE @XML AS XML, @hDoc AS INT
　　SELECT @XML = Data FROM #WorkingTable
　　EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML
　　SELECT *
　　FROM OPENXML(@hDoc, '/root/Customer/Order/Item')
　　WITH (CustomerId INT '../../@ID',
　　OrderId INT '../@ID',
　　ItemId INT '@ID',
　　Qty INT '@Qty')
　　EXEC sp_xml_removedocument @hDoc

　　执行后，会产生如下图所示的结果集：

　　如果我们使用同样的该XML文档，给第二个客户添加一个订单，我们就会看到如下图的输出内容：

　　正如我前面说的，我们从XML文档内容的“Item”节点开始处理。如果我们从文档的“Customer”节点开始，并向下解析该文档，我们得到的输出就是不正确的。那样的话，我们就只能得到文档中每一个“Customer ID”的第一个“item”。

　　OPENXML函数会得出不正确的结果集，如下图：

　　SELECT *
　　FROM OPENXML(@hDoc, '/root/Customer')
　　WITH (CustomerId INT '@ID',
　　OrderId INT 'Order/@ID',
　　ItemId INT 'Order/Item/@ID',
　　Qty INT 'Order/Item/@Qty')

　　在本文中，我们讨论了使用“XPath”最简单的例子。要了解更复杂的实例，你可以参考微软官方的“XPath语法”选项，来提高使用T-SQL语句XML引擎的能力。

作者

Denny Cherry
Denny Cherry

翻译

冯昀晖
冯昀晖

相关推荐