深入探討SQL Server 2000對XML的支持

作者簡介:］Wayne,新一代程序員，十二歲時開始學習編程，十三歲時擁有自己的電腦，先後學過Basic、Pascal、C、FOXPRO、VB、Delphi、C++、SQL，學習Java語言後，遂成為Java的瘋狂崇拜者，現就讀於中國科技大學。

　　引言

　　我們可以很簡單的使用ADO訪問數據庫中的數據，但是，如果我們想把從數據庫中檢索得到的數據以XML的格式顯示出來的話，就要費點神了，當然，我們可以去找一些現成的應用程序或者是把現有的存儲過程修改一下，來完成這個想法。

　　雖說ADO2.5宣稱支持XML，但是它仍需要一個額外的層來處理把數據轉化成XML的過程。幸好有了SQL Server 2000，它宣稱可以直接從數據庫中取出數據，而不通過ADO2.5直接把數據以XML方式顯示出來。這個功能極大的提高了構造分布式、數據集中的應用程序的性能，因為這個特性消除了不必要的代碼層。

　　讓我們看一看，為了支持XML，SQL Server 2000添加了什麼新的特性：

　　1、能夠使用 HTTP 訪問 SQL Server。

　　2、支持 XDR（XML數據簡化）架構並且能夠指定對這些架構的 XPath 查詢。

　　3、能夠檢索並寫入 XML 數據：

　　　　使用 SELECT 語句和 FOR XML 子句檢索 XML 數據。

　　　　使用 OPENXML 行集提供程序寫入 XML 數據。

　　　　使用 XPath 查詢語言檢索 XML 數據。

　　4、增強了 Microsoft SQL Server 2000 OLE DB 提供程序 (SQLOLEDB)，使得可以將 XML 文檔設置為命令文本並以流的形式返回結果集。

　　可見，我們可以使用好幾種方法使用SQL Server 2000來訪問XML格式的數據：第一種，在 URL 中執行的查詢可以直接訪問 SQL Server 2000生成XML文檔（也可以調用存儲在Web服務器上的XML模版生成XML數據文件）。第二種，可以使用SELECT命令和FOR XML關鍵字，通過調用一個存儲過程或是通過使用XPath查詢來取得XML數據。SQL Server 2000 完全支持 XDR（XML數據簡化）架構，具有映射XML元素和屬性到表和字段中的功能。下面，我就探討一下SQL Server 2000對XML的支持特性。

　　一、配置SQL Server 2000的IIS虛擬目錄

　　在本文的開始，我想先談一談如何配置SQL Server 2000的IIS虛擬目錄。SQL Server 2000 允許為IIS創建一個虛擬目錄，用來直接訪問一個SQL數據庫中的數據。一旦在一台配置了IIS的計算機上安裝了SQL Server 2000，就可以運行SQL Server 的 IIS 虛擬目錄管理實用工具來配置SQL Server 2000的IIS虛擬目錄。

　　好，讓我們開始配置過程吧！

　　在"SQL Server 工具"程序組中單擊"在 IIS 中配置 SQL XML 支持"，這時就會出現一個與IIS管理器相似的界面。展開服務器，選取默認的 Web 站點，點右鍵，在彈出的菜單中選取“新建”選項，然後單擊"虛擬目錄"命令。新虛擬目錄的屬性頁將顯示在屏幕上。在"新的虛擬目錄屬性"對話框的"常規"選項卡上，輸入虛擬目錄的名稱，在本例中，請輸入Northwind和物理目錄路徑（例如 C:\Inetpub\Wwwroot\Northwind，假設在 C:\Inetpub\Wwwroot 目錄中已創建了 Northwind子目錄）,當然我們也可以使用“浏覽”按鈕選擇目錄。在“安全性”選項卡上，填入有效的 SQL Server 登錄信息，在進入下一個選項卡時，它將要求你確認剛才輸入的密碼。在“數據源”選項卡上，在“SQL Server”框中輸入服務器的名稱，在“數據庫”框中，輸入 Northwind 作為默認數據庫的名稱。

