SQL Server 2000提供了一些XML功能，用于通過XML將關(guān)系行集合轉(zhuǎn)換成分層的XML文檔、讀取XML文檔和批量加載數(shù)據(jù)。例如，可以將XML文檔傳遞到存儲(chǔ)過程，將XML聯(lián)接到某些表并返回一個(gè)行集合，甚至可以在數(shù)據(jù)庫中修改數(shù)據(jù)。XML在當(dāng)今企業(yè)系統(tǒng)中不斷擴(kuò)展的功能促進(jìn)了OPENXML函數(shù)和FOR XML語句的引入。其中某些功能不但支持XML，而且還提高批量加載數(shù)據(jù)時(shí)的性能。

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

返回XML

當(dāng)用于SELECT語句中時(shí)，F(xiàn)OR XML子句指示SQL Server將數(shù)據(jù)作為XML返回，這與標(biāo)準(zhǔn)行集合相反。可以指定返回模式：RAW、AUTO或EXPLICIT。每種模式都提供了XML的不同轉(zhuǎn)換方式（圖 1 給出了各種模式的概述）。



圖 1 FOR XML模式概述

例如，若使用FOR XML RAW來查詢Northwind數(shù)據(jù)庫的Employees表，它會(huì)在<row>元素中返回每個(gè)員工行。SELECT語句中包含的每一列都會(huì)表示為<row>元素的一個(gè)屬性。下面的FOR XML RAW查詢選擇兩個(gè)員工記錄，然后以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"/>

對(duì)該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"/>

轉(zhuǎn)換和層次結(jié)構(gòu)



盡管上例中的區(qū)別較小，但與用于聯(lián)接表的一個(gè)查詢一起使用時(shí)，AUTO和RAW之間的區(qū)別比較明顯。無論數(shù)據(jù)是來自一個(gè)表還是來自多個(gè)表，使用FOR XML RAW的查詢都將只返回<row>元素。因此，RAW模式不利用XML文檔的固有分層結(jié)構(gòu)。請(qǐng)看下面的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

本例將檢索一種一對(duì)多父子關(guān)系。若執(zhí)行該SQL語句，則將返回一系列客戶及其相應(yīng)定單。若附帶FOR XML RAW子句并再次執(zhí)行，則得出的XML結(jié)果將包含一個(gè)表示所返回的每一行的單<row>元素。例如，圖 2中的XML數(shù)據(jù)表示CustomerID為ALFKI時(shí)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數(shù)據(jù)

請(qǐng)注意，這些數(shù)據(jù)不以父子層次結(jié)構(gòu)顯示。若想讓數(shù)據(jù)顯示為包含一系列相關(guān)<Orders>元素的一系列<Customers>元素，則可使用FOR XML AUTO子句（父子嵌套方法取決于成組聚集的父行）。CustomerID為ALFKI 時(shí)的XML結(jié)果如下：

<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的可讀性大大提高，因?yàn)樗褂帽砻麃碜鳛樵孛Ｋ臄?shù)據(jù)也比較少，因?yàn)樗恢貜?fù)每個(gè)定單元素的CustomerID和CompanyName屬性，而FOR XML RAW例子則不然。

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

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的可讀性大大提高，因?yàn)樗褂帽砻麃碜鳛樵孛Ｋ臄?shù)據(jù)也比較少，因?yàn)樗恢貜?fù)每個(gè)定單元素的CustomerID和CompanyName屬性，而FOR XML RAW例子則不然。 如果您更喜歡將列值表示為元素而不是屬性，則您就會(huì)很幸運(yùn)。通過在FOR XML子句中指定ELEMENTS選項(xiàng)，所有列值都將成為XML中的元素。有時(shí)百聞不如一見，因此本文在圖 3中給出了當(dāng)ELEMENTS條件適用時(shí)前面查詢的輸出。（為了簡便起見，給出的XML示例僅包含CustomerID ALFKI的XML。這些查詢所生成的實(shí)際XML會(huì)包含所有客戶及其定單的XML。）使用ELEMENTS選項(xiàng)的查詢?nèi)缦拢?

