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

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

下面的语句在表 T 的 XML 列 xCol 上创建了名为 idx_xCol 的 XML 索引：

CREATE PRIMARY XML INDEX idx_xCol on T (xCol)

辅助 XML 索引

在创建主 XML 索引之后，您可能希望创建辅助 XML 索引来提高工作负荷中的不同种类查询的速度。三种类型的辅助 XML 索引 - PATH、PROPERTY 和 VALUE 分别为基于路径的查询、自定义属性管理场合和基于值的查询提供帮助。PATH 索引在列中的所有 XML 实例上，按照文档顺序生成各个 XML 节点的 (path, value) 对的 B+ 树。PROPERTY 索引创建各个 XML 实例中 (PK, path, value) 对的聚集 B+ 树，其中 PK 是基表的主键。最后，VALUE 索引在 XML 列中的所有 XML 实例中，按照文档顺序创建各个节点的 (value, path) 对的 B+ 树。

以下是创建上述一个或多个索引的一些准则：

• 如果工作负荷大量使用 XML 列中的路径表达式，则 PATH 辅助 XML 索引可能会加快工作负荷的处理速度。最常见的例子是在 T-SQL 的 WHERE 子句中对 XML 列使用 exist() 方法。

• 如果您的工作负荷从单独的使用路径表达式的 XML 实例中检索多个值，则将各个 XML 实例中的路径聚集到 PROPERTY 索引中可能会很有用。这种情况通常出现在属性包场合中，此时对象的属性被获取并且其主键值已知。

• 如果您的工作负荷涉及到查询 XML 实例中的值，而不知道包含这些值的元素或属性名称，则您可能需要创建 VALUE 索引。这通常发生在子代轴查找中，例如 //author[last-name="Howard"]，其中 元素可以出现在层次结构的任意级别上。这种情况还会发生在"通配符"查询中，例如 /book [@* = "novel"]，其中查询将查找具有某个值为 "novel" 的属性的 元素。

示例：基于路径的查找

假设下面的查询在您的工作负荷中很常见：

SELECT pk, xCol

FROM T

WHERE 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_xCol FOR PATH

示例：获取对象的属性

请考虑下面的查询，它从表 T 的各个行中检索一本书的属性"genre"、"title"和 ISBN：

SELECT xCol.value ('(/book/@genre)[1]', 'varchar(50)'),

xCol.value ('(/book/title)[1]', 'varchar(50)'),

xCol.value ('(/book/@ISBN)[1]', 'varchar(50)')

FROM T

在这种情况下，属性索引很有用，其创建方式如下所示：

CREATE XML INDEX idx_xCol_Property on T (xCol)

USING XML INDEX idx_xCol FOR PROPERTY

示例：基于值的查询