在"設置"選項卡上，你可以選擇“允許 URL 查詢”、“允許模板查詢”、“允許 XPath”和“允許 POST”選項。

　　在構建一個應用程序時，你不但要考慮到能夠訪問到SQL Server數據庫，要有足夠的安全級別以保證你的數據的安全性。在“虛擬名稱”選項卡上，你可以更具自己的需要選擇新建模板類型（template)、架構類型(schema)和模板和架構類型（dbonject），並創建它們的路徑。好，我們這樣就創建了虛擬目錄 Northwind。默認情況下，使用該虛擬目錄對 Northwind 數據庫執行指定的查詢。你一定迫不及待的吸納高看看執行SQL的結果是什麼樣的了吧？好，讓我們在浏覽器中輸入http://localhost/northwind?sql=SELECT * FROM CUSTOMERS FOR XML AUTO&root=root，試驗一下吧！

　　我們還可以編程來實現配置SQL Server 2000的IIS虛擬目錄，請見下面的代碼：

　　Set ObjXML = CreateObject("SQLVDir.SQLVDirControl")

　　ObjXML.Connect 'Connect to the local computer and Web site "1"

　　Set ObjVDirs = ObjXML.SQLVDirs

　　Set ObjVDir = ObjVDirs.AddVirtualDirectory("Northwind")

　　ObjVDir.PhysicalPath = "C:\Inetpub\wwwroot\northwind"

　　ObjVDir.UserName = "wayne" 'SQL Server login

　　ObjVDir.PassWord = "" 'SQL Server PassWord

　　ObjVDir.DatabaseName = "Northwind"

　　objVDir.AllowFlags = 73

　　Set objVNames = objVDir.VirtualNames

　　objVNames.AddVirtualName "dbobject", 1, ""

　　objVNames.AddVirtualName "schema", 2,"C:\Inetpub\wwwroot\northwind\schema"

　　objVNames.AddVirtualName "template", 4 , "C:\Inetpub\wwwroot\northwind\template"

　　objXML.Disconnect

　　msgbox "Done."


　　二、使用 HTTP 執行 SQL 語句

　　使用我們剛才創建的虛擬目錄，我們就可以通過把SQL查詢語句寫入URL的方式執行查詢。打開浏覽器，在地址欄中寫入以下URL：http://localhost/northwind?sql=SELECT+ *+FROM+Customers+WHERE+CustomerID='ANTON' +FOR+XML+AUTO&root=root，如果你使用的虛擬目錄別名不是Northwind或者你使用一個遠程服務器，只需要把相應的值改掉就可以了。

　　浏覽器中會出現：

＜?XML version="1.0" encoding="utf-8" ?＞
＜root＞
＜Customers CustomerID="ANTON" CompanyName="Antonio Moreno Taquería" ContactName="Antonio Moreno" ContactTitle="Owner" Address="Mataderos 2312" City="México D.F."

PostalCode="05023" Country="Mexico" Phone="(5) 555-3932" /＞
＜/root＞

　　讓我們來分析一下這個URL，“http://localhost/northwind”後面跟了一個SQL查詢語句，用來執行查詢數據庫Northwind的任務。在本例中，我們使用的查詢語句是“SELECT+*+FROM+Customers+WHERE+CustomerID='ANTON'”。請注意，這條語句已經被URL編碼過了，其中的空格都被替換成加號“+”，這樣它才能被浏覽器正確的傳送到數據庫中去，關於URL編碼格式請您參閱相關文檔。

　　在查詢語句之後，有添加了兩個新的關鍵字：FOR XML和AUTO。FOR XML關鍵字可以對現有的關系數據庫執行 SQL 查詢，以返回 XML 文檔形式。AUTO模式則將查詢結果作為嵌套 XML 元素返回，在 FROM 子句內，每個在 SELECT 子句中至少有一列被列出的表都表示為一個 XML 元素，SELECT 子句中列出的列映射到適當的元素特性，當指定了 ELEMENTS 選項後，表列映射到子元素而非特性。默認情況下，AUTO 模式將表列映射到 XML 特性。

