模板
教程
环境
性能
功能
管理
软件测试 > 测试开发技术 > 软件测试开发语言 > 数据库 > SQL Server >
SQL Server 2005 XML最佳实施策略用法
加载 XML 数据 将 XML 数据从 SQL Server 2000 传输到 SQL Server 2005 可以用多种方式将 XML 数据传输到 SQL Server 2005。在下一节中，我们将讨论几种方案。 如果您将数据存储在 SQL Server 2000 数据库的 [n]text 或图像列中，可以使用 DTS 等将表导入到 S
加载 XML 数据 将 XML 数据从 SQL Server 2000 传输到 SQL Server 2005
可以用多种方式将 XML 数据传输到 SQL Server 2005。在下一节中，我们将讨论几种方案。
• 如果您将数据存储在 SQL Server 2000 数据库的 [n]text 或图像列中，可以使用 DTS 等将表导入到 SQL Server 2005 数据库 中。使用 ALTER TABLE 语句将列类型更改为 XML。
• 可以使用 bcp out 批量复制 SQL Server 2000 中的数据，使用 bcp in 将数据批量插入到 SQL Server 2005 数据库中。
• 如果您将数据存储在 SQL Server 2000 数据库的关系列中，请创建一个带有一个 ntext 列的新表，同时根据需要在该表中创建一个主键列以用作行标识符。使用客户端编程检索在服务器中通过 FOR XML 生成的 XML，并且将其写入 ntext 列。然后，使用上述技巧将数据传输到 SQL Server 2005 数据库。您可以选择将 XML 直接写入 SQL Server 2005 数据库的 XML 列中。
示例：将列类型更改为 XML
假设您需要将表 R 中的 [n]text 或图像列 XYZ 的类型更改为非类型化 XML。下面的语句可执行此类更改：
ALTER TABLE R ALTER COLUMN XYZ XML
• 如果需要，可以通过指定一个 XML 架构集合将目标类型化为 XML。
批量加载 XML 数据
可以使用 SQL Server 中的批量加载功能（如 bcp），将 XML 数据批量加载到服务器中。通过 OPENROWSET 可以将文件中的数据加载到 XML 列中。下面的示例阐明了这一点。
示例：从文件中加载 XML
该示例说明了如何在表 T 中插入行。XML 列的值作为 CLOB 从文件 C:\yukon\xmlfile.xml 中加载，并且整数列被提供了值 10。
INSERT INTO T SELECT 10, xCol FROM (SELECT * FROM OPENROWSET (BULK 'C:\Yukon\xmlfile.xml', SINGLE_CLOB) AS xCol) AS R(xCol)
SQL Server 2005 用 Unicode (UTF-16) 存储 XML 数据。从服务器中检索的 XML 数据采用 UTF-16 编码；如果您需要不同的编码，则需要对检索到的数据执行必要的转换。有时，您可能拥有采用不同编码的 XML 数据，因此在数据加载过程中需要非常小心：
• 如果文本 XML 采用 Unicode (UCS-2, UTF-16)，则将其赋给 XML 列、变量或参数不会带来任何问题。
• 如果编码不是 Unicode 并且是隐式的（由于源代码页），则数据库中的字符串代码页应该与要加载的代码点相同或兼容（必要时使用 COLLATE）。如果不存在这样的服务器代码页，则您必须添加带有正确编码的显式 XML 声明。
• 要使用显式编码，请使用 varbinary() 类型（它不与代码页交互）或者使用适当代码页的字符串类型。然后，将该数据赋给 XML 列、变量或参数。
示例：显式指定编码
假设您具有的 XML 文档 (vcdoc) 被存储为没有显式 XML 声明的 varchar(max)。下面的语句将添加一个带有编码"iso8859-1"的 XML 声明，将 XML 文档串连起来，将结果转换为 varbinary(max) 以便保留字节表示形式，最后将其转换为 XML。这使 XML 处理器能够按照指定的编码"iso8859-1"来分析数据，并为字符串值生成相应的 UTF-16 表示形式。
SELECT CAST( CAST (('<?xml version="1.0" encoding="iso8859-1"?>'+ vcdoc) AS VARBINARY (MAX)) AS XML)
嵌入到 T-SQL 中的 XQuery (http://www.w3.org/TR/xquery/) 语言支持查询 XML 数据类型。该语言正在由 WWW 联合会 (W3C) 进行开发（在本文作者最后一次召集起来撰写本文时），并且所有主要数据库供应商（包括 Microsoft）都参与了开发工作。它包括了 XPath 2.0 作为导航语言。同时，还提供了针对 XML 数据类型的数据修改语言构造。有关 SQL Server 2005 中支持的 Xquery 构造、函数和运算符的信息，请参阅联机图书。
错误模型
具有语法错误的 Xquery 表达式和 XML DML 语句会返回编译错误。编译阶段会检查 XQuery 表达式和 DML 语句的静态类型正确性，并且对于类型化 XML 使用 XML 架构进行类型推理。如果某个表达式可能在运行时由于类型安全冲突而失败，它会引发静态类型错误。静态错误的例子有将字符串添加到整数以及在不存在的节点中查询类型化数据。
与 W3C 标准有所不同的是，XQuery 运行时错误被转换为可以作为空 XML 或 NULL 传播给查询结果的空序列（具体取决于调用上下文）。
通过显式转换到正确的类型，用户可以避免静态错误，尽管运行时转换错误将被转化为空序列。
下面的小节将详细讨论类型检查。
唯一性检查
如果编译器无法确定能否在运行时保证唯一性，则要求唯一性的定位步骤、函数参数和运算符（例如 eq）将返回错误。问题经常出现在非类型化数据上。例如，属性查找要求存在唯一的父元素；能够选择单个父节点的序号即可满足需要。计算 node()-value() 组合（请参阅 Value()、Nodes() 和 OpenXML()）以提取属性值，这可能不需要指定序号，如下面的示例所示。
示例：已知的唯一性
在该示例中，nodes() 方法为每个 元素生成一个单独的行。（有关 nodes() 方法的详细说明，请参阅 Value()、Nodes() 和 OpenXML()）。在 节点上进行求值的 value() 方法会提取 @genre（它作为属性具有唯一性）的值。
SELECT nref.value('@genre', 'varchar(max)') LastName FROM T CROSS APPLY xCol.nodes('//book') AS R(nref)
对于类型检查，需要记住 //first-name[1] 和 (//first-name)[1] 之间的差别。前者返回 节点的序列，其中每个节点是其同辈节点中最左边的 节点。后者返回 XML 实例中按照文档顺序的第一个唯一的 节点。
示例：value() 的用法
下面这个对非类型化 XML 列执行的查询会导致静态、编译错误，因为 value() 需要将一个唯一性节点作为第一个参数，但编译器无法确定在运行时是否将只出现一个 节点：
SELECT xCol.value('//author/last-name', 'nvarchar(50)') LastName FROM T
SELECT xCol.value('//author/last-name[1]', 'nvarchar(50)') LastName FROM T
但是，这不会纠正该错误，因为在每个 XML 实例中都可能出现多个 节点。下面的改写方式将会有效：
父轴
如果节点的类型无法确定，则它将成为 anyType，后者不会隐式转换为任何其他类型。在使用父轴（例如，xCol.query('/book/@genre/../price')）进行导航的过程中，尤其会发生这种情况；该父节点类型被确定为 anyType。元素也可能被定义为 XML 架构中的 anyType。在这两种情况下，丢失更为精确的类型信息通常会导致静态类型错误，并且要求将原子值显式转换为它们的特定类型。
Data()、Text() 和 String() 访问器
XQuery 具有一个可从节点中提取标量的、类型化值的函数 fn:data()，一个可返回文本节点的节点测试 text()，以及可返回节点的字符串值的函数 fn:string()。它们的用法有时会引起混乱。下面是有关在 SQL Server 2005 中正确使用它们的准则。请考虑 XML 实例 12：
• 非类型化 XML：路径表达式 /age/text() 返回文本节点"12"。函数 fn:data(/age) 返回字符串值"12"，fn:string(/age) 也是如此。
• 类型化 XML：对于任何简单的类型化 元素，表达式 /age/text() 都会返回静态错误。另一方面，fn:data(/age) 返回整数 12，而 fn:string(/age) 会产生字符串"12"。
联合类型的函数和运算符
由于类型检查，联合类型要求进行认真的处理。以下示例阐述了其中两个问题。
示例：联合类型上的函数
请考虑以下联合类型的 的元素定义
在 XQuery 上下文中，"average"函数 fn:avg (//r) 会返回静态错误，因为 XQuery 编译器无法对 fn:avg() 的参数中元素的不同类型（xs:int、xs:float 或 xs:double）的值求和。为解决该问题，请将函数调用改写为 fn:avg(for $r in //r return $r cast as xs:double ?)。
示例：联合类型上的运算符
加法运算"+"要求精确的操作数类型，以至于表达式 (//r)[1] + 1 对上述元素 的类型定义返回静态错误。可以解决该问题的一种改写方式是 (//r)[1] cast as xs:int?+1，其中"?"表示具体取值 0 或 1。SQL Server 2005 要求带有"?"的"cast as"，因为任何转换都会由于运行时错误而产生空序列。
Value()、Nodes() 和 OpenXML()
可以在 SELECT 子句中对 XML 数据类型使用多个 value() 方法来生成提取值的行集。nodes() 方法会为所选的每个节点生成一个内部引用，以用于进一步查询。当行集具有多个列，并且用于生成行集的路径表达式可能比较复杂时，将 nodes() 和 value() 方法组合使用可能会更为有效。
nodes() 方法可生成特殊 XML 数据类型的实例，每个实例都将其上下文设置为所选的不同节点。此类 XML 实例支持 query()、value()、nodes() 和 exist() 方法，并且可用在 count(*) 聚合中。所有其他用法都会导致错误。
示例：nodes() 的用法
假设您希望提取名字不是"David"的作者的姓名，作为由两个列（FirstName 和 LastName）组成的行集。使用 nodes() 和 value() 方法可以达到此目的，如下所示：
在该示例中，nodes('//author') 会生成一个由对每个 XML 实例的 元素的引用组成的行集。通过相对于这些引用对 value() 方法求值，可以获取作者的名字和姓氏。
SQL Server 2000 提供了使用 OpenXml() 从 XML 实例生成行集的功能。您可以指定行集的关系架构，并指定 XML 实例内部的值如何映射到该行集中的列。
示例：对 XML 数据类型使用 OpenXml()
我们可以像下面显示的那样，使用 OpenXml() 来改写上一示例中的查询，方法是：创建一个游标，将各个 XML 实例读入一个 XML 变量，然后向其应用 OpenXML()：
penXml() 会创建内存中的表示形式，并使用工作表而不是查询处理器。它依赖于 MSXML 3.0 的 XPath 1.0 处理器而不是 XQuery 引擎。工作表不在对 OpenXml() 的多个调用中共享（即使是在同一个 XML 实例上）。这限制了它的可伸缩性。在未指定 WITH 子句时，可以通过 OpenXml() 来访问 XML 数据的边缘表格式。而且，还可以通过它使用 XML 值在单独的"溢出"列中的剩余部分。
nodes() 和 value() 函数的组合可以有效地使用 XML 索引。因此，这一组合可以表现出比 OpenXml 更高的可伸缩性。
SELECT xCol.value('(//author/last-name)[1]', 'nvarchar(50)') LastName FROM T This query returns the value of the first element in each XML instance.
如果节点的类型无法确定，则它将成为 anyType，后者不会隐式转换为任何其他类型。在使用父轴（例如，xCol.query('/book/@genre/../price')）进行导航的过程中，尤其会发生这种情况；该父节点类型被确定为 anyType。元素也可能被定义为 XML 架构中的 anyType。在这两种情况下，丢失更为精确的类型信息通常会导致静态类型错误，并且要求将原子值显式转换为它们的特定类型。
Data()、Text() 和 String() 访问器
XQuery 具有一个可从节点中提取标量的、类型化值的函数 fn:data()，一个可返回文本节点的节点测试 text()，以及可返回节点的字符串值的函数 fn:string()。它们的用法有时会引起混乱。下面是有关在 SQL Server 2005 中正确使用它们的准则。请考虑 XML 实例 12：
• 非类型化 XML：路径表达式 /age/text() 返回文本节点"12"。函数 fn:data(/age) 返回字符串值"12"，fn:string(/age) 也是如此。
• 类型化 XML：对于任何简单的类型化 元素，表达式 /age/text() 都会返回静态错误。另一方面，fn:data(/age) 返回整数 12，而 fn:string(/age) 会产生字符串"12"。
联合类型的函数和运算符
由于类型检查，联合类型要求进行认真的处理。以下示例阐述了其中两个问题。
示例：联合类型上的函数
请考虑以下联合类型的 的元素定义
<xs:element name="r"> <xs:simpleType> <xs:union memberTypes="xs:int xs:float xs:double"/> </xs:simpleType> </xs:element>
示例：联合类型上的运算符
加法运算"+"要求精确的操作数类型，以至于表达式 (//r)[1] + 1 对上述元素 的类型定义返回静态错误。可以解决该问题的一种改写方式是 (//r)[1] cast as xs:int?+1，其中"?"表示具体取值 0 或 1。SQL Server 2005 要求带有"?"的"cast as"，因为任何转换都会由于运行时错误而产生空序列。
Value()、Nodes() 和 OpenXML()
可以在 SELECT 子句中对 XML 数据类型使用多个 value() 方法来生成提取值的行集。nodes() 方法会为所选的每个节点生成一个内部引用，以用于进一步查询。当行集具有多个列，并且用于生成行集的路径表达式可能比较复杂时，将 nodes() 和 value() 方法组合使用可能会更为有效。
nodes() 方法可生成特殊 XML 数据类型的实例，每个实例都将其上下文设置为所选的不同节点。此类 XML 实例支持 query()、value()、nodes() 和 exist() 方法，并且可用在 count(*) 聚合中。所有其他用法都会导致错误。
示例：nodes() 的用法
假设您希望提取名字不是"David"的作者的姓名，作为由两个列（FirstName 和 LastName）组成的行集。使用 nodes() 和 value() 方法可以达到此目的，如下所示：
SELECT nref.value('first-name[1]', 'nvarchar(50)') FirstName, nref.value('last-name[1]', 'nvarchar(50)') LastName FROM T CROSS APPLY xCol.nodes('//author') AS R(nref) WHERE nref.exist('.[first-name != "David"]') = 1
SQL Server 2000 提供了使用 OpenXml() 从 XML 实例生成行集的功能。您可以指定行集的关系架构，并指定 XML 实例内部的值如何映射到该行集中的列。
示例：对 XML 数据类型使用 OpenXml()
我们可以像下面显示的那样，使用 OpenXml() 来改写上一示例中的查询，方法是：创建一个游标，将各个 XML 实例读入一个 XML 变量，然后向其应用 OpenXML()：
DECLARE name_cursor CURSOR FOR SELECT xCol FROM T OPEN name_cursor DECLARE @xmlVal XML DECLARE @idoc int FETCH NEXT FROM name_cursor INTO @xmlVal WHILE (@@FETCH_STATUS = 0) BEGIN EXEC sp_xml_preparedocument @idoc OUTPUT, @xmlVal SELECT * FROM OPENXML (@idoc, '//author') WITH (FirstName varchar(50) 'first-name', LastName varchar(50) 'last-name') R WHERE R.FirstName != 'David' EXEC sp_xml_removedocument @idoc FETCH NEXT FROM name_cursor INTO @xmlVal END CLOSE name_cursor DEALLOCATE name_cursor
nodes() 和 value() 函数的组合可以有效地使用 XML 索引。因此，这一组合可以表现出比 OpenXml 更高的可伸缩性。
使用 FOR XML 从行集中生成 XML
通过新的 TYPE 指令，可以使用 FOR XML 从行集中生成 XML 数据类型实例。
可以将结果赋给 XML 数据类型列、变量或参数。而且，可以将 FOR XML 嵌套以便生成任意层次结构。这使得嵌套的 FOR XML 比 FOR XML EXPLICIT 更加便于编写，但是对于较深的层次结构，它的性能可能不太好。FOR XML 还引入了新的 PATH 模式，该模式指定列的值应该出现在 XML 树中的哪个路径。
可以使用新的 FOR XML TYPE 指令，通过 SQL 语法来定义关系数据上的只读 XML 视图。可以通过 SQL 语句和嵌入式 XQuery 来查询该视图，如下面的示例所示。例如，您可以在存储过程中引用此类 SQL 视图。
示例：返回生成的 XML 数据类型的 SQL 视图
下面的 SQL 视图定义可在一个关系列 (pk) 以及从一个 XML 列中检索到的书籍作者上创建一个 XML 视图：
视图 V 包含一个行，该行只有一个列：XML 类型的 xmlValtype。可以像查询常规的 XML 数据类型实例那样查询它。例如，下面的查询将返回名字为"David"的作者：
SQL 视图定义在某种程度上类似于使用带有批注的架构创建的 XML 视图。然而，二者之间存在重要的区别。SQL 视图定义是只读的，并且必须通过嵌入式 XQuery 来操作；而使用带有批注的架构的 XML 视图则不是这样。而且，SQL 视图在应用 XQuery 表达式之前生成 XML 结果，而 XML 视图上的 XPath 查询在基础表上计算 SQL 查询。
添加业务逻辑
可以用多种方式将业务逻辑添加到 XML 数据中：
• 您可以编写行或列约束，在插入和修改 XML 数据的过程中实施特定于域的约束。
• 您可以在 XML 列上编写相应的触发器，使其当您在该列中插入或更新值时引发。该触发器可以包含特定于域的验证规则，或者填充属性表。
• 可以使用托管代码编写 SQLCLR 函数并向其传递 XML 值，并且使用由 System.Xml 命名空间提供的 XML 处理功能。这方面的一个例子是将 XSL 转换应用于 XML 数据，如下所示。您还可以将 XML 反序列化为一个或多个托管类，并且使用托管代码来操作它们。
• 您可以编写 T-SQL 存储过程和函数，激活 XML 列上的处理以满足您的业务需要。
示例：应用 XSL 转换
考虑 CLR 函数 TransformXml()，它接受一个 XML 数据类型实例和一个存储在文件中的 XSL 转换，将该转换应用于 XML 数据，并且在结果中返回转换后的 XML。用 C# 编写的主干函数如下所示：
在注册该程序集，并且创建了对应于 TransformXml() 的用户定义 T-SQL 函数 SqlXslTransform() 之后，就可以像在下面的查询中那样从 T-SQL 中调用该函数：
查询结果包含转换后的 XML 的行集。
SQLCLR 打开了一个全新的世界，可以使用它将 XML 数据分解到表或属性提升中，并使用 System.Xml 命名空间中的托管类来查询 XML 数据。有关详细信息，请参阅 SQL Server 2005 和 Microsoft Visual Studio"Whidbey"联机图书。
通过新的 TYPE 指令，可以使用 FOR XML 从行集中生成 XML 数据类型实例。
可以将结果赋给 XML 数据类型列、变量或参数。而且，可以将 FOR XML 嵌套以便生成任意层次结构。这使得嵌套的 FOR XML 比 FOR XML EXPLICIT 更加便于编写，但是对于较深的层次结构，它的性能可能不太好。FOR XML 还引入了新的 PATH 模式，该模式指定列的值应该出现在 XML 树中的哪个路径。
可以使用新的 FOR XML TYPE 指令，通过 SQL 语法来定义关系数据上的只读 XML 视图。可以通过 SQL 语句和嵌入式 XQuery 来查询该视图，如下面的示例所示。例如，您可以在存储过程中引用此类 SQL 视图。
示例：返回生成的 XML 数据类型的 SQL 视图
下面的 SQL 视图定义可在一个关系列 (pk) 以及从一个 XML 列中检索到的书籍作者上创建一个 XML 视图：
CREATE VIEW V (xmlVal) AS SELECT pk, xCol.query('/book/author') FROM T FOR XML AUTO, TYPE
SELECT xmlVal.query('//author[first-name = "David"]') FROM V
添加业务逻辑
可以用多种方式将业务逻辑添加到 XML 数据中：
• 您可以编写行或列约束，在插入和修改 XML 数据的过程中实施特定于域的约束。
• 您可以在 XML 列上编写相应的触发器，使其当您在该列中插入或更新值时引发。该触发器可以包含特定于域的验证规则，或者填充属性表。
• 可以使用托管代码编写 SQLCLR 函数并向其传递 XML 值，并且使用由 System.Xml 命名空间提供的 XML 处理功能。这方面的一个例子是将 XSL 转换应用于 XML 数据，如下所示。您还可以将 XML 反序列化为一个或多个托管类，并且使用托管代码来操作它们。
• 您可以编写 T-SQL 存储过程和函数，激活 XML 列上的处理以满足您的业务需要。
示例：应用 XSL 转换
考虑 CLR 函数 TransformXml()，它接受一个 XML 数据类型实例和一个存储在文件中的 XSL 转换，将该转换应用于 XML 数据，并且在结果中返回转换后的 XML。用 C# 编写的主干函数如下所示：
public static SqlXml TransformXml (SqlXml XmlData, string xslPath) { // Load XSL transformation XslTransform xform = new XslTransform(); XPathDocument xslDoc = new XPathDocument (xslPath); xform.Load (xslDoc.CreateNavigator(),null); // Load XML data XPathDocument xDoc = new XPathDocument (XmlData.CreateReader()); XPathNavigator nav = xDoc.CreateNavigator (); // Return the transformed value SqlXml retSqlXml = new SqlXml (xform.Transform(nav, null)); return (retSqlXml); }
SELECT SqlXslTransform (xCol, 'C:\yukon\xsltransform.xsl') FROM T WHERE xCol.exist('/book/title/text()[contains(.,"custom")]') =1
SQLCLR 打开了一个全新的世界，可以使用它将 XML 数据分解到表或属性提升中，并使用 System.Xml 命名空间中的托管类来查询 XML 数据。有关详细信息，请参阅 SQL Server 2005 和 Microsoft Visual Studio"Whidbey"联机图书。
