SQL Server 2000提供了一些XML功能，用于通過XML將關系行集合轉換成分層的XML文檔、讀取XML文檔和批量加載數據。例如，可以將XML文檔傳遞到存儲過程，將XML聯接到某些表并返回一個行集合，甚至可以在數據庫中修改數據。XML在當今企業系統中不斷擴展的功能促進了OPENXML函數和FOR XML語句的引入。其中某些功能不但支持XML，而且還提高批量加載數據時的性能。

在本文中我們將討論如何通過T-SQL的FOR XML子句從SQL Server返回XML。本文將通過幾個例子來介紹返回XML數據和架構信息的幾種不同方式，還將介紹將XML轉換成更令人滿意的格式的方法。然后討論OPENXML，以及將XML文檔聯接到數據庫表和使用WriteXml和GetXml方法從數據集提取XML的方法。這些例子的SQL，以及執行其中某些例子并將它們導出為文本文件的示例ASP.NET 項目，都可從MSDN Magazine Web站點下載。該示例項目中還包含了用于從XML將記錄插入和更新到數據庫的代碼。

返回XML

當用于SELECT語句中時，FOR XML子句指示SQL Server將數據作為XML返回，這與標準行集合相反。可以指定返回模式：RAW、AUTO或EXPLICIT。每種模式都提供了XML的不同轉換方式（圖 1 給出了各種模式的概述）。



圖 1 FOR XML模式概述

例如，若使用FOR XML RAW來查詢Northwind數據庫的Employees表，它會在<row>元素中返回每個員工行。SELECT語句中包含的每一列都會表示為<row>元素的一個屬性。下面的FOR XML RAW查詢選擇兩個員工記錄，然后以RAW格式返回：

SELECT EmployeeID,

FirstName, LastName

FROM Employees

WHERE LastName LIKE 'D%'

FOR XML RAW
<row EmployeeID="1" FirstName="Nancy" LastName="Davolio"/>

<row EmployeeID="9" FirstName="Anne" LastName="Dodsworth"/>

對該SELECT語句作一下修改就可以使用FOR XML AUTO子句。這次將元素命名為Employees，與源表的名稱匹配。列仍是主元素的屬性：

SELECT EmployeeID, FirstName, LastName

FROM Employees

WHERE LastName LIKE 'D%'

FOR XML AUTO
<Employees EmployeeID="1" FirstName="Nancy" LastName="Davolio"/>

<Employees EmployeeID="9" FirstName="Anne" LastName="Dodsworth"/>

轉換和層次結構



盡管上例中的區別較小，但與用于聯接表的一個查詢一起使用時，AUTO和RAW之間的區別比較明顯。無論數據是來自一個表還是來自多個表，使用FOR XML RAW的查詢都將只返回<row>元素。因此，RAW模式不利用XML文檔的固有分層結構。請看下面的SQL語句：

SELECT   Customers.CustomerID,

CompanyName,

OrderID,

CONVERT(VARCHAR(10), OrderDate, 101) AS OrderDate

FROM     Customers

INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID

ORDER BY Customers.CustomerID

本例將檢索一種一對多父子關系。若執行該SQL語句，則將返回一系列客戶及其相應定單。若附帶FOR XML RAW子句并再次執行，則得出的XML結果將包含一個表示所返回的每一行的單<row>元素。例如，圖 2中的XML數據表示CustomerID為ALFKI時FOR XML RAW將返回的行。

<row CustomerID="ALFKI" CompanyName="Alfreds Futterkiste" OrderID="10643" 
OrderDate="08/25/1997" />

<row CustomerID="ALFKI" CompanyName="Alfreds Futterkiste" OrderID="10692" 
OrderDate="10/03/1997" />

<row CustomerID="ALFKI" CompanyName="Alfreds Futterkiste" OrderID="10702" 
OrderDate="10/13/1997" />

<row CustomerID="ALFKI" CompanyName="Alfreds Futterkiste" OrderID="10835" 
OrderDate="01/15/1998" />

<row CustomerID="ALFKI" CompanyName="Alfreds Futterkiste" OrderID="10952" 
OrderDate="03/16/1998" />

<row CustomerID="ALFKI" CompanyName="Alfreds Futterkiste" OrderID="11011" 
OrderDate="04/09/1998" />

圖 2 XML數據