　　在FOR XML AUTO後，還需要添加一個參數“root”，其參數值作為返回的XML文件的root元素名。比如說，你可以把上面我給出的例子中的root的參數值設為Northwind，你會發現，返回的XML文件中除了root元素名變為Northwind了以外，其它都沒有變化。

　　上面我們說的是直接使用HTTP中執行簡單的查詢，除此之外你還可以執行更加復雜的查詢，比如說連接不同的表進行查詢，請看下面的例子，在下例中，SELECT 語句連接了 Northwind 數據庫的中的 Customers 和 Orders 表，並返回信息。

http://localhost/northwind?sql=SELECT
Customer.CustomerID%2cCustomer.Contact
Name%2c%5bOrder%5d.OrderID+FROM+Customers+
Customer+INNER+JOIN+Orders+%5bOrder%
5d+ON+Customer.CustomerID%3d%5bOrder%
5d.CustomerID+FOR+XML+AUTO&root=Northwind

因為返回的XML文件太長，我就不把它列出來了。

　　如果你不想在Customers表和Orders表中出現嵌套的話，SQL Server 2000還提供另一個關鍵字用來替代AUTO，這個關鍵字就是RAW。RAW 模式將查詢結果集中的每一行轉換為帶有類屬標識符 row 的 XML 元素。為了讓您能夠深入了解RAW，我再給出一個例子：使用 RAW 模式檢索客戶和訂單信息

　　下面的查詢返回客戶和訂單信息。在 FOR XML 子句中指定 RAW 模式。


SELECT Customers.CustomerID, Orders.OrderID, Orders.OrderDate
FROM Customers, Orders
WHERE Customers.CustomerID = Orders.CustomerID
ORDER BY Customers.CustomerID
FOR XML RAW

　　下面是部分結果：

＜row CustomerID="ALFKI" OrderID="10643" OrderDate="1997-08-25T00:00:00"/＞

＜row CustomerID="ANATR" OrderID="10308" OrderDate="1996-09-18T00:00:00"/＞

＜row CustomerID="ANATR" OrderID="10

625" OrderDate="1997-08-08T 00:00:00"/＞

＜row CustomerID="AROUT" OrderID="10355" OrderDate="1996-11-15T00:00:00"/＞

　　可以使用外部聯接指定上面的查詢在結果集中返回所有客戶，無論這些客戶是否有訂單。


　　　SELECT C.CustomerID, O.OrderID, O.OrderDate
　　　　　FROM Customers C LEFT OUTER JOIN Orders O ON C.CustomerID = O.CustomerID　ORDER BY C.CustomerID　FOR XML RAW

　　下面是部分結果：


＜row CustomerID="BONAP" OrderID="11076" OrderDate="1998-05-06T00:00:00"/＞

＜row CustomerID="FISSA"/＞

＜row CustomerID="PARIS"/＞

＜row CustomerID="RICSU" OrderID="11075" OrderDate="1998-05-06T00:00:00"/＞

　　我們還可以使用 HTTP 執行存儲過程，比如下面這個名為GetXML的存儲過程：


　　CREATE PROCEDURE GetXML
　　　(
　　　　@CustomerID varchar(5)
　　　)
　　　AS
　　　　BEGIN
　　　　　SELECT CustomerID, CompanyName,ContactName
　　　　　　FROM Customers
　　　　　　WHERE CustomerID LIKE @CustomerID + '%'
　　　　　　FOR XML AUTO
　　END

　　為了執行這個存儲過程並傳送相應的參數，我們可以使用下面這段URL，http://localhost/northwind?sql=exec+GetXML+'A'&root=root。這樣，我們就能夠在更高一層次使用存儲過程，並且可以根據最終用戶想要得到的結果動態的改變參數值（比如在本例中，我們用的是“A”）。

　　三、使用XML模板進行查詢

　　SQL Server 2000通過在HTTP請求中內嵌SQL語句的功能顯然是非常強大並且有用的。但是這種見也隱藏著極大的隱患，一旦某個最終用戶了解了直接使用浏覽器查詢數據庫的方法，那麼數據庫中的數據就很危險了，因為最終用戶可能會嘗試執行他自己的insert,update甚至是delete過程。

