ms sql serverxml 最佳实践数据库技巧.docx

1、ms sql serverxml 最佳实践数据库技巧MS SQL Server 2005 XML 最佳实践SQL Server 2005 为 XML 数据处理提供了广泛支持。可以将 XML 值以本机方式存储在 xml 数据类型列中，后者可以根据 XML 架构的集合进行类型化，也可以保持非类型化状态。可以对 XML 列创建索引。此外，通过使用 XQuery 和 XML DML 可支持精细的数据操作。XML DML 是针对数据修改的扩展。SQL Server 2000 和 SQLXML Web 版本提供了强大的 XML 数据管理功能。这些功能着重于关系数据和 XML 数据之间的映射。可以使用带批注

2、的 XSD (AXSD) 来定义关系数据的 XML 视图，以提供以 XML 为中心的方法，该方法支持对 XML 数据执行大容量数据加载、查询和更新功能。Transact-SQL 扩展提供了一个以 SQL 为中心的方法，以便使用 FOR XML 将关系查询结果映射到 XML，以及使用 OPENXML 从 XML 生成关系视图。这些支持已在 SQL Server 2005 中进行了扩展。结合新添加的本机 XML 支持，SQL Server 2005 提供了一个强大的平台，以针对半结构化和非结构化数据管理开发功能丰富的应用程序。本主题提供了 SQL Server 2005 中的 XML 数据建模和使

3、用准则，包含下列两个部分： 数据建模可以通过使用本机 xml 数据类型和拆分到表中的 XML，以多种方式在 SQL Server 2005 中存储 XML 数据。本主题提供了为对 XML 数据进行建模做出适当选择的准则。另外，还介绍了对 XML 数据创建索引、属性提升和 XML 实例的类型化。使用本部分讨论了与使用相关的主题，例如将 XML 数据加载到服务器和查询编译中的类型推理。本部分还解释和区分了密切相关的功能，并就如何适当使用这些功能提出了建议。这些均通过示例进行了说明。 数据建模本部分概述了应使用 SQL Server 2005 中的 XML 功能的理由，另外还提供了在本机 XML 存

4、储和 XML 视图技术之间进行选择的准则，并给出了数据建模建议。 关系或 XML 数据模型如果您的数据是高度结构化的，具有已知架构，则对于数据存储，关系模型可能最适用。SQL Server 提供了您可能需要的必要功能和工具。另一方面，如果结构是半结构化或非结构化的或者未知的，则必须考虑对这类数据进行建模。如果您需要一个与平台无关的模型，以便通过使用结构和语义标记来确保数据的可移植性，则 XML 是一个不错的选择。此外，下列情况下，适于做此选择： 您的数据为稀疏数据，或您不了解数据的结构，或数据结构将来可能会有重大变化。您的数据体现的是包容层次结构而不是在实体间的引用，并且可能是递归数据。您的数

5、据本身具有顺序性。您希望基于数据的结构查询数据或更新部分数据。如果上述条件均不满足，则应使用关系数据模型。例如，如果数据为 XML 格式，但应用程序只是使用数据库来存储和检索数据，则只需要 nvarchar(max) 列。将数据存储在 XML 列中还有其他好处，包括让引擎确定数据格式是否正确或有效，以及支持对 XML 数据进行精细查询和更新。在 SQL Server 2005 中存储 XML 数据的理由下面是一些使用 SQL Server 2005 中的本机 XML 功能而不是在文件系统中管理 XML 数据的理由：您希望以一种高效的事务处理方式来共享、查询和修改 XML 数据。精细的数据访问对

6、于您的应用程序而言很重要。例如，您可能需要提取 XML 文档中的某些部分，或者您可能需要插入新的部分而不是替换整个文档。您有关系数据和 XML 数据，希望在应用程序中进行关系数据和 XML 数据之间的互操作。您需要语言支持，以便对于跨域应用程序可以进行查询和数据修改。您希望服务器能够保证数据格式正确，并能够视情况根据 XML 架构来验证您的数据。您希望对 XML 数据创建索引以实现高效的查询处理和良好的可伸缩性，并使用一流查询优化器。您希望对 XML 数据进行 SOAP、ADO.NET 和 OLE DB 访问。您希望使用数据库服务器的管理功能来管理 XML 数据。例如，这可能是备份、恢复和复制