請注意，這些數據不以父子層次結構顯示。若想讓數據顯示為包含一系列相關<Orders>元素的一系列<Customers>元素，則可使用FOR XML AUTO子句（父子嵌套方法取決于成組聚集的父行）。CustomerID為ALFKI 時的XML結果如下：

<Customers CustomerID="ALFKI" CompanyName="Alfreds Futterkiste">

<Orders OrderID="10643" OrderDate="08/25/1997" />

<Orders OrderID="10692" OrderDate="10/03/1997" />

<Orders OrderID="10702" OrderDate="10/13/1997" />

<Orders OrderID="10835" OrderDate="01/15/1998" />

<Orders OrderID="10952" OrderDate="03/16/1998" />

<Orders OrderID="11011" OrderDate="04/09/1998" />

</Customers>

該XML的可讀性大大提高，因為它使用表名來作為元素名。它所包含的數據也比較少，因為它不重復每個定單元素的CustomerID和CompanyName屬性，而FOR XML RAW例子則不然。

如果您更喜歡將列值表示為元素而不是屬性，則您就會很幸運。通過在FOR XML子句中指定ELEMENTS選項，所有列值都將成為XML中的元素。有時百聞不如一見，因此本文在圖 3中給出了當ELEMENTS條件適用時前面查詢的輸出。（為了簡便起見，給出的XML示例僅包含CustomerID ALFKI的XML。這些查詢所生成的實際XML會包含所有客戶及其定單的XML。）使用ELEMENTS選項的查詢如下：

SELECT   Customers.CustomerID,

CompanyName,

OrderID,

CONVERT(VARCHAR(10), OrderDate, 101) AS OrderDate

FROM     Customers

INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID

ORDER BY Customers.CustomerID

FOR XML AUTO, ELEMENTS
<Customers>

<CustomerID>ALFKI</CustomerID>

<CompanyName>Alfreds Futterkiste</CompanyName>

<Orders>

<OrderID>10643</OrderID>

<OrderDate>08/25/1997</OrderDate>

</Orders>

<Orders>

<OrderID>10692</OrderID>

<OrderDate>10/03/1997</OrderDate>

</Orders>

<Orders>

<OrderID>10702</OrderID>

<OrderDate>10/13/1997</OrderDate>

</Orders>

<Orders>

<OrderID>10835</OrderID>

<OrderDate>01/15/1998</OrderDate>

</Orders>

<Orders>

<OrderID>10952</OrderID>

<OrderDate>03/16/1998</OrderDate>

</Orders>

<Orders>

<OrderID>11011</OrderID>

<OrderDate>04/09/1998</OrderDate>

</Orders>

</Customers>

圖 3 使用FOR XML AUTO, ELEMENTS

該XML的可讀性大大提高，因為它使用表名來作為元素名。它所包含的數據也比較少，因為它不重復每個定單元素的CustomerID和CompanyName屬性，而FOR XML RAW例子則不然。 如果您更喜歡將列值表示為元素而不是屬性，則您就會很幸運。通過在FOR XML子句中指定ELEMENTS選項，所有列值都將成為XML中的元素。有時百聞不如一見，因此本文在圖 3中給出了當ELEMENTS條件適用時前面查詢的輸出。（為了簡便起見，給出的XML示例僅包含CustomerID ALFKI的XML。這些查詢所生成的實際XML會包含所有客戶及其定單的XML。）使用ELEMENTS選項的查詢如下：

該XML的可讀性大大提高，因為它使用表名來作為元素名。它所包含的數據也比較少，因為它不重復每個定單元素的CustomerID和CompanyName屬性，而FOR XML RAW例子則不然。 如果您更喜歡將列值表示為元素而不是屬性，則您就會很幸運。通過在FOR XML子句中指定ELEMENTS選項，所有列值都將成為XML中的元素。有時百聞不如一見，因此本文在圖 3中給出了當ELEMENTS條件適用時前面查詢的輸出。（為了簡便起見，給出的XML示例僅包含CustomerID ALFKI的XML。這些查詢所生成的實際XML會包含所有客戶及其定單的XML。）使用ELEMENTS選項的查詢如下：

