SQL 存储过程解析XML

第一种说明：

我看过这样一篇文章，如下
在SQL Server2005中，微软延续了 2000中一个特性（即支持XML类型的数据），并加强了对XML 数据列、XML变量以及XML索引的支持。
在数据库中存储XML数据是一个很出色的特征。对于大部分的数据处理需求来讲，将XML数据格式化为其它关联数据是十分重要。这也是引入OPENXML函数的原因。OPENXML是一个SQL Server提供的函数，它的作用是接收XML数据，提供内存中 XML数据的行集视图。
一个OPENXML函数用例
在下面的例子中，我们将演示如何使用OPENXML函数。假定我们现在正在从事一个网上购物系统开发。在这个场景中，顾客选择登录网站方式购买多种产品。因为根据公司的商业模式，通过网站购物能够节省成本。我们假定大部分的顾客都购买了七件或者更多的商品。我们的目标是当客户频繁的访问网站的时候，要尽量减少数据库的调用。我们的想法是将网页上提交的采购需求存储为XML文档，并且将其中的XML数据以字符串的形式传送给数据库底层的程序处理。这样，我们就可以在一次数据库调用中将XML数据中的订单信息插入数据库中。
我们该如何解决上面的问题呢？首先我们看看将要编辑的网页数据。一个简单的XML文档含有来自网站的很多核心信息，包括：产品名称、价格、日期以及顾客资料。列表A包含了一个简单的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>

列表A

然后，我们需要设计一个网页接口提供存储程序的调用。通过调用这一存储程序，将XML数据流存储至数据库。列表B是这一存储程序的实现内容。

CREATE   PROCEDURE   usp_InsertShoppingCartOrder   
(   
      @xml   XML   
)   
AS   
BEGIN   
            DECLARE   @Pointer   INT   
            EXECUTE   sp_xml_preparedocument   @Pointer   OUTPUT,@xml   

            INSERT   INTO   WebSales 
            ( 
                        ProductID,   
                        SalePrice,   
                        SaleDate, 
                        SaleBatchID, 
                        CustomerID 
            ) 
            SELECT   
                        ProductID, 
                        Price, 
                        SaleDate, 
                        SaleBatchID, 
                        CustomerID 
            FROM   
            OPENXML   (@Pointer, '/ShoppingCart/Purchase ')   
            WITH   
            (   
                        ProductID   INT,   
                        Price   MONEY,   
                        SaleDate   SMALLDATETIME,   
                        SaleBatchID   INT,   
                        CustomerID   INT   
            )   

            EXEC   sp_xml_removedocument   @Pointer   
END

列表B

这一存储程序支持XML数据类型（SQL Server 2005中新特色）作为输入参数（我们也可以使用变量字符数据类型作为我们的输入参数，例如VARCHAR (max)或者定义VARCHAR数据类型）。
接下来，我们调用系统程序sp_xml_preparedocument，它不仅创建了XML文档在内存中的表示，也允许XML文档作为调用参数。一旦我们拥有了指向内存中XML文档的句柄，就可以调用OPENXML函数。在函数调用中使用不同的参数，还可以对XML数据的返回结果集进行详细的控制。
我们提供指向XML文档的句柄，并且通过XPATH查询返回想要的XML数据中的节点。OPENXML函数中的WITH子句允许指定返回数据的行集格式。一旦将数据插入到websales 表格中，就可以调用系统程序sp_xml_removedocument删除SQL Server 服务器内存中的XML数据。

这个简单的例子演示了通过OPENXML函数来减少数据库调用次数的强大功能。OPENXML函数提供很大的灵活性，你既可以将所有的XML数据插入到 SQL Server 数据表中，也可以拆分XML文件，将其插入到不同的SQL Server数据表中。通过这一技术，我们就可以在一次程序调用中插入8条记录，而不是像传统的技术那样，要通过8次操作实现。
OPENXML的局限性
调用OPENXML函数时要注意内存的使用情况。系统程序sp_xml_preparedocument 的返回值是指向内存中XML文档的句柄。所以，你最好不要使用OPENXML加载较大的XML文档，因为那样可能会造成服务器的内存溢出。
在以后的文章中，我将会向你介绍如何使用SQL Server 2005提供的关于XML的一些新属性，这些新属性不仅能够实现OPENXML函数的功能，而且不需要考虑OPENXML调用时产生的内存操作问题。
作者简介
Tim Chapman先生在一家位于Louisville, KY的银行从事数据库管理员的工作，拥有7年的IT工作经验。他还通过了微软的SQL Server 2000和SQL Server 2005认证。