該XML的可讀性大大提高，因?yàn)樗褂帽砻麃碜鳛樵孛Ｋ臄?shù)據(jù)也比較少，因?yàn)樗恢貜?fù)每個(gè)定單元素的CustomerID和CompanyName屬性，而FOR XML RAW例子則不然。 如果您更喜歡將列值表示為元素而不是屬性，則您就會(huì)很幸運(yùn)。通過在FOR XML子句中指定ELEMENTS選項(xiàng)，所有列值都將成為XML中的元素。有時(shí)百聞不如一見，因此本文在圖 3中給出了當(dāng)ELEMENTS條件適用時(shí)前面查詢的輸出。（為了簡便起見，給出的XML示例僅包含CustomerID ALFKI的XML。這些查詢所生成的實(shí)際XML會(huì)包含所有客戶及其定單的XML。）使用ELEMENTS選項(xiàng)的查詢?nèi)缦拢?

該XML的可讀性大大提高，因?yàn)樗褂帽砻麃碜鳛樵孛Ｋ臄?shù)據(jù)也比較少，因?yàn)樗恢貜?fù)每個(gè)定單元素的CustomerID和CompanyName屬性，而FOR XML RAW例子則不然。 如果您更喜歡將列值表示為元素而不是屬性，則您就會(huì)很幸運(yùn)。通過在FOR XML子句中指定ELEMENTS選項(xiàng)，所有列值都將成為XML中的元素。有時(shí)百聞不如一見，因此本文在圖 3中給出了當(dāng)ELEMENTS條件適用時(shí)前面查詢的輸出。（為了簡便起見，給出的XML示例僅包含CustomerID ALFKI的XML。這些查詢所生成的實(shí)際XML會(huì)包含所有客戶及其定單的XML。）使用ELEMENTS選項(xiàng)的查詢?nèi)缦拢?

通過ADO.NET返回XML



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

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



圖 5所示的GetFORXML方法代碼說明了如何使用標(biāo)準(zhǔn)SqlCommand對(duì)象來執(zhí)行包含F(xiàn)OR XML子句的SQL語句。這里使用了ExecuteXmlReader方法，因此可以將XML作為一個(gè)XmlReader對(duì)象返回。數(shù)據(jù)集的ReadXml方法接受XmlReader，其第二個(gè)參數(shù)表明該XML是一個(gè)片段。

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



因?yàn)樗且粋€(gè)片段，所以如果它有一個(gè)用于匹配的架構(gòu)，則只加載所有的Customer節(jié)點(diǎn)。因此在加載XML之前，先使用ReadXmlSchema方法加載該架構(gòu)（在這種情況下需要推理）。最后，將該數(shù)據(jù)集的DataSetName屬性設(shè)置為“root”，這樣將得到封閉在一個(gè)父標(biāo)記<root/>中的讀XML。

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

 FOR XML RAW 

 FOR XML AUTO 

 FOR XML AUTO, ELEMENTS 

 FOR XML AUTO, ELEMENTS, XMLDATA 

使用FOR XML限制

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

SELECT EmployeeID, FirstName, LastName, Photo 

FROM Employees 

WHERE LastName LIKE 'D%' 

FOR XML RAW,BINARY BASE64 

這樣SQL Server就會(huì)使用BASE64格式來格式化二進(jìn)制數(shù)據(jù)。其優(yōu)點(diǎn)是SQL語句將不再改變。缺點(diǎn)是讀取使用BINARY BASE64的查詢結(jié)果不方便。下面的XML是使用BINARY BASE64時(shí)所返回的結(jié)果的一個(gè)例子。這里削減了Photo屬性的內(nèi)容，因?yàn)槠溟L度超過了14,000個(gè)字符！

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

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

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

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時(shí)，還必須說明計(jì)算列。盡管FOR XML支持計(jì)算列，但仍需確保對(duì)這些列進(jìn)行命名。當(dāng)FOR XML試圖創(chuàng)建每一列的屬性時(shí)，就會(huì)出現(xiàn)問題。屬性名是通過使用關(guān)聯(lián)的列名創(chuàng)建的。因此如果該列是一個(gè)計(jì)算列且沒有別名，則SQL Server將停滯不前。簡單的辦法是確保給計(jì)算列取個(gè)別名，如下面的SQL語句及其XML結(jié)果所示：

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

FROM Employees 

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

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

OPENXML



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

