如何使用SQL Server 2000中的XML功能(5)

http://developer.51cto.com 2007-02-25 09:30 晓杜编译 IT168 我要评论(0)

摘要：在本文中我们将讨论如何通过T-SQL的FOR XML子句从SQL Server返回XML。本文将通过几个例子来介绍返回XML数据和架构信息的几种不同方式，还将介绍将XML转换成更令人满意的格式的方法。然后讨论OPENXML，以及将XML文档联接到数据库表和使用WriteXml和GetXml方法从数据集提取XML的方法。
标签：SQL Server XML

插入和更新

由SQL Server sp_xml_preparedocument系统存储过程准备的XML文档可像其他任何表一样用于JOIN中。它们也可像其他任何行集合一样用于INSERT、UPDATE或DELETE记录。为了证明这一点，首先从OrderID 10285选择一系列Order Details行，并将它们填入一个ADO.NET数据集。然后，通过更改它们的数量和向该数据集添加若干Order Details行，修改某些现有的Order Details行，如下面的代码片断所示：

作了这些更改之后，该数据集给出了关于如何取用该数据集中已更改的数据并将其转变为XML的若干个选项。可以使用WriteXml方法写出作为DiffGram的数据、带有或不带有其架构的数据。还可以使用GetXml方法将数据置入XML。本例将使用DiffGram，因为它将包含修改行以及各行在修改前后的状态（用于UPDATES）。

DiffGram被传递给一个存储过程（如图 8所示），该存储过程从XML文档取出新的和更新的行，并将它们插入一个TABLE变量(@tblTemp)。请注意，图 8中的OPENXML函数使用XPath表达式来获取hasChanges属性的值。对于插入行该值为i，对于修改行为m。由于OPENXML函数能够筛选出行，因此只需将一个XML文档传递给该存储过程。接下来，适当的行被插入Order Details表，然后适当的Order Details行被更新。INSERT和UPDATE都被包装在一个事务内部，因此无论哪一个失败都可以回滚。

图 8 使用DiffGram进行插入和更新

CREATE PROCEDURE prInsertUpdateOrderDetailsFromXML @sXML NTEXT
AS
DECLARE @iDoc int, @OrderID int
EXEC sp_xml_preparedocument @iDoc output, @sXML,
'<ns xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1"
xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"/>'
IF @@Error<>0
BEGIN
RETURN
END
BEGIN TRANSACTION
DECLARE @tblTemp TABLE (
OrderID INT,
ProductID INT,
UnitPrice MONEY,
Quantity SMALLINT,
Discount REAL,
ChangeType CHAR(1)
)
INSERT INTO @tblTemp
(OrderID, ProductID, UnitPrice, Quantity, Discount, ChangeType)
SELECT OrderID, ProductID, ProductPrice,
ProductQTY, Discount, ChangeType
FROM OpenXML(@iDoc,
'/diffgr:diffgram/NorthwindOrderDetailsData/OrderDetails', 2)
WITH (OrderID INT 'OrderID', ProductID INT 'ProductID',
ProductPrice MONEY 'UnitPrice',
ProductQTY SMALLINT 'Quantity', Discount REAL 'Discount',
ChangeType CHAR(1) '@diffgr:hasChanges')
IF @@Error<>0
BEGIN
EXEC sp_xml_removedocument @iDoc
ROLLBACK TRANSACTION
RETURN
END
EXEC sp_xml_removedocument @iDoc
INSERT INTO [Order Details]
(OrderID, ProductID, UnitPrice, Quantity, Discount)
SELECT OrderID, ProductID, UnitPrice, Quantity, Discount
FROM @tblTemp
WHERE ChangeType = 'i'
IF @@Error<>0
BEGIN
ROLLBACK TRANSACTION
RETURN
END
UPDATE [Order Details]
SET
UnitPrice = t.UnitPrice,
Quantity = t.Quantity,
Discount = t.Discount
FROM
@tblTemp t, [Order Details] od
WHERE
t.ChangeType = 'm'
AND od.OrderID = t.OrderID
AND od.ProductID = t.ProductID
IF @@Error<>0
BEGIN
ROLLBACK TRANSACTION
RETURN
END
COMMIT TRANSACTION
GO
EXEC sp_xml_removedocument @iDoc
INSERT INTO [Order Details]
(OrderID, ProductID, UnitPrice, Quantity, Discount)
SELECT OrderID, ProductID, UnitPrice, Quantity, Discount
FROM @tblTemp
WHERE ChangeType = 'i'
IF @@Error<>0
BEGIN
ROLLBACK TRANSACTION
RETURN
END
UPDATE [Order Details]
SET
UnitPrice = t.UnitPrice,
Quantity = t.Quantity,
Discount = t.Discount
FROM
@tblTemp t, [Order Details] od
WHERE
t.ChangeType = 'm'
AND od.OrderID = t.OrderID
AND od.ProductID = t.ProductID
IF @@Error<>0
BEGIN
ROLLBACK TRANSACTION
RETURN
END
COMMIT TRANSACTION
GO

通过OPENXML批量插入

通过DataAdapter和存储过程从数据集插入10行非常容易。但DataAdapter会一次一个地遍历数据集行来查找行状态为已插入的行，并执行与DataAdapter针对每一行的InsertCommand关联的存储过程。这意味着10次插入将导致对数据库的10次调用。当必要的更新较少时，这种ADO.NET代码和数据库之间的来回操作几乎不会引起什么明显变化。不过，当引入其他因素时，如并发用户数量大大增加或插入数百行时，性能可能会迅速降低。要插入100行，不必调用100次存储过程，而可以将这100行作为XML一次性传递到一个存储过程中。

与可下载的代码一起提供的示例ASP.NET应用程序将执行一个将n个客户插入到Customers表中的存储过程。ASP.NET代码在数据集中创建并添加100条客户记录。然后使用WriteXml方法输出XML数据，不包含其架构。该XML然后被传递给一个一次性插入100个客户的存储过程。与每插入一次便执行一个存储过程的一次一个地插入各个客户相比，这种插入批量数据的方法更为高效。

使用示例

若使用SQL Query Analyzer来测试该列中包含的FOR XML SQL，可能需要更改某些默认选项。例如，若要查询和返回XML，则要先增加结果窗格中每列的最大字符数。转到Options | Tools | Results，将Maximum Characters per Column设置增加到某一值，如4096。由于返回的XML在结果窗格中显示为单列，因此该XML不会在默认的256个字符处截止。最后，用户将能够看到该XML，如果愿意，还可以将它复制并粘贴到XML编辑器中。

结束语

XML和SQL Server集成的第一步是引入FOR XML和OPENXML功能。由于这种进步，我们能够直接从SQL Server数据库获取XML，而不必使用某些中间组件对其进行转换。我们还可以通过结合使用OPENXML函数和老式备用方法、SQL INSERT、UPDATE和DELETE语句，将XML返给数据库。这些XML功能只是SQL Server 2005将提供的XML收集支持中的第一步，但它们如今已可用于集成XML应用程序和SQL Server，是一些非常有效的工具。

（责任编辑火凤凰 sunsj@51cto.com QQ：34067741 TEL：（010）68476636-8007）

共5页: 上一页 [1] [2] [3] [4] 5

【内容导航】

上一篇：当XML走进关系型数据库下一篇： 2007年XML的十大预测