該XML的可讀性大大提高，因為它使用表名來作為元素名。它所包含的數據也比較少，因為它不重復每個定單元素的CustomerID和CompanyName屬性，而FOR XML RAW例子則不然。 如果您更喜歡將列值表示為元素而不是屬性，則您就會很幸運。通過在FOR XML子句中指定ELEMENTS選項，所有列值都將成為XML中的元素。有時百聞不如一見，因此本文在圖 3中給出了當ELEMENTS條件適用時前面查詢的輸出。（為了簡便起見，給出的XML示例僅包含CustomerID ALFKI的XML。這些查詢所生成的實際XML會包含所有客戶及其定單的XML。）使用ELEMENTS選項的查詢如下：

通過ADO.NET返回XML



在提供的可下載的示例代碼中還包含了一個用于運行某些代碼示例（如圖 4所示）的ASP.NET項目。該項目示例使用一個叫做GetFORXML的方法，該方法運行任何SQL時都附帶一個FOR XML子句，并將得到的XML寫到一個文件。為簡單起見，該XML還被包裝在一個<root>元素中。

498)this.style.width=498;" border=0>



圖 5所示的GetFORXML方法代碼說明了如何使用標準SqlCommand對象來執行包含FOR XML子句的SQL語句。這里使用了ExecuteXmlReader方法，因此可以將XML作為一個XmlReader對象返回。數據集的ReadXml方法接受XmlReader，其第二個參數表明該XML是一個片段。

private string GetFORXML(string sFile, string sSQL) {

// Create和open the connection to Northwind

using(SqlConnection oCn = new SqlConnection(this.sCnNW)) {

oCn.Open();
// Create the SQL command to execute

SqlCommand oCmd = new SqlCommand(sSQL, oCn);
DataSet oDs = new DataSet();
// Execute the SQL statement and return the data to an XmlReader.

// Then read the schema and the fragment

XmlReader oXml = oCmd.ExecuteXmlReader();

oDs.ReadXmlSchema(oXml);

oDs.ReadXml(oXml, XmlReadMode.Fragment);
oCn.Close();
oDs.DataSetName = "root";

oDs.WriteXml(this.sPath + sFile);

return "Wrote XML to file " + sFile;

}

}

圖 5 使用XmlTextReader遍歷XML



因為它是一個片段，所以如果它有一個用于匹配的架構，則只加載所有的Customer節點。因此在加載XML之前，先使用ReadXmlSchema方法加載該架構（在這種情況下需要推理）。最后，將該數據集的DataSetName屬性設置為“root”，這樣將得到封閉在一個父標記<root/>中的讀XML。

圖 4中的ASP.NET頁能夠執行和輸出前面已經研究過的例子的XML，也能夠使用FOR XML RAW, BINARY BASE64獲取員工數據 。此外，通過使用以下子句，它還能夠執行和輸出XML，獲取客戶和定單數據：

 FOR XML RAW 

 FOR XML AUTO 

 FOR XML AUTO, ELEMENTS 

 FOR XML AUTO, ELEMENTS, XMLDATA 

使用FOR XML限制

FOR XML AUTO和FOR XML RAW 對返回二進制數據都不提供完全支持。例如，若選擇一個二進制字段（比如 Employees.Photo列）并使用FOR XML RAW，則會收到一條難以處理的錯誤消息。避免這種情況的一種方法是將一個URL返給二進制數據字段。這就需要首先在IIS中建立一個SQL Server虛擬目錄。另一種方法不需要進行IIS配置，它是在FOR XML子句中指定BINARY BASE64選項，如以下代碼所示：

SELECT EmployeeID, FirstName, LastName, Photo 

FROM Employees 

WHERE LastName LIKE 'D%' 

FOR XML RAW,BINARY BASE64 

這樣SQL Server就會使用BASE64格式來格式化二進制數據。其優點是SQL語句將不再改變。缺點是讀取使用BINARY BASE64的查詢結果不方便。下面的XML是使用BINARY BASE64時所返回的結果的一個例子。這里削減了Photo屬性的內容，因為其長度超過了14,000個字符！

<row EmployeeID="1" FirstName="Nancy" 

LastName="Davolio" Photo="FRw...f4="/> 

FOR XML AUTO模式的另一個限制是它不支持GROUP BY子句或聚合函數。不過，可以通過選擇若干行、構成TABLE變量，然后使用FOR XML AUTO子句從該中間表中檢索這些行來避開這種限制。下面的SQL代碼即可完成這一任務：