為此，首先取一個(gè)包含客戶數(shù)據(jù)的XML文檔，并將其插入到Northwind數(shù)據(jù)庫的Customers表中。該XML文檔可能使用元素或?qū)傩詠肀硎驹摽蛻舻臄?shù)據(jù)，如下所示：

<root>

<customer>

<custid>77777</custid>

<custname>fake customer</custname>

<city>Somewhere</city>

<country>USA</country>

</customer>

</root> 

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

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

圖 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 

對(duì)XML所做的第一件事是將其傳遞給sp_xml_preparedocument系統(tǒng)存儲(chǔ)過程。該過程取用該XML并將其轉(zhuǎn)換成內(nèi)部DOM，以便OPENXML能夠?qū)⑺鳛樾屑蟻硖幚怼Ｔ撨^程還在內(nèi)存中創(chuàng)建一個(gè)DOM引用（圖 6中的@iDoc變量）。通過將@iDoc變量傳遞給OPENXML函數(shù)，可以在SELECT語句的FROM子句中將XML作為行集合來訪問。

使用完XML后，應(yīng)使用sp_xml_removedocument系統(tǒng)存儲(chǔ)過程從內(nèi)存中刪除之。

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

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

通過OPENXML插入



現(xiàn)在我們將用下面的XML示例從中插入一個(gè)定單和兩個(gè)定單細(xì)節(jié)行：

<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所示的存儲(chǔ)過程，并使用sp_xml_preparedocument系統(tǒng)存儲(chǔ)過程準(zhǔn)備之。然后啟動(dòng)一個(gè)事務(wù)來包裝INSERT語句，以便插入一個(gè)定單及其子記錄。這樣便可以在部分事務(wù)失敗時(shí)回滾事務(wù)。接下來，使用在Customer\Order節(jié)點(diǎn)開始的OPENXML打開XML文檔。利用WITH子句，將XML文檔返回到Order元素（Customer 元素）的父節(jié)點(diǎn)，然后查看Customer元素的CustomerID屬性值，從而獲得CustomerID。這是OPENXML函數(shù)的一個(gè)重要功能，因?yàn)樗層脩羰褂檬芟薜腦Path表達(dá)式來遍歷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后，我們獲取由內(nèi)置的SQL Server SCOPE_IDENTITY函數(shù)剛剛生成的OrderID值。然后我們使用另一個(gè)INSERT語句（使用OPENXML函數(shù)從XML數(shù)據(jù)中獲取Order Details）繼續(xù)插入Order Details行。只要不出現(xiàn)錯(cuò)誤，定單及其子定單細(xì)節(jié)行就被插入到其各自的數(shù)據(jù)庫表中。

SCOPE_IDENTITY方法使用單個(gè)Order及其子定單。不過，以一個(gè)XML批插入多個(gè)Order及其子定單的情況比較復(fù)雜。問題就是在具有多個(gè)Order記錄的情況下，仍要能夠?qū)⑦m當(dāng)?shù)亩▎斡成涞狡渥佣▎巍Ｓ捎诓恢酪P(guān)聯(lián)哪些行，因此必須添加一些代碼來處理這一問題。可以在WITH子句中使用@mp:id/@mp:parentid元屬性來提供一種獲取父定單的新OrderID并將其映射到其子定單的OrderID字段的方法。

插入和更新

由SQL Server sp_xml_preparedocument系統(tǒng)存儲(chǔ)過程準(zhǔn)備的XML文檔可像其他任何表一樣用于JOIN中。它們也可像其他任何行集合一樣用于INSERT、UPDATE或DELETE記錄。為了證明這一點(diǎn)，首先從OrderID 10285選擇一系列Order Details行，并將它們填入一個(gè)ADO.NET數(shù)據(jù)集。然后，通過更改它們的數(shù)量和向該數(shù)據(jù)集添加若干Order Details行，修改某些現(xiàn)有的Order Details行，如下面的代碼片斷所示：



作了這些更改之后，該數(shù)據(jù)集給出了關(guān)于如何取用該數(shù)據(jù)集中已更改的數(shù)據(jù)并將其轉(zhuǎn)變?yōu)閄ML的若干個(gè)選項(xiàng)。可以使用WriteXml方法寫出作為DiffGram的數(shù)據(jù)、帶有或不帶有其架構(gòu)的數(shù)據(jù)。還可以使用GetXml方法將數(shù)據(jù)置入XML。本例將使用DiffGram，因?yàn)樗鼘薷男幸约案餍性谛薷那昂蟮臓顟B(tài)（用于UPDATES）。

