Go homepage(回首页)
Upload pictures (上传图片)
Write articles (发文字帖)

The author:(作者)归海一刀
published in(发表于) 2014/2/1 0:10:56
Sql，server，2005的XML最佳实施策略(4)_[SQL，Server教程]

在 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() 方法可以达到此目的，如下所示：

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

在该示例中，nodes('//author') 会生成一个由对每个 XML 实例的 元素的引用组成的行集。通过相对于这些引用对 value() 方法求值，可以获取作者的名字和姓氏。

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

OpenXml() 会创建内存中的表示形式，并使用工作表而不是查询处理器。它依赖于 MSXML 3.0 的 XPath 1.0 处理器而不是 XQuery 引擎。工作表不在对 OpenXml() 的多个调用****享（即使是在同一个 XML 实例上）。这限制了它的可伸缩性。在未指定 WITH 子句时，可以通过 OpenXml() 来访问 XML 数据的边缘表格式。而且，还可以通过它使用 XML 值在单独的"溢出"列中的剩余部分。

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 视图：

CREATE VIEW V (xmlVal) AS

SELECT pk, xCol.query('/book/author')

FROM T

FOR XML AUTO, TYPE

视图 V 包含一个行，该行只有一个列：XML 类型的 xmlValtype。可以像查询常规的 XML 数据类型实例那样查询它。例如，下面的查询将返回名字为"David"的作者：

SELECT xmlVal.query('//author[first-name = "David"]')

FROM V

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# 编写的主干函数如下所示：

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);
}

在注册该程序集，并且创建了对应于 TransformXml() 的用户定义 T-SQL 函数 SqlXslTransform() 之后，就可以像在下面的查询中那样从 T-SQL 中调用该函数：

SELECT SqlXslTransform (xCol, 'C:\yukon\xsltransform.xsl')

FROM T

WHERE xCol.exist('/book/title/text()[contains(.,"custom")]') =1

查询结果包含转换后的 XML 的行集。

SQLCLR 打开了一个全新的世界，可以使用它将 XML 数据分解到表或属性提升中，并使用 System.Xml 命名空间中的托管类来查询 XML 数据。有关详细信息，请参阅 SQL Server 2005 和 Microsoft Visual Studio"Whidbey"联机图书。

返回页首

跨域查询

当您的数据同时驻留在关系列和 XML 数据类型列中时，您可能希望编写将关系数据处理和 XML 数据处理结合起来的查询。例如，您可以使用 FOR XML 将关系列和 XML 列中的数据转换为 XML 数据类型实例，然后使用 XQuery 对其进行查询。相反，可以从 XML 值生成行集（请参阅用法），并使用 T-SQL 对其进行查询。

编写跨域查询的更为方便和有效的方法是在 XQuery 或 XML DML 表达式内使用 SQL 变量或列的值：

• 可以在 XQuery 或 XML DML 表达式中，通过 sql:variable() 来使用 SQL 变量的值。

• 可以在 XQuery 或 XML DML 表达式中，通过 sql:column() 来使用关系列中的值。

• 该方法使应用程序可以将查询参数化，如下面的示例所示。然而，不允许在 sql:variable() 和 sql:column() 中使用 XML 和用户定义的类型。

示例：使用 sql:variable() 的跨域查询

下面的查询是对示例：基于 XML 数据类型方法的计算列上的查询中显示的查询进行修改后得到的版本。在该版本中，使用 SQL 变量 @isbn 传入感兴趣的 ISBN。通过将常量替换为 sql:variable()，可以使用该查询来搜索任意 ISBN，而不仅是其 ISBN 为 0-7356-1588-2 的那个。

DECLARE @isbn varchar(20)

SET @isbn = '0-7356-1588-2'

SELECT xCol

FROM T

WHERE xCol.exist ('/book[@ISBN = sql:variable("@isbn")]') = 1

可以用类似的方式使用 Sql:column()，并且提供附加的好处。可以使用列上的索引来提高效率，这要由基于成本的查询优化器决定。而且，计算列可以存储提升的属性，如基于 XML 数据类型的计算列中所述。

用于原生 XML 支持的目录视图

• 目录视图的目的是提供与 XML 用法有关的元数据信息。下面讨论了其中几个目录视图。