DECLARE @TempTable TABLE (OrderID INT, Total MONEY) 
INSERT INTO @TempTable 

SELECT OrderID, SUM(UnitPrice * Quantity) AS Total 

FROM [Order Details] 

GROUP BY OrderID 
SELECT OrderID, Total FROM @TempTable AS OrderDetails FOR XML AUTO 

使用FOR XML時，還必須說明計算列。盡管FOR XML支持計算列，但仍需確保對這些列進行命名。當FOR XML試圖創建每一列的屬性時，就會出現問題。屬性名是通過使用關聯的列名創建的。因此如果該列是一個計算列且沒有別名，則SQL Server將停滯不前。簡單的辦法是確保給計算列取個別名，如下面的SQL語句及其XML結果所示：

SELECT TOP 1 LastName + ', ' + FirstName AS FullName 

FROM Employees 

FOR XML AUTO 
<Employees FullName="Davolio, Nancy"/> 

使用FOR XML時值得注意的最后一個問題是任何專用的XML字符都將通過使用XML編碼來轉換。正如HTML可以轉換URL中的專用字符一樣，適當形式的XML可以編碼專用字符。例如，如果<字符包含在數據中，則它將轉換成“&lt;”。

OPENXML



到此為止我們已經探討了從SQL Server獲取XML的方法。下一步將是在數據庫中使用XML來修改數據。輸入OPENXML函數。T-SQL OPENXML函數能夠從XML流構建關系行集合。該行集合可以執行像表一樣的操作，因此可用于可能與其他表聯接的其他SQL語句中，甚至可用于插入或更新數據。這可以提供很大方便，因為這樣可以將包含需要在數據庫中修改的新的或更新的記錄XML流傳遞給某一應用程序。OPENXML函數使用某種形式的Xpath，以便用戶能夠告訴它在何處搜索想要取出的數據。

為此，首先取一個包含客戶數據的XML文檔，并將其插入到Northwind數據庫的Customers表中。該XML文檔可能使用元素或屬性來表示該客戶的數據，如下所示：

<root>

<customer>

<custid>77777</custid>

<custname>fake customer</custname>

<city>Somewhere</city>

<country>USA</country>

</customer>

</root> 

OPENXML語句允許用戶使用XPath表達式來深入了解XML文檔，這意味著XML的格式可以相當靈活。

下面創建了一個存儲過程（如圖 6所示），用于接受該XML文檔，為關系行集合準備該XML文檔，讀取客戶數據，將其插入到Customers表，然后從內存刪除該XML文檔?，F在我們來看這一切是如何實現的。首先，可以在XML中作為任意字符串類型（如VARCHAR（8000）或大對象類型如TEXT或NTEXT）的變量傳遞。我們使用NTEXT，以便不受VARCHAR的8,000字符限制。NTEXT的最大長度為230 - 1（1,073,741,823）個字符。（當然，在SQL Server 2005中還可以使用XML數據類型。）

圖 6 通過OPENXML插入Customer

CREATE PROCEDURE prInsertCustomerFromXML (

@sXML NTEXT

)

AS

DECLARE @iDoc INT

EXEC sp_xml_preparedocument@iDoc OUTPUT, @sXML
INSERT INTO Customer(CustomerID, CompanyName, City, Country)

SELECT CustomerID, CompanyName, City, Country

FROM OPENXML(@iDoc,'/root/customer',2)

WITH (CustomerID NCHAR(5) 'custid',

CompanyName NVARCHAR(40) 'custname',

City NVARCHAR(15) 'city',

Country NVARCHAR(15) 'country')
EXEC sp_xml_removedocument@iDoc
RETURN 

對XML所做的第一件事是將其傳遞給sp_xml_preparedocument系統存儲過程。該過程取用該XML并將其轉換成內部DOM，以便OPENXML能夠將它作為行集合來處理。該過程還在內存中創建一個DOM引用（圖 6中的@iDoc變量）。通過將@iDoc變量傳遞給OPENXML函數，可以在SELECT語句的FROM子句中將XML作為行集合來訪問。

使用完XML后，應使用sp_xml_removedocument系統存儲過程從內存中刪除之。

