深入探讨 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="10625" OrderDate="1997-08-08T00: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这些技术，我会在以后的文章中进一步进行讨论，请大家等待。