7、。如果上述条件均不满足，最好将数据存储为非 XML 大型对象类型，如 nvarchar(max) 或 varbinary(max)。XML 存储选项SQL Server 2005 中的 XML 存储选项包括： 采用 xml 数据类型的本机存储数据以保留数据的 XML 内容的内部表示形式进行存储，XML 内容包括包容层次结构、文档顺序、元素和属性值等。具体来说，就是保留 XML 数据的 InfoSet 内容。有关 InfoSet 的详细信息，请访问 http:/www.w3.org/TR/xml-infoset。InfoSet 内容并不是文本 XML 的精确副本，因为其中未保留下列信息：无关紧要

8、的空格、属性顺序、命名空间前缀和 XML 声明。对于类型化的 xml 数据类型（即绑定到 XML 架构的 xml 数据类型），后架构验证 InfoSet (PSVI) 将类型信息添加到 InfoSet，并以内部表示形式进行编码。这会显著提高分析速度。有关详细信息，请参阅 http:/www.w3.org/TR/xmlschema-1 和 http:/www.w3.org/TR/xmlschema-2 上的 W3C XML 架构规范。 在 XML 和关系存储之间映射 通过使用带批注的架构 (AXSD)，将 XML 分解到一个或多个表中的列。这可保留在关系级别上的数据保真度。因此，尽管忽略了元素间

9、的顺序，但仍保留了层次结构。架构不能是递归的。 大型对象存储 nvarchar(max) 和 varbinary(max) 存储数据的精确副本。这对于特殊用途的应用（如法律文档）很有用。大多数应用不需要完全相同的副本，且 XML 内容（InfoSet 保真度）即可满足需要。 通常，您必须结合使用这些方法。例如，您可能需要将 XML 数据存储在 xml 数据类型列中，并将其中的属性提升到关系列中。或者，您可能需要使用映射技术将非递归部分存储到非 XML 列中，而只将递归部分存储在 xml 数据类型列中。XML 技术的选择XML 技术（本机 XML 与 XML 视图）的选择通常取决于下列因素：存储

10、选项 您的 XML 数据可能更适于大型对象存储（例如，产品手册），或更适于存储在关系列中（例如，转换为 XML 的行项）。每个存储选项都在不同程度上保留文档保真度。 查询功能 您可能会发现，基于查询的特性和对 XML 数据进行查询的程度，某一个存储选项比其他选项更合适。在两个存储选项中，对 XML 数据的精细查询（例如，XML 节点上的谓词评估）得到不同程度的支持。 对 XML 数据创建索引 您可能希望对 XML 数据创建索引以提高 XML 查询的性能。索引选项随存储选项的不同而不同，您必须做出适当的选择才能优化工作负荷。 数据修改功能 某些工作负荷涉及对 XML 数据进行精细修改。例如，在文

11、档中添加新的部分，而其他工作负荷（如 Web 内容）则不涉及。对于您的应用程序来说，数据修改语言支持可能很重要。架构支持 您的 XML 数据可通过架构进行说明，该架构可能是 XML 架构文档，也可能不是。对架构绑定的 XML 的支持取决于 XML 技术。 此外，不同的选择具有不同的性能特征。本机 XML 存储您可以将 XML 数据存储在服务器上的 XML 数据类型列中。下列情况下，适于做此选择： 您希望使用一种简单的方法将 XML 数据存储在服务器上，同时保留文档顺序和文档结构。 您可能有对应于您的 XML 数据的架构，也可能没有。您希望查询和修改 XML 数据。您希望对 XML 数据创建索引

12、，以提高查询处理的速度。您的应用程序需要系统目录视图以管理您的 XML 数据和 XML 架构。如果您的 XML 文档具有多种结构，或您的 XML 文档符合不同的或复杂的架构，而这些架构很难映射到关系结构，本机 XML 存储很有用。 示例：使用 xml 数据类型对 XML 数据进行建模例如有一个 XML 格式的产品手册，其中每个主题对应单独的一章，而每章中又包含多节。一节可以包含多个小节。因此， 是一个递归元素。产品手册包含大量混合内容、关系图和技术材料；数据是半结构化的。用户可能希望对感兴趣的主题执行上下文搜索，例如，在有关“索引”的章中搜索有关“聚集索引”的节，并查询技术数量。 适于您的 X