在以下查询中，子代轴或自身轴 (//) 指定了部分路径，以便基于 ISBN 值的查找可以因为使用 VALUE 索引而受益：

SELECT xCol

FROM T

WHERE xCol.exist ('//book[@ISBN = "1-8610-0157-6"]') = 1

VALUE 索引按如下方式创建：

CREATE XML INDEX idx_xCol_Value on T (xCol)

USING XML INDEX idx_xCol FOR VALUE

XML 列上的全文索引

您可以在 XML 列上创建全文索引，从而将 XML 值的内容编入索引，而忽略 XML 标记。属性值没有被编入全文索引（因为它们被视为标记的一部分），并且元素标记被用作标记边界。在某些情况下，可以将全文搜索与 XML 索引用法结合起来：

• 首先，使用 SQL 全文搜索筛选感兴趣的 XML 值。

• 接下来，查询这些 XML 值，这会使用 XML 列上的 XML 索引。

示例：将全文搜索与 XML 查询结合起来

在 XML 列上创建全文索引之后，以下查询将检查 XML 值是否在书名中包含单词"custom"：

SELECT *

FROM T

WHERE CONTAINS(xCol,'custom')

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

CONTAINS() 方法使用全文索引，将文档中任何地方包含单词"custom"的 XML 值组合为一个子集。exist() 子句确保单词"custom"出现在书名中。

使用 CONTAINS() 和 XQuery contains() 的全文搜索具有不同的语义。后者是子字符串匹配，而前者则是使用单词衍生的标记匹配。因此，如果要搜索标题中的字符串 "run"，则 "run"、"runs" 和 "running" 都将匹配，因为全文 CONTAINS() 和 Xquery contains() 都满足。然而，上述查询不匹配标题中的单词"customizable"。（全文 CONTAINS() 失败，而 Xquery contains() 被满足）。通常，对于纯粹的子字符串匹配，应该删除全文 CONTAINS() 子句。

而且，全文搜索采用单词衍生，而 XQuery contains() 是一种字面匹配。这一区别将在下一个示例中阐述。

示例：使用单词衍生对 XML 值进行全文搜索

通常情况下，不能排除示例：将全文搜索与 XML 查询结合起来中的 XQuery contains() 检查。请考虑查询：

SELECT *

FROM T

WHERE CONTAINS(xCol,'run')

因为使用单词衍生，所以文档中的单词"ran"匹配搜索条件。而且，使用 XQuery 时不会检查搜索上下文。

在使用被全文索引的 AXSD 将 XML 分解到关系列中时，XML 视图上的 XPath 查询不会对基础表执行全文搜索。

属性提升

如果主要是对少量元素和属性值进行查询（例如，基于客户 ID 查找客户，即指定了 /Customer/@CustId 的值），您可能希望将这些数量提升到关系列中。当检索了整个 XML 实例，但只对一小部分 XML 数据进行查询时，这将很有用。在 XML 列上创建 XML 索引是没有必要的；相反，可以将被提升的列编入索引。必须编写查询以使用提升的列（即，查询优化器不会将对 XML 列的查询重新定向到提升的列）。

提升的列可以是同一表中的计算列，也可以是表中单独的、用户维护的列。当从各个 XML 实例中提升唯一值（即单值属性）时，这已足够。然而，对于多值属性，您必须为该属性创建单独的表，如下所述。

基于 XML 数据类型的计算列

可以使用能够激活 XML 数据类型方法的用户定义函数 (UDF) 来创建计算列。计算列的类型可以是任何 SQL 类型，包括 XML。以下示例说明了这一点。

示例：基于 XML 数据类型方法的计算列

为书籍的 ISBN 创建用户定义的函数：

CREATE FUNCTION udf_get_book_ISBN (@xData xml)

RETURNS varchar(20)

BEGIN

DECLARE @ISBN varchar(20)

SELECT @ISBN = @xData.value('/book[1]/@ISBN', 'varchar(20)')

RETURN @ISBN

END

为 ISBN 向表中添加一个计算列：

ALTER TABLE T

ADD ISBN AS dbo.udf_get_book_ISBN(xCol)

可以用通常的方式将计算列编入索引。

示例：基于 XML 数据类型方法的计算列上的查询

要获取其 ISBN 为 0-7356-1588-2 的 ，可以改写 XML 列上的查询

SELECT xCol

FROM T

WHERE xCol.exist ('/book[@ISBN = "0-7356-1588-2"]') = 1

以使用计算列，如下所示：

SELECT xCol

FROM T

WHERE ISBN = '0-7356-1588-2'

可以创建一个用户定义的函数，返回 XML 数据类型和使用该 UDF 的计算列。然而，无法在计算的 XML 列上创建 XML 索引。

创建属性表

您可能希望将 XML 数据中的某些多值属性提升到一个或多个表中，在这些表上创建索引，并且重定向查询以使用这些表。典型的情形是一小部分属性覆盖了大部分查询工作负荷。您可以执行以下操作：

• 创建一个或多个表以存放多值属性。您可能发现采用以下处理方式会很方便：每个表存储一个属性，并且在属性表中复制基表的主键以便与基表进行向后联接。

• 如果您希望保持属性的相对顺序，则需要为相对顺序引入一个单独的列。

• 在 XML 列上创建触发器以便维护属性表。在触发器中，执行以下操作：

• 使用 XML 数据类型方法（如 nodes() 和 value()）在属性表中插入和删除行。（有关 nodes() 方法的详细信息，请参阅 Value()、Nodes() 和 OpenXML()。）

• 在 CLR 中创建流式表值函数，以便在属性表中插入和删除行。

• 编写查询，以便对属性表进行 SQL 访问，以及对基表中的 XML 列进行 XML 访问，这需要使用这些表的主键将其相互联接。

示例：创建属性表

假设您希望提升作者的名字。书籍有一个或多个作者，因此名字是一个多值属性。每个名字都存储在属性表的单独行中。在属性表中复制了基表的主键以便向后联接。

create table tblPropAuthor (propPK int, propAuthor varchar(max))

示例：创建用户定义的函数以便从 XML 实例生成行集

下面的表值函数 udf_XML2Table 接受一个主键值和一个 XML 实例。它将检索 元素的所有作者的名字，并返回（主键，名字）对行集。

create function udf_XML2Table (@pk int, @xCol xml)

returns @ret_Table table (propPK int, propAuthor varchar(max))

with schemabinding

as

begin

insert into @ret_Table

select @pk, nref.value('.', 'varchar(max)')

from @xCol.nodes('/book/author/first-name') R(nref)

return

end

示例：创建触发器以填充属性表

插入触发器：在属性表中插入行

create trigger trg_docs_INS on T for insert

as

declare @wantedXML xml

declare @FK int

select @wantedXML = xCol from inserted

select @FK = PK from inserted

insert into tblPropAuthor

select * from dbo.udf_XML2Table(@FK, @wantedXML)

删除触发器：基于删除行的主键值，从属性表中删除行

create trigger trg_docs_DEL on T for delete

as

declare @FK int

select @FK = PK from deleted

delete tblPropAuthor where propPK = @FK

更新触发器：在与更新的 XML 实例对应的属性表中删除现有行，并且在该属性表中插入新行

create trigger trg_docs_UPD

on T

for update

as

if update(xCol) or update(pk)

begin

declare @FK int

declare @wantedXML xml

select @FK = PK from deleted

delete tblPropAuthor where propPK = @FK

select @wantedXML = xCol from inserted

select @FK = pk from inserted

insert into tblPropAuthor

select * from dbo.udf_XML2Table(@FK, @wantedXML)

end

示例：查找作者的名字为"David"的 XML 实例

可以在 XML 列上表示该查询。另外，还可以在属性表中搜索名字"David"，然后与基表执行向后联接以返回 XML 实例，如下所示：

SELECT xCol

FROM T JOIN tblPropAuthor ON T.pk = tblPropAuthor.propPK

WHERE tblPropAuthor.propAuthor = 'David'

赞