XML 索引

XML 索引项出现在目录视图 sys.indexes 中，索引"type"为 3。"name"列包含 XML 索引的名称。

XML 索引还被记录在目录视图 sys.xml_indexes 中，它包含 sys.indexes 的所有列以及一些对 XML 索引有意义的特殊列。列"secondary_type"中的值 NULL 表示主 XML 索引；值"P"、"R"和"V' "分别代表 PATH、PROPERTY 和 VALUE 辅助 XML 索引。

XML 索引的空间利用率可以在表值函数 sys.fn_indexinfo() 中找到。该函数会提供许多信息，例如，所占用的磁盘页数、平均行大小（字节）、记录数以及所有索引类型（包括 XML 索引）的其他信息。对于每个数据库分区都会提供这些信息；XML 索引使用基表的相同分区方案和分区函数。

示例：XML 索引的空间利用率

SELECT sum(Pages)

FROM sys.fn_indexinfo ('T', 'idx_xCol_Path' , DEFAULT, 'DETAILED')

这会产生表 T 中的 XML 索引 idx_xCol_Path 在所有分区中占用的磁盘页数。如果不使用 sum() 函数，结果将返回每个分区的磁盘页利用率。

检索 XML 架构集合

XML 架构集合在目录视图 sys.xml_schema_collections 中被枚举。XML 架构集合"sys"由系统定义，它包含无须显式加载就可在所有用户定义的 XML 架构集合中使用的预定义命名空间。该列表包含 xml、xs、xsi、fn 和 xdt 的命名空间。其他两个值得一提的目录视图是：sys.xml_schema_namespaces，它枚举了每个 XML 架构集合中的所有命名空间；sys.xml_components，它枚举了每个 XML 架构中的所有 XML 架构组件。

内置的函数 XML_SCHEMA_NAMESPACE(schemaName, XmlSchemacollectionName, namespace-uri) 可产生一个 XML 数据类型实例，该实例包含 XML 架构集合中所含架构（预定义的 XML 架构除外）的 XML 架构片段。

可以用下列方式来枚举 XML 架构集合的内容：

• 在 XML 架构集合的适当目录视图上编写 T-SQL 查询。

• 使用内置函数 XML_SCHEMA_NAMESPACE()。可以在该函数的输出上应用 XML 数据类型方法。然而，您无法修改基础 XML 架构。

• 下面的示例阐述了这些概念。

示例：枚举 XML 架构集合中的 XML 命名空间

对于 XML 架构集合"myCollection"使用以下查询：

SELECT XSN.name

FROM sys.xml_schema_collections XSC JOIN sys.xml_schema_namespaces XSN

ON (XSC.xml_collection_id = XSN.xml_collection_id)

WHERE XSC.name = 'myCollection'

示例：枚举 XML 架构集合的内容

下面的语句枚举了关系架构 dbo 中的 XML 架构集合"myCollection"的内容。

SELECT XML_SCHEMA_NAMESPACE (N'dbo', N'myCollection')

通过将目标命名空间指定为 XML_SCHEMA_NAMESPACE() 的第三个参数，可以按 XML 数据类型实例的形式获取该集合中的单独 XML 架构，如下所示。

示例：输出 XML 架构集合中的指定架构

下面的语句从关系架构 dbo 中的 XML 架构集合"myCollection"中输出目标命名空间为"http://www.microsoft.com/books"的 XML 架构。

SELECT XML_SCHEMA_NAMESPACE (N'dbo', N'myCollection',

N'http://www.microsoft.com/books')

查询 XML 架构

如果您需要查询已经加载到 XML 架构集合中的 XML 架构，可以采用下列方式：

• 在 XML 架构命名空间的目录视图上编写 T-SQL 查询。

• 除了将 XML 架构加载到 XML 类型系统中以外，创建一个包含 XML 数据类型列的表来存储 XML 架构。您可以使用 XML 数据类型方法来查询 XML 列。而且，您可以在该列上生成 XML 索引。然而，需要由应用程序来维护存储在 XML 列中的 XML 架构与存储在 XML 类型系统中的 XML 架构之间的一致性。例如，如果您从 XML 类型系统中删除了 XML 架构命名空间，则还必须从表中删除该命名空间以保持一致性。

赞