DiffGram被傳遞給一個(gè)存儲(chǔ)過程（如圖 8所示），該存儲(chǔ)過程從XML文檔取出新的和更新的行，并將它們插入一個(gè)TABLE變量(@tblTemp)。請(qǐng)注意，圖 8中的OPENXML函數(shù)使用XPath表達(dá)式來獲取hasChanges屬性的值。對(duì)于插入行該值為i，對(duì)于修改行為m。由于OPENXML函數(shù)能夠篩選出行，因此只需將一個(gè)XML文檔傳遞給該存儲(chǔ)過程。接下來，適當(dāng)?shù)男斜徊迦隣rder Details表，然后適當(dāng)?shù)腛rder Details行被更新。INSERT和UPDATE都被包裝在一個(gè)事務(wù)內(nèi)部，因此無論哪一個(gè)失敗都可以回滾。

圖 8 使用DiffGram進(jìn)行插入和更新

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和存儲(chǔ)過程從數(shù)據(jù)集插入10行非常容易。但DataAdapter會(huì)一次一個(gè)地遍歷數(shù)據(jù)集行來查找行狀態(tài)為已插入的行，并執(zhí)行與DataAdapter針對(duì)每一行的InsertCommand關(guān)聯(lián)的存儲(chǔ)過程。這意味著10次插入將導(dǎo)致對(duì)數(shù)據(jù)庫的10次調(diào)用。當(dāng)必要的更新較少時(shí)，這種ADO.NET代碼和數(shù)據(jù)庫之間的來回操作幾乎不會(huì)引起什么明顯變化。不過，當(dāng)引入其他因素時(shí)，如并發(fā)用戶數(shù)量大大增加或插入數(shù)百行時(shí)，性能可能會(huì)迅速降低。要插入100行，不必調(diào)用100次存儲(chǔ)過程，而可以將這100行作為XML一次性傳遞到一個(gè)存儲(chǔ)過程中。

與可下載的代碼一起提供的示例ASP.NET應(yīng)用程序?qū)?zhí)行一個(gè)將n個(gè)客戶插入到Customers表中的存儲(chǔ)過程。ASP.NET代碼在數(shù)據(jù)集中創(chuàng)建并添加100條客戶記錄。然后使用WriteXml方法輸出XML數(shù)據(jù)，不包含其架構(gòu)。該XML然后被傳遞給一個(gè)一次性插入100個(gè)客戶的存儲(chǔ)過程。與每插入一次便執(zhí)行一個(gè)存儲(chǔ)過程的一次一個(gè)地插入各個(gè)客戶相比，這種插入批量數(shù)據(jù)的方法更為高效。

使用示例



若使用SQL Query Analyzer來測(cè)試該列中包含的FOR XML SQL，可能需要更改某些默認(rèn)選項(xiàng)。例如，若要查詢和返回XML，則要先增加結(jié)果窗格中每列的最大字符數(shù)。轉(zhuǎn)到Options | Tools | Results，將Maximum Characters per Column設(shè)置增加到某一值，如4096。由于返回的XML在結(jié)果窗格中顯示為單列，因此該XML不會(huì)在默認(rèn)的256個(gè)字符處截止。最后，用戶將能夠看到該XML，如果愿意，還可以將它復(fù)制并粘貼到XML編輯器中。

結(jié)束語



XML和SQL Server集成的第一步是引入FOR XML和OPENXML功能。由于這種進(jìn)步，我們能夠直接從SQL Server數(shù)據(jù)庫獲取XML，而不必使用某些中間組件對(duì)其進(jìn)行轉(zhuǎn)換。我們還可以通過結(jié)合使用OPENXML函數(shù)和老式備用方法、SQL INSERT、UPDATE和DELETE語句，將XML返給數(shù)據(jù)庫。這些XML功能只是SQL Server 2005將提供的XML收集支持中的第一步，但它們?nèi)缃褚芽捎糜诩蒟ML應(yīng)用程序和SQL Server，是一些非常有效的工具。