圖 6中的代碼核心是OPENXML函數，它對該XML文檔的內存表達式執行操作。OPENXML方法將該XML文檔的引用作為其第一個參數來接受。其第二個參數用于告訴OPENXML用戶想映射到行的XML DOM中的節點。在這個例子中，我們想識別Customers節點以獲取該客戶的數據值，因此我們將“/root/customer”指定為第二個參數。OPENXML函數的第三個參數是用于指示要使用的映射類型。取值為1時，OPENXML映射到屬性，取值為2時則映射到元素。

WITH子句可用于指定要從該XML文檔獲取的字段以及要轉換成的數據類型。WITH子句也可用于在XML中用XPath表達式來映射屬性或元素，或用于為要用于某一查詢的XML字段取別名。圖 6中的OPENXML代碼主要是從XML數據中取4個客戶字段，并將其轉換成行集合。然后可以從Customers表選擇該行集合，或插入到其中。

通過OPENXML插入



現在我們將用下面的XML示例從中插入一個定單和兩個定單細節行：

<Customer CustomerID='ALFKI'>

<Order OrderDate='1/1/1972' Freight='3'>

<Detail ProductID='1' Price='4' Quantity='10' Discount='0'/>

<Detail ProductID='2' Price='5' Quantity='2' Discount='0'/>

</Order>

</Customer> 

首先，將該XML傳遞給圖 7所示的存儲過程，并使用sp_xml_preparedocument系統存儲過程準備之。然后啟動一個事務來包裝INSERT語句，以便插入一個定單及其子記錄。這樣便可以在部分事務失敗時回滾事務。接下來，使用在Customer\Order節點開始的OPENXML打開XML文檔。利用WITH子句，將XML文檔返回到Order元素（Customer 元素）的父節點，然后查看Customer元素的CustomerID屬性值，從而獲得CustomerID。這是OPENXML函數的一個重要功能，因為它讓用戶使用受限的XPath表達式來遍歷XML文檔，從而獲得屬性和元素值。

圖 7 插入父定單及其子定單

CREATE PROCEDURE prInsertOrderAndOrderDetailsFromXML @sXML NTEXT

AS

DECLARE @iDoc int, @OrderID int
EXEC sp_xml_preparedocument @iDoc output, @sXML

IF @@Error<>0

BEGIN

RETURN

END
BEGIN TRANSACTION

INSERT INTO Orders (CustomerID , OrderDate, Freight)

SELECT CustomerID, CAST(OrderDate As datetime), CAST(Freight AS money)

FROM OpenXML(@iDoc,'/Customer/Order', 1)

WITH (CustomerID nchar(5) '../@CustomerID' ,

OrderDate varchar(10) '@OrderDate',

Freight varchar(12) '@Freight')
IF @@Error<>0

BEGIN

EXEC sp_xml_removedocument @iDoc

ROLLBACK TRANSACTION

RETURN

END
SET @OrderID=SCOPE_IDENTITY()
INSERT INTO [Order Details]

(OrderID, ProductID, UnitPrice, Quantity, Discount)

SELECT @OrderID, ProductID, ProductPrice, ProductQTY, Discount

FROM OpenXML(@iDoc,'/Customer/Order/Detail')

WITH (ProductID int '@ProductID', ProductPrice money '@Price',

ProductQTY smallint '@Quantity', Discount real '@Discount')
IF @@Error<>0

BEGIN

EXEC sp_xml_removedocument @iDoc

ROLLBACK TRANSACTION

RETURN

END
EXEC sp_xml_removedocument @iDoc

COMMIT TRANSACTION

SELECT @OrderID

GO

插入Order后，我們獲取由內置的SQL Server SCOPE_IDENTITY函數剛剛生成的OrderID值。然后我們使用另一個INSERT語句（使用OPENXML函數從XML數據中獲取Order Details）繼續插入Order Details行。只要不出現錯誤，定單及其子定單細節行就被插入到其各自的數據庫表中。

SCOPE_IDENTITY方法使用單個Order及其子定單。不過，以一個XML批插入多個Order及其子定單的情況比較復雜。問題就是在具有多個Order記錄的情況下，仍要能夠將適當的定單映射到其子定單。由于不知道要關聯哪些行，因此必須添加一些代碼來處理這一問題?？梢栽赪ITH子句中使用@mp:id/@mp:parentid元屬性來提供一種獲取父定單的新OrderID并將其映射到其子定單的OrderID字段的方法。

插入和更新

由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，是一些非常有效的工具。