IT技术网 新版入口

IT技术网www.itjs.cn

当前位置：首页 > 数据库 > SQl Server > 如何使用SQL Server 2000中的XML功能(1)

如何使用SQL Server 2000中的XML功能(1)

发布时间：2014-08-01 00:00 来源：未知

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

例如，若使用FOR XML RAW来查询Northwind数据库的Employees表，它会在元素中返回每个员工行。SELECT语句中包含的每一列都会表示为元素的一个属性。下面的FOR XML RAW查询选择两个员工记录，然后以RAW格式返回：    

SELECT EmployeeID,

对该SELECT语句作一下修改就可以使用FOR XML AUTO子句。这次将元素命名为Employees，与源表的名称匹配。列仍是主元素的属性：   

SELECT EmployeeID, FirstName, LastName

转换和层次结构

OrderDate="10/03/1997" />

OrderDate="01/15/1998" />

OrderDate="04/09/1998" />

图 2  XML数据

请注意，这些数据不以父子层次结构显示。若想让数据显示为包含一系列相关元素的一系列元素，则可使用FOR XML AUTO子句（父子嵌套方法取决于成组聚集的父行）。CustomerID为ALFKI 时的XML结果如下：

ALFKI
08/25/1997
10/03/1997
10/13/1997
01/15/1998
03/16/1998
04/09/1998

图 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还被包装在一个元素中。

如何使用SQL Server 2000中的XML功能(1) 

图 5所示的GetFORXML方法代码说明了如何使用标准SqlCommand对象来执行包含FOR XML子句的SQL语句。这里使用了ExecuteXmlReader方法，因此可以将XML作为一个XmlReader对象返回。数据集的ReadXml方法接受XmlReader，其第二个参数表明该XML是一个片段。

private string GetFORXML(string sFile, string sSQL) {
因为它是一个片段，所以假如它有一个用于匹配的架构，则只加载所有的Customer节点。因此在加载XML之前，先使用ReadXmlSchema方法加载该架构（在这种情况下需要推理）。最后，将该数据集的DataSetName属性设置为“root”，这样将得到封闭在一个父标记中的读XML。 


图 4中的ASP.NET页能够执行和输出前面已经研究过的例子的XML，也能够使用FOR XML RAW, BINARY BASE64获取员工数据 。此外，通过使用以下子句，它还能够执行和输出XML，获取客户和定单数据： 







 FOR XML RAW 


使用FOR XML时，还必须说明计算列。尽管FOR XML支持计算列，但仍需确保对这些列进行命名。当FOR XML试图创建每一列的属性时，就会出现问题。属性名是通过使用关联的列名创建的。因此假如该列是一个计算列且没有别名，则SQL Server将停滞不前。简单的办法是确保给计算列取个别名，如下面的SQL语句及其XML结果所示： 







SELECT TOP 1 LastName + ', ' + FirstName AS FullName 
fake customer
USA
 


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 (
 


首先，将该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


插入和更新


由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





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元素。元素将包含一个属性，用来表示所检索的列。



AUTO
行集合记录可以转换成以FROM子句中的表命名的嵌套XML元素。所检索每一列都将表示为一个属性



EXPLICIT
为格式化XML提供许多控制。不过，EXPLICIT模式的使用语法要复杂得多。XSLT是一个比较常用的XML转换方法。


例如，若使用FOR XML RAW来查询Northwind数据库的Employees表，它会在元素中返回每个员工行。SELECT语句中包含的每一列都会表示为元素的一个属性。下面的FOR XML RAW查询选择两个员工记录，然后以RAW格式返回：    







SELECT EmployeeID,


对该SELECT语句作一下修改就可以使用FOR XML AUTO子句。这次将元素命名为Employees，与源表的名称匹配。列仍是主元素的属性：   







SELECT EmployeeID, FirstName, LastName


转换和层次结构
OrderDate="10/03/1997" />
OrderDate="01/15/1998" />
OrderDate="04/09/1998" />


图 2  XML数据



请注意，这些数据不以父子层次结构显示。若想让数据显示为包含一系列相关元素的一系列元素，则可使用FOR XML AUTO子句（父子嵌套方法取决于成组聚集的父行）。CustomerID为ALFKI 时的XML结果如下：







ALFKI
08/25/1997
10/03/1997
10/13/1997
01/15/1998
03/16/1998
04/09/1998


图 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还被包装在一个元素中。


如何使用SQL Server 2000中的XML功能(1) 
图 5所示的GetFORXML方法代码说明了如何使用标准SqlCommand对象来执行包含FOR XML子句的SQL语句。这里使用了ExecuteXmlReader方法，因此可以将XML作为一个XmlReader对象返回。数据集的ReadXml方法接受XmlReader，其第二个参数表明该XML是一个片段。







private string GetFORXML(string sFile, string sSQL) {
因为它是一个片段，所以假如它有一个用于匹配的架构，则只加载所有的Customer节点。因此在加载XML之前，先使用ReadXmlSchema方法加载该架构（在这种情况下需要推理）。最后，将该数据集的DataSetName属性设置为“root”，这样将得到封闭在一个父标记中的读XML。 


图 4中的ASP.NET页能够执行和输出前面已经研究过的例子的XML，也能够使用FOR XML RAW, BINARY BASE64获取员工数据 。此外，通过使用以下子句，它还能够执行和输出XML，获取客户和定单数据： 







 FOR XML RAW 
fake customer
USA
 


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 (
 


首先，将该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
作了这些更改之后，该数据集给出了关于如何取用该数据集中已更改的数据并将其转变为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

XML和SQL Server集成的第一步是引入FOR XML和OPENXML功能。由于这种进步，我们能够直接从SQL Server数据库获取XML，而不必使用某些中间组件对其进行转换。我们还可以通过结合使用OPENXML函数和老式备用方法、SQL INSERT、UPDATE和DELETE语句，将XML返给数据库。这些XML功能只是SQL Server 2005将提供的XML收集支持中的第一步，但它们如今已可用于集成XML应用程序和SQL Server，是一些非常有效的工具。 

 
 
 
 



 
    		
    		

    		

    		




	
相关阅读

		




            
        

    


    
推荐阅读

    



            
        

    


    
    

        

    
最新资讯

        
    

    


        

            
热文推荐

                更多
            

            
        



    








    

        
友情链接

    

    

        
    







    


    	
IT技术网 - www.itjs.cn 新版入口

    	
技术改变命运，ITJS.CN