第二种说明：
创建几个带不同参数的存储过程去完成同一样任务对我们来说一直以来都是一个负担。通过使用XML字符串传递参数给你的存储过程可以简化这个任务，从而使设计COM组件变得更加简单。
达到这个目标的方法是将你的参数作为一个XML字符串传入，然后解析XML以取得你需要的数据，最后继续完成你需要实现的功能。你不但可以从XML中获得参数，你还可以在由XML创建的DOM文档运行查询语句，完成几个存储过程调用。我将给出一些例子来说明怎样做，每个例子都有一个简单的解释。

在这个例子中，我将传递一些参数用以更新Customer表的name域。XML被解析以获得customerid（标识列）和新的name。我传给过程的XML字符串如下：

<root>
    <Customer>
        <customerid>3</customerid>
        <name>Acme Inc.</name>
    </Customer>
</root>

创建的存储过程如下：

CREATE PROCEDURE update_Customer (@xmldata varchar(8000)) AS 
DECLARE @customerid int 
DECLARE @customername varchar(50) 
DECLARE @xmldata_id int 

EXEC sp_xml_preparedocument @xmldata_id OUTPUT, @xmldata, '' 

SELECT @customerid = customerid, @customername = [name] FROM 
OPENXML(@xmldata_id, '//Customer', 2) WITH (customerid int, [name] 
varchar(50)) 

EXEC sp_xml_removedocument @xmldata_id 

UPDATE Customer SET Customer.[name] = ISNULL(@customername, Customer.[name]) 
WHERE Customer.tblID = @customerid

这个过程首先声明我们用于存储相关信息的变量。之后，打开DOM文档，调用过程sp_xml_preparedocument，其第一个参数返回一个“句柄”。

这个过程调用的第二个参数是新DOM文档的XML源。第一个参数返回的“句柄”在OPENXML调用中用于查询DOM文档。OPENXML调用的第二个参数是一个映射到包含要提取数据的父节点的扩展路径。
第三个参数（2）指明使用以元素为中心的映射。WITH子句为被解析的数据提供行集合格式，然后sp_xml_removedocument调用删除DOM文档资源。

在下面的另一个例子中，我将会根据传入的一组customer ID删除几行数据。使用的XML字符串如下：

<root>
    <Customer>
        <customerid>1</customerid>
    </Customer>
    <Customer>
        <customerid>2</customerid>
    </Customer>
    <Customer>
        <customerid>3</customerid>
    </Customer> 
</root>

使用的存储过程如下：

EXEC sp_xml_preparedocument @xml_id OUTPUT, @xmldata, '' 
DELETE FROM Customer WHERE Customer.tblID IN (SELECT customerid FROM 
OPENXML (@xmldata_id, '//Customer', 2) WITH (customerid int))

这个存储过程的使用避免了建立一个长SQL查询字符串来通过ADO传递，或者多次调用同一个存储过程。也避免了由于多次调用引起的网络拥挤。

正如你看到的，使用Microsoft SQL Server 2000可以使一些事情变得比较简单。但记住是当你在SQL Server 2000中使用XML时，作为参数传入的XML不能超过8000个字符。就像常说的，不要过高的看待一件好事的好处。
参考MSDN库取得更多关于OPENXML, sp_xml_preparedocument 和 sp_xml_removedocument 的信息。

posted @ 2022-11-14 20:17 yclizq 阅读(350) 评论(0) 编辑收藏举报

刷新页面返回顶部

SQL 存储过程 解析XML

公告

SQL 存储过程解析XML