13、ML 文档的存储模型是 xml 数据类型列。这可保留 XML 数据的 InfoSet 内容。对 XML 列创建索引有利于提高查询性能。示例：保留 XML 数据的精确副本为了进行说明，假定政府条例要求您保留 XML 文档的精确文本副本。例如，这些文档可能为签署的文档、法律文档或股票交易单。您可能希望将文档存储在 nvarchar(max) 列中。 查询时，先在运行时将数据转换为 xml 数据类型，然后对其执行 Xquery。运行时转换开销可能很高，尤其是在文档较大的情况下。如果经常查询，可以采取冗余方式将文档存储在 xml 数据类型列中，并对其创建索引，同时从 nvarchar(max) 列中返

14、回精确的文档副本。XML 列可以是基于 nvarchar(max) 列的计算列。但不能对 XML 计算列创建 XML 索引，也不能对 nvarchar(max) 或 varbinary(max) 列创建 XML 索引。 XML 视图技术通过定义 XML 架构和数据库中的表之间的映射，可以创建持久性数据的“XML 视图”。通过 XML 视图，可使用 XML 大容量加载来填充基础表。您可以使用 XPath 1.0 版来查询 XML 视图；这种查询将被转换为针对表的 SQL 查询。与此类似，更新也会被传播到那些表。 在下列情况下，此技术很有用： 您希望拥有以 XML 为中心的编程模型，该模型使用现有

15、关系数据上的 XML 视图。您有对应于您的 XML 数据的架构（XSD、XDR），该架构可能由外部伙伴提供。数据中的顺序并不重要，或查询表数据不是递归的，或事先已知最大递归深度。您希望使用 XPath 1.0 版通过 XML 视图查询和修改数据。您希望通过 XML 视图来大容量加载 XML 数据，并将其分解到基础表。 相关示例包括显示为用于数据交换和 Web 服务的 XML 的关系数据。有关详细信息，请参阅 MSDN Online Library。示例：使用带批注的 XML 架构 (AXSD) 对数据进行建模为了进行说明，假定您具有希望将其作为 XML 处理的关系数据（如客户、订单和行项）。请

16、使用 AXSD 在关系数据上定义 XML 视图。通过使用 XML 视图可以将 XML 数据大容量加载到表，以及使用 XML 视图查询和更新关系数据。如果必须在 SQL 应用程序不间断工作时与其他应用程序交换包含 XML 标记的数据，该模型很有用。混合模型通常，对于数据建模，适于结合使用关系列和 xml 数据类型列。可以将 XML 数据中的某些值存储在关系列中，而将其余或全部 XML 值存储在 XML 列中。这可获得更好的性能，您可以更好地控制对关系列创建的索引和锁定特征。要存储在关系列中的值取决于您的工作负荷。例如，如果基于路径表达式 /Customer/CustId 检索所有 XML 值，则

17、将 CustId 属性的值提升到关系列并对其创建索引可以获得更快的查询性能。另一方面，如果您的 XML 数据是以非冗余方式广泛地分解为关系列中，则重新汇集的开销可能很大。对于高度结构化的 XML 数据，例如，表的内容已转换为 XML，您可以将所有值映射到关系列，并且可能使用 XML 视图技术。使用 xml 数据类型进行数据建模本部分讨论有关本机 XML 存储的数据建模主题，包括对 XML 数据创建索引、属性提升和类型化的 xml 数据类型。相同或不同的表可以在包含其他关系列的表中，或在与主表具有外键关系的单独表中创建 xml 数据类型列。如果满足下列条件之一，请在同一个表中创建 xml 数据类

18、型列： 您的应用程序对 XML 列执行数据检索，并且不需要 XML 列的 XML 索引。您希望对 xml 数据类型列生成 XML 索引，并且主表的主键与其聚集键相同。有关详细信息，请参阅“对 xml 数据类型列创建索引”。如果满足下列条件，请在单独的表中创建 xml 数据类型列：您希望对 xml 数据类型列生成 XML 索引，但主表的主键与其聚集键不同，或主表没有主键，或主表是一个堆（即没有聚集键）。如果主表已存在，可能会这样。您不希望因为表中存在 XML 列而降低表扫描的速度。无论该列是存储在行内还是行外，都会占用空间。XML 数据的粒度XML 列中存储的 XML 数据的粒度对锁定至关重要，

