如何在SQL Server 2005中用XQuery分解XML数据(转)

如何在SQL Server 2005中用XQuery分解XML数据

上周，我们简单讨论了如何应用SQL Server中的OPENXML函数将XML数据分解到个一个关系格式中。我们了解到OPENXML使用起来简单直接，但非常占用内存。本周，我们讨论SQL Server 2005的新功能，它允许你将XML数据分解到关系格式中，而不必耗用太多内存。
在举例说明如何分解上一篇文章中的数据时，我们首先了解一下XQuery和它在SQL Server 2005中为开发者提供的功能。
XQuery介绍
XQuery，也称作XML Query，是一种查询XML数据的语言，允许你提取所需的节点和元素。它由W3C定义，可用于今天的大多数主流数据库引擎中，如Oracle、DB2和SQL Server。
SQL Server 2005 XQuery函数
下面的四个函数是SQL Server 2005中的XQuery函数。（注意，XML、XQuery语句和下面的函数都区分大小写。例如，SQL编译器接受XML数据中的.exist，但拒绝.EXIST或.Exist。）
xml.exist
这个方法根据一个XML节点上的搜索表达式返回一个布尔值。例如，列表A中XML代码片段中的语句将返回1（真）：
列表A
[code]DECLARE @x XML

SET @x = '<christmaslist><person name = "betty" gift = "camera"/><person name = "zach" gift = "elmo doll"/><person name = "brad" gift = "socks"/></christmaslist>'[/code]
SELECT @x.exist('/christmaslist/person[@gift = "socks"]')
这个语句返回0（假）：
SELECT @x. exist ('/christmaslist/zach')
由于“Socks”一词被封套，这个语句将返回0（假）。
SELECT @x.exist('/christmaslist/person[@gift = "socks"]')
xml.value
这个方法接受一个XQuery语句并返回一个单独值。使用列表A中同样的XML代码片段，不可以使用VALUE函数生成“betty”值，如下所示：
SELECT @x.value('/christmaslist[1]/person[1]/@name', 'VARCHAR(20)')
而XQuery生成“zach”值。
SELECT @x.value('/christmaslist[1]/person[2]/@name', 'VARCHAR(20)')
xml.query
这个方法接受一个XQuery并返回一个XML数据类型的实例。可以按需要将这些查询简单或复杂化，下面是一个简单的例子：
SELECT @x.query('/christmaslist/person')
它返回XML文件：
<person name="betty" gift="camera" />
<person name="zach" gift="elmo doll" />
<person name="brad" gift="socks" />
xml.nodes
在你需要将一个XML数据类型变量中的数据分解到关系数据中时，这个方法十分有用。这个方法接受一个XQuery语句作为参数，并返回一个包含XML变量逻辑标量数据的行集。列表B中的查询利用上面定义的XML变量，并将数据分解到一个结果集中，它显示在XML变量中定义的人物姓名。
列表B

复制内容到剪贴板

代码:

SELECT
      Table1.Column1.value('@name',VARCHAR(20)')
FROM   @x.nodes('/christmaslist/person') as Table1(Column1)

修改OPENXML存储过程
现在我来说明如何修改上周的OPENXML存储过程，使其可以应用XQuery功能。首先，我往XML变量中加载一些数据。如列表C所示。我们可以建立一个接受XML参数的过程，再应用XQuery函数把XML文件中的数据插入一个表中，而不必应用OPENXML。如列表D所示。
列表C

复制内容到剪贴板

代码:

DECLARE @xml XML

SET @xml = '

<ShoppingCart>

<Purchase ProductID="7" Price="10.00" SaleDate="10/11/2006" SaleBatchID = "4523" CustomerID = "2398"/>
<Purchase ProductID="99" Price="25.00" SaleDate="10/11/2006" SaleBatchID = "4523" CustomerID = "2398"/>

<Purchase ProductID="32" Price="12.00" SaleDate="10/11/2006" SaleBatchID = "4523" CustomerID = "2398"/>

<Purchase ProductID="11" Price="90.00" SaleDate="10/11/2006" SaleBatchID = "4523" CustomerID = "2398"/>

<Purchase ProductID="7" Price="50.00" SaleDate="10/11/2006" SaleBatchID = "4523" CustomerID = "2398"/>

<Purchase ProductID="8" Price="67.35" SaleDate="10/11/2006" SaleBatchID = "4523" CustomerID = "2398"/>

<Purchase ProductID="45" Price="29.99" SaleDate="10/11/2006" SaleBatchID = "4523" CustomerID = "2398"/>

<Purchase ProductID="54" Price="49.49" SaleDate="10/11/2006" SaleBatchID = "4523" CustomerID = "2398"/>

</ShoppingCart>'

列表D

复制内容到剪贴板

代码:

CREATE PROCEDURE usp_InsertShoppingCartOrder
(   
       @xml XML
)
AS
BEGIN      

      INSERT INTO WebSales      
      (            
            ProductID,            
            SalePrice,            
            SaleDate,            
            SaleBatchID,            
            CustomerID      
      )      
      SELECT
            Table1.Column1.value('@ProductID', 'INT'),
            Table1.Column1.value('@Price', 'FLOAT'),
            Table1.Column1.value('@SaleDate', 'DATETIME'),
            Table1.Column1.value('@SaleBatchID', 'INT'),
            Table1.Column1.value('@CustomerID', 'INT')
      FROM
            @xml.nodes('/ShoppingCart/Purchase') AS Table1(Column1)

END

最初在数据库中应用XML似乎有些难于处理，还要花一些时间习惯使用XQuery和Xpath查询。但是，经过一段时间的学习以后，你就会发现在数据库中应用XML数据相当实用。
例如，在上述存储过程中应用XML数据，你只需调用一次数据库，而不必像典型存储过程编程那样调用N次数据库。这二者似乎区别不大，但对一个繁忙的系统而言，应用XML数据会有很大益处。而且，应用XQuery而非OPENXML还会显著提高性能，对小型XML文件更是如此。
Tim Chapman是肯塔基州路易维尔市一家银行的SQL Server数据库管理员，他有超过7年的IT行业经验。他还通过了微软SQL Server 2000和SQL Server 2005的认证。