|
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的不同转换方式。
模式 说明
RAW:行集合的每个记录都转换成叫做行的XML元素。<row>元素将包含一个属性，用来表示所检索的列。
AUTO:行集合记录可以转换成以FROM子句中的表命名的嵌套XML元素。所检索每一列都将表示为一个属性
EXPLICIT:为格式化XML提供许多控制。不过，EXPLICIT模式的使用语法要复杂得多。XSLT是一个比较常用的XML转换方法。
例如，若使用FOR XML RAW来查询Northwind数据库的Employees表，它会在<row>元素中返回每个员工行。SELECT语句中包含的每一列都会表示为<row>元素的一个属性。下面的FOR XML RAW查询选择两个员工记录，然后以RAW格式返回：
<row EmployeeID="1" FirstName="Nancy" LastName="Davolio"/>
<row EmployeeID="9" FirstName="Anne" LastName="Dodsworth"/>
对该SELECT语句作一下修改就可以使用FOR XML AUTO子句。这次将元素命名为Employees，与源表的名称匹配。列仍是主元素的属性：
<Employees EmployeeID="1" FirstName="Nancy" LastName="Davolio"/>
<Employees EmployeeID="9" FirstName="Anne" LastName="Dodsworth"/>
转换和层次结构
尽管上例中的区别较小，但与用于联接表的一个查询一起使用时，AUTO和RAW之间的区别比较明显。无论数据是来自一个表还是来自多个表，使用FOR XML RAW的查询都将只返回<row>元素。因此，RAW模式不利用XML文档的固有分层结构。请看下面的SQL语句：
本例将检索一种一对多父子关系。若执行该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>
该XML的可读性大大提高，因为它使用表名来作为元素名。它所包含的数据也比较少，因为它不重复每个定单元素的CustomerID和CompanyName属性，而FOR XML RAW例子则不然。
如果您更喜欢将列值表示为元素而不是属性，则您就会很幸运。通过在FOR XML子句中指定ELEMENTS选项，所有列值都将成为XML中的元素。有时百闻不如一见，因此本文在图 3中给出了当ELEMENTS条件适用时前面查询的输出。（为了简便起见，给出的XML示例仅包含CustomerID ALFKI的XML。这些查询所生成的实际XML会包含所有客户及其定单的XML。）使用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选项的查询如下：
该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还被包装在一个<root>元素中。
图 5所示的GetFORXML方法代码说明了如何使用标准SqlCommand对象来执行包含FOR XML子句的SQL语句。这里使用了ExecuteXmlReader方法，因此可以将XML作为一个XmlReader对象返回。数据集的ReadXml方法接受XmlReader，其第二个参数表明该XML是一个片段。
图 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代码即可完成这一任务：
使用FOR XML时，还必须说明计算列。尽管FOR XML支持计算列，但仍需确保对这些列进行命名。当FOR XML试图创建每一列的属性时，就会出现问题。属性名是通过使用关联的列名创建的。因此如果该列是一个计算列且没有别名，则SQL Server将停滞不前。简单的办法是确保给计算列取个别名，如下面的SQL语句及其XML结果所示：
<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
对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表选择该行集合，或插入到其中。
通过OPENXML插入
现在我们将用下面的XML示例从中插入一个定单和两个定单细节行：
<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所示的存储过程，并使用sp_xml_preparedocument系统存储过程准备之。然后启动一个事务来包装INSERT语句，以便插入一个定单及其子记录。这样便可以在部分事务失败时回滚事务。接下来，使用在Customer\Order节点开始的OPENXML打开XML文档。利用WITH子句，将XML文档返回到Order元素（Customer 元素）的父节点，然后查看Customer元素的CustomerID属性值，从而获得CustomerID。这是OPENXML函数的一个重要功能，因为它让用户使用受限的XPath表达式来遍历XML文档，从而获得属性和元素值。
图 7 插入父定单及其子定单
插入Order后，我们获取由内置的SQL Server SCOPE_IDENTITY函数刚刚生成的OrderID值。然后我们使用另一个INSERT语句（使用OPENXML函数从XML数据中获取Order Details）继续插入Order Details行。只要不出现错误，定单及其子定单细节行就被插入到其各自的数据库表中。
SCOPE_IDENTITY方法使用单个Order及其子定单。不过，以一个XML批插入多个Order及其子定单的情况比较复杂。问题就是在具有多个Order记录的情况下，仍要能够将适当的定单映射到其子定单。由于不知道要关联哪些行，因此必须添加一些代码来处理这一问题。可以在WITH子句中使用@mp:id/@mp:parentid元属性来提供一种获取父定单的新OrderID并将其映射到其子定单的OrderID字段的方法。
插入和更新
由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进行插入和更新
通过OPENXML批量插入
通过DataAdapter和存储过程从数据集插入10行非常容易。但DataAdapter会一次一个地遍历数据集行来查找行状态为已插入的行，并执行与DataAdapter针对每一行的InsertCommand关联的存储过程。这意味着10次插入将导致对数据库的10次调用。当必要的更新较少时，这种ADO.NET代码和数据库之间的来回操作几乎不会引起什么明显变化。不过，当引入其他因素时，如并发用户数量大大增加或插入数百行时，性能可能会迅速降低。要插入100行，不必调用100次存储过程，而可以将这100行作为XML一次性传递到一个存储过程中。
与可下载的代码一起提供的示例ASP.NET应用程序将执行一个将n个客户插入到Customers表中的存储过程。ASP.NET代码在数据集中创建并添加100条客户记录。然后使用WriteXml方法输出XML数据，不包含其架构。该XML然后被传递给一个一次性插入100个客户的存储过程。与每插入一次便执行一个存储过程的一次一个地插入各个客户相比，这种插入批量数据的方法更为高效。
使用示例
若使用SQL Query Analyzer来测试该列中包含的FOR XML SQL，可能需要更改某些默认选项。例如，若要查询和返回XML，则要先增加结果窗格中每列的最大字符数。转到Options | Tools | Results，将Maximum Characters per Column设置增加到某一值，如4096。由于返回的XML在结果窗格中显示为单列，因此该XML不会在默认的256个字符处截止。最后，用户将能够看到该XML，如果愿意，还可以将它复制并粘贴到XML编辑器中。
结束语
XML和SQL Server集成的第一步是引入FOR XML和OPENXML功能。由于这种进步，我们能够直接从SQL Server数据库获取XML，而不必使用某些中间组件对其进行转换。我们还可以通过结合使用OPENXML函数和老式备用方法、SQL INSERT、UPDATE和DELETE语句，将XML返给数据库。这些XML功能只是SQL Server 2005将提供的XML收集支持中的第一步，但它们如今已可用于集成XML应用程序和SQL Server，是一些非常有效的工具。
|
设为首页
收藏本站