19、在一定程度上，对更新也很重要。SQL Server 对 XML 数据和非 XML 数据都使用相同的锁定机制。因此，行级锁定会导致锁定行中的所有 XML 实例。当粒度较大时，锁定大型 XML 实例以便进行更新会导致多用户情况下的吞吐量下降。另一方面，过度分解会丢失对象封装，并增加重新汇集开销。对于良好的设计而言，重要的是保持数据建模要求与锁定和更新特征之间的平衡。但在 SQL Server 2005 中，实际存储的 XML 实例的大小并不十分重要。 例如，通过使用对部分二进制大型对象 (BLOB) 和部分索引更新（将存储的现有 XML 实例与其更新后的版本进行比较）的新支持，对 XML 实例进行

20、更新。部分二进制大型对象 (BLOB) 更新在两个 XML 实例之间执行差异比较，并只更新差异之处。部分索引更新只修改那些必须在 XML 索引中更改的行。非类型化、类型化和约束的 xml 数据类型SQL Server 2005 xml 数据类型实现了 ISO SQL-2003 标准 xml 数据类型。因此，它可以在非类型化的 XML 列中存储格式正确的 XML 1.0 版的文档、具有文本节点和任意数量顶级元素的所谓的 XML 内容片段。系统将检查数据格式是否正确，但不要求将列绑定到 XML 架构，并且拒绝在扩展意义上格式不正确的数据。对于非类型化的 XML 变量和参数也是如此。如果您有说明 X

21、ML 数据的 XML 架构，则可以将架构与 XML 列相关联以产生类型化的 XML。XML 架构用于验证数据，在编译查询和数据修改语句过程中执行比非类型化的 XML 更精确的类型检查，以及优化存储和查询处理。在下列情况下，请使用非类型化的 xml 数据类型：您没有对应于您的 XML 数据的架构。您有架构，但不希望服务器验证数据。当应用程序在将数据存储到服务器之前执行客户端验证时，或临时存储根据架构确定无效的 XML 数据时，或在服务器上使用不受支持的架构组件（如 key/keyref）时，有时会出现这种情况。 在下列情况下，请使用类型化的 xml 数据类型： 您有对应于您的 XML 数据的架构

22、，并且希望服务器根据 XML 架构验证 XML 数据。您希望充分利用基于类型信息的存储和查询优化。您希望在编译查询过程中更好地充分利用类型信息。类型化的 XML 列、参数和变量可以存储 XML 文档或内容。但是，必须使用标志指定在声明时是存储文档还是存储内容。此外，必须提供 XML 架构集合。如果每个 XML 实例都刚好有一个顶级元素，请指定 DOCUMENT。否则，请使用 CONTENT。查询编译器在编译查询过程中的类型检查中使用 DOCUMENT 标志以推断单一的顶级元素。 除了对 XML 列进行类型化之外，还可以对类型化或非类型化的 xml 数据类型列使用关系（列或行）约束。在下列情况下

23、，请使用约束： 无法在 XML 架构中表达业务规则。例如，花店的交货地址必须在其营业地点周围 50 英里之内。这可以编写为 XML 列的约束。约束可能涉及 xml 数据类型方法。 您的约束涉及表中的其他 XML 列或非 XML 列。例如，强制使 XML 实例中的客户 ID (/Customer/CustId) 与 CustomerID 关系列中的值匹配。文档类型定义 (DTD)可以使用 XML 架构来对 xml 数据类型列、变量和参数进行类型化，但不能使用 DTD 进行此项操作。但是，内联 DTD 既可用于非类型化的 XML，也可用于类型化的 XML，以便提供默认值，并将实体引用替换为其扩展形

24、式。 可以通过使用第三方工具将 DTD 转换为 XML 架构文档，然后将 XML 架构加载到数据库中。 对 xml 数据类型列创建索引可以对 xml 数据类型列创建 XML 索引。它将对列中 XML 实例的所有标记、值和路径进行索引，从而提高查询性能。在下列情况下，您的应用程序可以从 XML 索引中获益： 对 XML 列进行查询在您的工作负荷中很常见。必须考虑数据修改过程中的 XML 索引维护开销。XML 值相对较大，而检索的部分相对较小。生成索引避免了在运行时分析所有数据，并且索引查找有利于进行高效的查询处理。XML 列的第一个索引是主 XML 索引。使用它时，可以对 XML 列创建三种类型