　　為了維護大部分數據庫中數據交易的安全，使用戶不能夠直接使用URL進行查詢，SQL Server 2000介紹了XML模板的概念，可以設置SQL Server虛擬目錄把這些URL查詢導向那些含有所需的SQL過程的XML模板中。

　　在我們討論模板概念之前，讓我們重新回到SQL Server的IIS虛擬目錄管理器中，進入“設置”選項卡。為了防止用戶使用HTTP訪問，我們必須把“允許 URL 查詢”選項給去掉。所有SQL查詢現在都會被導向XML模板，XPath中。

　　為了允許XML模板執行SQL查詢，進入“虛擬名稱”選項卡，並單擊新建按鈕，新建一個模板文件夾，取名為templates，在下拉菜單中選擇template。然後，要麼輸入一個你的 XML模板將儲存的路徑或者單擊“浏覽”按鈕。本例中使用C:\Inetpub\wwwroot\XML\templates。一旦你已經提供了所有的必要的信息，請單擊“保存”按鈕。


現在一個虛擬目錄已經被映射到一個指定來保存 XML查詢模板的文件夾，讓我們創建一個有效的 XML模板，用來執行 SQL查詢。下面的代碼是一個模板示例。


　　file2.XML

＜Northwind XMLns:sql=
"urn:schemas-microsoft-com:XML-sql"＞
＜sql:query＞
SELECT Customers.CustomerID, Customers.ContactName,
Orders.OrderID, Orders.CustomerID
FROM Customers
INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID
FOR XML AUTO
＜/sql:query＞
＜/Northwind＞


　　這段代碼中使用了一個名為sql的前綴和一個URI urn:schemas-microsoft-com:xml-sql，這個前綴用來標識使用在 SQL Server XML ISAPI上的元素。有一個元素名為query，顧名思義它就是用來標記模板文件中的SQL 查詢語句。好，讓我們來演示一下如何使用這個模板吧！請在地址欄中輸入，http://localhost/northwind/templates/file2.XML，當然你也可以根據你的需要改變相應的服務器名和虛擬目錄名。

　　讓我們把這個 URL拆分成單獨的片段，進行分析，你可以看見，我們先使用了 northwind虛擬根，然後使用templates虛擬目錄名，如前我們說過，該虛擬目錄名已經映射到templates的物理目錄中。最後， URL給出了模板文件的名稱。執行這個模板，浏覽器就會把表中customers元素下嵌套的不同的訂單以XML文檔的形式顯示出來。

　　使用模板而不使用 URL查詢有許多優點。首先，現在一個最終用戶就沒有改變SQL語句的權力了，去除“通過URL查詢訪問 SQL Server服務器”的選項 ，就只有SQL Server XML ISAPI可以用來處理模板文件，這就避免未經授權的 插入、更新和刪除程序被執行。其次， XML模板支持動態加入參數，這就允許你不用更改模板文件就可以更改一個 SQL WHERE子句的值。

　　使用參數，就像插入一個 XML header元素一樣簡單的，在 header元素中，定義了一個 param元素，使用一個值為CustomerID的名稱屬性。這個參數被賦予一個默認值"A"，你可以象在一個存儲過程中一樣在模板文件中使用這個參數，只要在這個參數前添加一個@，然後把它放入SQL語句或用來調用一個存儲過程就可以了。請見下面的代碼。

＜Northwind xmlns:sql="urn:schemas-microsoft-com:XML-sql"＞
＜sql:header＞
＜sql:param name='CustomerID'＞A＜/sql:param＞
＜/sql:header＞
＜sql:query＞
SELECT Customers.CustomerID, Customers.ContactName,
Orders.OrderID, Orders.CustomerID
FROM Customers
INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID
WHERE Customers.CustomerID LIKE @CustomerID + '%'
FOR XML AUTO
＜/sql:query＞
＜/Northwind＞

