如何使用SQL Server 2000中的XML功能一-技术开发专区

如何使用SQL Server 2000中的XML功能一

作者：IT168 晓杜编译编辑：晓熊 2007-02-09 10:37

  【IT168技术文档】
    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模式概述

模式	说明
RAW	行集合的每个记录都转换成叫做行的XML元素。<row>元素将包含一个属性，用来表示所检索的列。
AUTO	行集合记录可以转换成以FROM子句中的表命名的嵌套XML元素。所检索每一列都将表示为一个属性
EXPLICIT	为格式化XML提供许多控制。不过，EXPLICIT模式的使用语法要复杂得多。XSLT是一个比较常用的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>

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选项的查询如下：
通过ADO.NET返回XML
    在提供的可下载的示例代码中还包含了一个用于运行某些代码示例（如图 4所示）的ASP.NET项目。该项目示例使用一个叫做GetFORXML的方法，该方法运行任何SQL时都附带一个FOR XML子句，并将得到的XML写到一个文件。为简单起见，该XML还被包装在一个<root>元素中。

图 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可以编码专用字符。例如，如果<字符包含在数据中，则它将转换成“<”。

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文档。现在我们来看这一切是如何实现的。首先，可以在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表选择该行集合，或插入到其中。

(待续.......)

关注我们