25、的辅助 XML 索引，以提供常见种类的查询的速度，如以下部分所述。主 XML 索引这将对 XML 列中 XML 实例的所有标记、值和路径进行索引。基表（即包含 XML 列的表）的主键必须具有聚集索引。主键用于将索引行与基表中的行相关联。可从 XML 列中检索完整的 XML 实例，例如 SELECT *。查询使用主 XML 索引，并通过使用索引本身返回标量值或 XML 子树。示例：创建主 XML 索引在大多数示例中，使用包含非类型化的 XML 列的表 T (pk INT PRIMARY KEY, xCol XML)。可以采用简单的方式将这些示例扩展为类型化的 XML。有关如何使用类型化的 XML

26、 的详细信息，请参阅 xml 数据类型。为简化起见，针对 XML 数据实例说明了查询，如下所示： 复制代码 Writing Secure Code Michael Howard David LeBlanc 39.99 以下语句对表 T 的 XML 列 xCol 创建 XML 索引（名为 idx_xCol）： 复制代码 CREATE PRIMARY XML INDEX idx_xCol on T (xCol) 辅助 XML 索引创建了主 XML 索引之后，您可能希望创建辅助 XML 索引来提高工作负荷中不同种类查询的速度。三种类型的辅助 XML 索引（即 PATH、PROPERTY 和 VALU

27、E）分别用于优化基于路径的查询、自定义属性管理方案和基于值的查询。PATH 索引功能是按文档顺序对列中的所有 XML 实例生成各个 XML 节点的 (path, value) 对的 B+ 树。PROPERTY 索引功能是创建各个 XML 实例中 (PK, path, value) 对的聚集 B+ 树，其中 PK 是基表的主键。最后，VALUE 索引功能是按文档顺序对 XML 列中的所有 XML 实例创建每个节点的 (value, path) 对的 B+ 树。下面是创建一个或多个这些索引的一些准则： 如果工作负荷对 XML 列大量使用路径表达式，则 PATH 辅助 XML 索引可能会提高工作负荷

28、的处理速度。最常见的情况是在 Transact-SQL 的 WHERE 子句中对 XML 列使用 exist() 方法。如果工作负荷通过使用路径表达式从单个 XML 实例中检索多个值，则在 PROPERTY 索引中聚集各个 XML 实例中的路径可能会很有用。这种情况通常出现在属性包方案中，此时提取对象的属性并且已知其主键值。如果工作负荷涉及查询 XML 实例中的值，但不知道包含那些值的元素名称或属性名称，则您可能希望创建 VALUE 索引。这通常出现在 descendant 轴查找中，例如 /authorlast-name=Howard，其中 元素可以出现在层次结构的任何级别上。这种情况也出现

29、在通配符查询中，例如 /book * = novel，其中查询将查找具有某个值为“novel”的属性的 元素。示例：基于路径的查找为了进行说明，假定以下查询在您的工作负荷中很常见： 复制代码 SELECT pk, xColFROM TWHERE xCol.exist (/book/genre.=novel) = 1 路径表达式 /book/genre 和值“novel”对应于 PATH 索引的键字段。因此，PATH 类型的辅助 XML 索引对此工作负荷很有用： 复制代码 CREATE XML INDEX idx_xCol_Path on T (xCol) USING XML INDEX idx

30、_xCol FOR PATH 示例：提取对象的属性例如，下面的查询从表 T 中的各行检索书的属性 genre、title 和 ISBN： 复制代码 SELECT xCol.value (/book/genre)1, varchar(50), xCol.value (/book/title/text()1, varchar(50), xCol.value (/book/ISBN)1, varchar(50)FROM T 在这种情况下，属性索引很有用，其创建方式如下： 复制代码 CREATE XML INDEX idx_xCol_Property on T (xCol) USING XML IND

31、EX idx_xCol FOR PROPERTY 示例：基于值的查询在下面的查询中，descendant-or-self (/) 指定部分路径，以便基于 ISBN 值的查找从 VALUE 索引的使用中获益。 复制代码 SELECT xCol FROM T WHERE xCol.exist (/book/ISBN. = 0-7356-1588-2) = 1VALUE 索引的创建方式如下： 复制代码 CREATE XML INDEX idx_xCol_Value on T (xCol) USING XML INDEX idx_xCol FOR VALUE对 XML 列的全文索引您可以对 XML 列创建一个全文索引，该索引对 XML 值的内容进行索引，但忽略 XML 标记。属性值不