　　在本例中，CustomerID參數被一個WHERE子句使用。如果把參數設為"B"，SQL 語句就會從Customers和Orders表中返回所有的CustomerID以B開頭的行。調用模板並傳遞正確的CustomerID參數值，只要在查詢字符串之後加上參數名和參數值，如：http://localhost/northwind/templates/file2.xml?CustomerID=B即可，就這麼簡單。

　　四、XPath查詢 架構和模板

　　XPath查詢也可以被內嵌進一個XML模板文件中，下面的代碼是一個包含XPath查詢的簡單的XML模板文件。

＜Northwind XMLns:sql=
"urn:schemas-microsoft-com:
XML-sql"＞
＜sql:xpath-query mapping-schema=
"file4.xdr"＞
/Customer[@CustomerID=
'ALFKI']/Order
＜/sql:xpath-query＞
＜/Northwind＞

　　這個查詢使用了一個架構(schema）返回CustomerID號為ALFKI的用戶的所有的訂單，如果想要使XPath語句運行，必須使用一個XDR架構文件映射不同的XML元素和屬性到相應的數據庫表和字段名。下面給出了這個架構文件。

＜?XML version="1.0" ?＞
＜Schema xmlns="urn:schemas-microsoft-com:XML-data"
XMLns:dt="urn:schemas-microsoft-com:datatypes"
xmlns:sql="urn:schemas-microsoft-com:XML-sql"＞

＜ElementType name="Customer" sql:relation="Customers"＞
＜AttributeType name="CustomerID" dt:type="id" /＞
＜AttributeType name="CompanyName" /＞
＜AttributeType name="ContactName" /＞
＜AttributeType name="City" /＞
＜AttributeType name="Fax" /＞
＜AttributeType name="Orders" dt:type=
"idrefs" sql:id-prefix="Ord-" /＞

＜attribute type="CustomerID" /＞
＜attribute type="CompanyName" /＞
＜attribute type="ContactName" /＞
＜attribute type="City" /＞
＜attribute type="Fax" /＞
＜attribute type="Orders" sql:relation=
"Orders" sql:fIEld="OrderID"＞
＜sql:relationship
key-relation="Customers"
key="CustomerID"
foreign-relation="Orders"
foreign-key="CustomerID" /＞
＜/attribute＞

＜element type="Order"＞
＜sql:relationship
key-relation="Customers"
key="CustomerID"
foreign-relation="Orders"
foreign-key="CustomerID" /＞
＜/element＞
＜/ElementType＞

＜ElementType name="Order" sql:relation="Orders"＞
＜AttributeType name="OrderID" dt:type=
"id" sql:id-prefix="Ord-" /＞
＜AttributeType name="EmployeeID" /＞
＜AttributeType name="OrderDate" /＞
＜AttributeType name="RequiredDate" /＞
＜AttributeType name="ShippedDate" /＞

＜attribute type="OrderID"/＞
＜attribute type="EmployeeID" /＞
＜attribute type="OrderDate" /＞
＜attribute type="RequiredDate" /＞
＜attribute type="ShippedDate" /＞

＜element type="OrderDetail"＞
＜sql:relationship
key-relation="Orders"
key="OrderID"
foreign-relation="[Order Details]"
foreign-key="OrderID" /＞
＜/element＞
＜element type="Employee"＞
＜sql:relationship
key-relation="Orders"
key="EmployeeID"
foreign-relation="Employees"
foreign-key="EmployeeID" /＞
＜/element＞
＜/ElementType＞

＜ElementType name="OrderDetail" sql:relation=
"[Order Details]"
sql:key-fIElds="OrderID ProductID"＞
＜AttributeType name="ProductID" dt:type="idref"
sql:id-prefix="Prod-" /＞
＜AttributeType name="UnitPrice"/＞
＜AttributeType name="Quantity" /＞

＜attribute type="ProductID" /＞
＜attribute type="UnitPrice"/＞
＜attribute type="Quantity" /＞

＜element type="Discount" sql:fIEld="Discount"/＞
＜/ElementType＞

＜ElementType name="Discount" dt:type="string"
sql:relation="[Order Details]"/＞

＜ElementType name="Employee" sql:relation="Employees"＞
＜AttributeType name="EmployeeID" dt:type="idref"
sql:id-prefix="Emp-" /＞
＜AttributeType name="LastName" /＞
＜AttributeType name="FirstName" /＞
＜AttributeType name="Title" /＞
＜attribute type="EmployeeID"/＞

＜attribute type="LastName" /＞
＜attribute type="FirstName" /＞
＜attribute type="Title" /＞
＜/ElementType＞
＜/Schema＞

　　如果您想深入了解架構文件的話，請參看SQL Server 2000的用戶文檔或等待我的以後的文章。

　　和內嵌在XML模板文件中的SQL查詢語句一樣，XPath查詢語句使用urn:schemas-microsoft-com:xml-sql和sql作為前綴，共同標示用在模板中的自定義元素和屬性，對於XPath查詢而言，我們使用一個名為xpath-query的元素來標識查詢語法，這個元素也有一個名為mapping-schema的屬性，用來標示相應用以映射表和字段到特定的XML項目的架構文件所在的路徑。

　　下面的代碼給出了另一個使用更復雜XPath查詢的模板文件。


＜Northwind XMLns:sql=
"urn:schemas-microsoft-com:XML-sql"＞
＜sql:xpath-query mapping-schema=
"listing4.xdr"＞
/Customer[@CustomerID=
'ALFKI']/Order/
Employee[@LastName='Suyama']
＜/sql:xpath-query＞
＜/Northwind＞

　　當執行這個模板文件時，XPath查詢返回與某個客戶簽下訂單的雇員(employee)的姓名，結果如下：

＜Northwind XMLns:sql=
"urn:schemas-microsoft-com:XML-sql"＞
＜Employee EmployeeID="Emp-6"
LastName="Suyama"
FirstName="Michael"
Title="Sales
Representative"/＞
＜/Northwind＞

　　模板文件中使用的XPath查詢也可以使用參數，處理過程很象在XSL樣式表中使用參數一樣。象XSL一樣，使用$指定一個變量。下面的代碼說明了如何在一個包含XPath查詢的模板文件中整合變量。


＜Northwind xmlns:sql="urn:schemas-microsoft-com:XML-sql"＞
＜sql:header＞
＜sql:param name="ID"/＞
＜/sql:header＞
＜sql:xpath-query mapping-schema="listing4.xdr"＞ {{should this be "listing6.xdr"?}}
/Customer/Order[@OrderID=$ID]
＜/sql:xpath-query＞
＜/Northwind＞

　　通過在URL中傳遞參數名和相應的參數值，我們就可以完成把參數傳遞到模板中的操作。結果如下：


＜Northwind xmlns:sql="urn:schemas-microsoft-com:XML-sql"＞
＜Order OrderID="Ord-10643" EmployeeID=
"6" OrderDate="1997-08-25T00:00:00" RequiredDate=
"1997-09-22T00:00:00" ShippedDate=
"1997-09-02T00:00:00"＞
＜Employee EmployeeID="Emp-6" LastName=
"Suyama" FirstName="Michael" Title=
"Sales Representative" /＞
＜OrderDetail ProductID="Prod-28" UnitPrice=
"45.6" Quantity="15"＞
＜Discount＞0.25＜/Discount＞
＜/OrderDetail＞
＜OrderDetail ProductID="Prod-39" UnitPrice=
"18" Quantity="21"＞
＜Discount＞0.25＜/Discount＞
＜/OrderDetail＞
＜OrderDetail ProductID="Prod-46" UnitPrice=
"12" Quantity="2"＞
＜Discount＞0.25＜/Discount＞
＜/OrderDetail＞
＜/Order＞
＜/Northwind＞



　　小結

　　通過使用上面我介紹的幾種技術，我們可以直接從SQL Server 2000數據庫中直接取得XML數據。如我所介紹，URL查詢、XML模板文件、XDR架構和XPath查詢提供了強大的功能，從SQL Server 2000中直接獲得XML數據。除此之外，還有很多重要的概念，由於篇幅有限在本文中不可能詳述，如FOR XML EXPLICIT查詢和OPENXML這些技術，我會在以後的文章中進一步進行討論，請大家等待。