甘草轩

Never surrender to complexity
  博客园  :: 首页  :: 新随笔  :: 联系 :: 订阅 订阅  :: 管理

SQLServer2005的XML最佳实施策略

Posted on 2006-07-06 18:53  甘草轩  阅读(753)  评论(0编辑  收藏  举报

适用于:
microsoft sql server

摘要:了解 sql server 2005 中的 xml 数据建模和使用准,并察一些明性的示例。



microsoft sql server 2005
xml 数据理提供了广泛的支持。xml 可以自然地存 xml 数据型列中,而后者可以根据 xml 架构集合型化,或者保持非型化。可以将 xml 入索引。而且,使用 xquery xml dml为进行数据修改而行的展)可以支持粒度的数据操作。

sql server 2000 sqlxml web release 提供了大的 xml 数据管理功能。些功能致力于系数据和 xml 数据之的映射。可以使用有批注的 xsd (axsd) 来定义关系数据的 xml 视图,以便提供以 xml 中心的方法,方法支持 xml 数据的批量数据加查询和更新功能。transact-sql 展提供了以 sql 中心的方法,以便将查询结果映射到 xml(使用 for xml),以及从 xml 生成视图(使用 openxml)。些支持已在 sql server 2005 中得到了展。合新增的原生 xml 支持,sql server 2005 提供了一种强大的平台,以便针对构化和非构化的数据管理开发功能丰富的用程序。

本文提供了 sql server 2005 中的 xml 数据建模和使用准。它包含以下两个主

数据建模

xml 数据可用多方式存 sql server 2005 中,例如,使用原生 xml 数据型和分散到表中的 xml本主提供了做出适当的选择以便 xml 数据行建模的准。同还讨论了将 xml 数据入索引、属性提升和 xml 例的型化。
 
用法

本主题讨论了与用法相的主(如将 xml 数据加到服器以及查询编译中的型推理),解和区分了密切相的功能,并推荐了些功能的适当使用。文中通示例述了各概念。
 

了最大限度地会本文的内容,您应该对 sql server 境中的 xml 功能有一个基本的了解。 xml support in microsoft sql server 2005

返回
数据建模
概述了使用 sql server 2005 中的 xml 的理由,提供了在原生 xml xml 视图间进选择的准,并且提供了数据建模建

系或 xml 数据模型
如果您的数据是高度构化的,具有已知的架构,则关系模型可能于数据存有效。microsoft sql server 提供了您可能需要的必要功能和工具。另一方面,如果构是灵活的(半构化和非构化)或未知的,适当地考如何数据行建模。

如果您需要独立于平台的模型,以便确保使用构化和语义标记的数据的可移植性, xml 是一选择。而且,如果足下列某些属性,是一适当的选择

您的数据比稀疏,或者您不了解数据的构,或者数据的构将来可能生重大更改。
 
您的数据表示容器构(与体中的引用相),并且可能是递归的。

 
您的数据具有内在的序。

 
您希望数据查询,或者基于其构更新部分数据。

 

如果上述任一条件都不足,应该使用系数据模型。例如,如果您的数据是 xml 格式,但您的用程序很少使用数据来存索数据, [n]varchar(max) 列就能足您的全部需要。在 xml 列中存数据可以来其他好 - 引擎将检查数据格式范或者有效,并且支持 xml 数据粒度的查询和更新。

sql server 2005 中存 xml 数据的理由
以下一些使用 sql server 2005 中的原生 xml 功能而不是在文件系中管理 xml 数据的理由:

您希望使用数据器的管理功能来管理 xml 数据(例如,份、恢制)。
 
您希望以高效的方式和事务处理方式来共享、查询和修改 xml数据。粒度的数据访问对于您的用程序而言很重要。例如,您可能需要提取 xml 文档内部的某些,或者您可能需要插入一个新而不是替整个文档。

 
您具有系数据和 sql 用程序,您希望在用程序内部的系数据和 xml 数据之间进行互操作。于跨域用程序,您需要有关查询和数据修改的言支持。

 
您希望服器能数据格式范,并能够视情况根据 xml 架构来验证数据。

 
您需要将 xml 数据入索引以便实现高效的查询处理和良好的可伸性,并且使用一流的查询优化器。

 
您希望 xml 数据 soapado.net ole db 访问

 

如果不足上述任一条件,您最好将数据存储为 xml 的大型数据型,如 [n]varchar(max) varbinary(max)

xml 储选项
sql server 2005
中的 xml 的存储选项如下所示:

本机存采用 xml 数据型:

用能保留数据的 xml 内容(如容器构、文档序、元素和属性等等)的内部表示形式存数据。具体来,就是保留 xml 数据的信息集内容(有信息集的详细信息, http://www.w3.org/tr/xml-infoset)。它可能不是文本 xml 的精确副本,因未保留以下信息:无关紧要的空格、属性序、命名空 xml 声明。

型化的 xml 数据型(即定到 xml 架构的 xml 数据型)而言,负责向信息集添加型信息的后架构验证信息集 (post schema validation infoset, psvi) 以内部表示形式编码著提高分析速度。(有关详细信息, w3c xml 架构范,网址 http://www.w3.org/tr/xmlschema-1 http://www.w3.org/tr/xmlschema-2。)
 
xml
系存的映射:

使用有批注的架构 (axsd)xml 将被分解到一个或多个表中的列,并且在级别保留数据的保真度 - 保留构,但忽略元素。架构不能是递归的。
 
大型象存[n]varchar(max) varbinary(max)):

了数据的精确副本。这对于特殊用途的用(如法律文档)很有用。大多数用不要求精确副本,xml 内容(信息集保真度)即可足需要。
 

通常情况下,可能需要合使用些方法。例如,您可能需要用 xml 数据型列存 xml 数据,并将其中的属性提升到系列中。相反,您可能希望使用映射技,将非递归部分存到非 xml 列中,而递归部分存 xml 数据型列中。

xml 选择
xml
(原生 xml xml 视图)的选择通常取决于下列因素:

储选项

您的 xml 数据可能更适合于大型象存(例如,品手册),或者更适合于存系列中(例如,转换 xml 的行目)。个存储选项都在不同程度上保留了文档保真度。
 
查询功能:

基于查询的性以及 xml 数据查询的程度,您可能发现一个存储选项比其他存储选项适合。粒度的 xml 数据查询(例如,xml 点上的谓词计算)在两个存储选项中受到不同程度的支持。
 
xml 数据入索引:

您可能希望将 xml 数据入索引,以便提高 xml 查询性能。索引选项随存储选项的不同而不同;您需要行适当的选择化工作量。
 
数据修改功能:

某些工作量及到 xml 数据粒度的修改(例如,在文档内添加新),而其他工作量及(例如,web 内容)。于您的用程序而言,数据修改言支持可能很重要。
 
架构支持:

您的 xml 数据可能通架构行描述,可能是也可能不是 xml 架构文档。架构 xml 的支持取决于 xml
 

不用,不同的选择具有不同的性能特性。

原生 xml
可以将您的 xml 数据存在服器的 xml 数据型列中。在下列情况下,将是一个适当的选择

您需要一在服器上存 xml 数据的简单方法,同需要保留文档序和文档构。
 
您的 xml 数据可能有也可能没有架构。

 
您需要查询和修改您的 xml 数据。

 
您需要将 xml 数据入索引以便实现快速的查询处理。

 
您的用程序需要系录视图以管理您的 xml 数据和 xml 架构。

 

当您的 xml 文档具有多种结,或者当您的 xml 文档符合不同的或复杂的架构,而些架构很映射到,原生 xml 将很有用。

示例:使用 xml 数据 xml 数据行建模
一个 xml 格式的品手册,其中个主题对应单独的一章,而章内又有多。一可以包含多个子,因此 是一个递归元素。品手册包含大量混合内容、表和技术资料;数据是半构化的。用可能希望趣的主题执行与上下文有的搜索(例如,在有"索引"的章内部搜索有"聚集索引"),并且查询数量。

xml 文档的合适存模型是 xml 数据型列。可以保留 xml 数据的信息集内容。将 xml 入索引可以提高查询性能。

示例:保留 xml 数据的精确副本
政府法令要求您保留 xml 文档(例如,已署的文档、法律文档或股票交易订单)的精确文本副本。您可能需要将您的文档存 [n]varchar(max) 列中。

查询,可在运行将数据转换为 xml 数据型,然后 xquery。运行时转换可能代价高昂,尤其是在文档很大。如果您查询,可以采用冗余方式将文档存 xml 数据型列中并将其入索引,同 [n]varchar(max) 列返回精确的文档副本。

xml 列可能是基于 [n]varchar(max) 列的算列。您不能在 xml 算列上 xml 索引,也不能在 [n]varchar(max) varbinary(max) 列上生成 xml 索引。

xml 视图
xml 架构和数据的表之映射,可以建持久性数据的"xml 视图"。可以使用 xml 批量负载来填充使用 xml 视图的基表。您可以查询使用 xpath 1.0 xml 视图该查询将被转换为针对表的 sql 查询。与此似,更新也会被传递些表。

在以下情况下,此技很有用:

您希望有以 xml 中心的程模型,模型使用系数据上的 xml 视图
 
您的 xml 数据具有架构 (xsd, xdr),它可能由外部合作伙伴提供。

 
数据的序不重要,或者您的可查询数据不是递归的,或者先已知道最大递归深度。

 
您希望通使用 xpath 1.0 xml 视图查询和修改数据。

 
您希望批量加 xml 数据,并将其分解到使用 xml 视图的基表中。

 

方面的例子包括以 xml 形式公以便用于数据交 web 系数据,以及具有固定架构的 xml 数据。有关详细信息, sqlxml 开发中心。

示例:使用有批注的 xml 架构 (axsd) 数据行建模
有一些希望以 xml 形式行操作的系数据(例如,客订单和行目)。使用 axsd 系数据上定 xml 视图。通 xml 视图,可以将 xml 数据批量加到表中,以及使用 xml 视图查询和更新系数据。如果您需要在自己的 sql 用程序持工作与其他用程序中的 xml 标记数据,则该模式很有用。

混合模型
很多候,适合将系数据和 xml 数据型列合起来行数据建模。可以将 xml 数据中的某些系列中,而将其余或全部 xml xml 列中。可能会生更好的性能(例如,可以完全控制在系列上建的索引)和定特性。然而,需要您承担更多的任来管理数据存

要存系列中的取决于您的工作荷。例如,如果您基于路径表达式 /customer/@custid 索全部 xml custid 属性的提升到系列中以及将其入索引,可能生更高的查询性能。另一方面,如果您的 xml 数据被广泛且非冗余地分解到系列中,重新合的成本可能很大。

于高度构化的 xml 数据(例如,表的内容已经转换 xml),可以将所有映射到系列(可能使用 xml 视图)。

返回
使用 xml 数据行数据建模
节讨论原生 xml 的数据建模主些主包括将 xml 数据入索引、属性提升和型化 xml 数据型。

相同或不同的表
xml
数据型列可以在包含其他系列的表中建,也可以在与主表之具有外键关系的独立表中建。

足下列某个条件在同一个表中 xml 数据型列:

您的用程序在 xml 列上行数据索,并且不需要 xml 列上的 xml 索引。 或者
 
您需要在 xml 数据型列上生成 xml 索引,并且主表的主与其聚集相同。有关详细信息, xml 数据型列入索引一

 

足下列条件独的表中 xml 数据型列:

您需要在 xml 数据型列上生成 xml 索引,但主表的主与其聚集不同,或者主表不具有主,或者主表是一个堆(也就是,没有聚集)。如果主表已存在,则这可能是真的。
 
您不希望表描由于表中存在 xml 列(无它是存在行中是存在行外,都会占用空)而降低速度。

 

xml 数据的粒度
xml
列中存 xml 数据的粒度定和更新特性而言至重要。sql server xml 和非 xml 数据采用了相同的定机制。因此,行级锁定会致相行中的所有 xml 例被定。当粒度比定大型 xml 例以便行更新会致多用户场合下的吐量下降。另一方面,重的分解会象封装并提高重新合成本。

xml 行更新会将例替换为更新后的例,即使只修改个属性的xml 数据粒度越大,更新成本就越高。小的 xml 例会高的更新性能。

于良好的设计而言,重要的是保持数据建模需要与定和更新特性之的平衡。

型化、型化和受束的 xml 数据
sql server 2005 xml
数据实现 iso sql-2003 xml 数据型。因此,它可以在非型化 xml 列中存格式范的 xml 1.0 文档以及有文本点和任意数量顶级元素的所 xml 内容片段。系检查数据的格式是否范,不要求将列定到 xml 架构,并且拒展意上格式不范的数据。于非型化 xml 量和参数,也是如此。

如果您具有描述 xml 数据的 xml 架构,可以将些架构与 xml 列相关联,以便型化 xmlxml 架构用于数据行有效性验证,在查询和数据修改编译过程中行比非型化 xml 更准确的检查,以及化存查询处理。

在下列条件下,使用非型化 xml 数据型:

您没有对应 xml 数据的架构
 
有架构,但不希望服数据行有效性验证。当用程序在服器中存数据之前行客验证时,或者暂时根据架构无效的 xml 数据,或者使用在服器中不受支持的架构件(例如 key/keyref,有会出现这种情况。

 

在下列条件下,使用型化 xml 数据型:

对应 xml 数据的架构,并且希望服器按照 xml 架构 xml 数据行有效性验证
 
您希望充分利用基于型信息的存查询优化。

 
您希望在查询编译过程中更充分地利用型信息。

 

型化 xml 列、参数和量可以存 xml 文档或内容 - 您在声明将它指定为标志(分指定 document content)。而且,您必提供 xml 架构集合。如果 xml 例恰好有一个顶级元素,指定 document;否使用 content查询编译器在查询编译过程的检查中使用 document 标记来推理唯一的顶级元素。

除了将 xml 型化以外,您可以在型化或非型化 xml 数据型列上使用系(列或行)束。在下列条件下,使用束:

无法在 xml 架构中表示业务规则。例如,花店的送地址必在其营业地点周 50 英里范之内,可以 xml 列上的束。该约束可能及到 xml 数据型方法。
 
您的及到表中的其他 xml 列或非 xml 列。方面的一个例子是: xml 例中存在的 customer id (/customer/@custid) customerid 列中的匹配。

 

文档型定 (dtd)
xml
数据型列、量和参数可以使用 xml 架构而不是 dtd 加以型化。然而,于非型化和型化 xml,都可以使用内 dtd 来提供默认值,以便将体引用替换为展形式。

您可以使用第三方工具将 dtd xml 架构文档,并且将 xml 架构加到数据中。

xml 数据型列入索引
可以在 xml 数据型列上 xml 索引。会将列中 xml 例上的所有标记和路径入索引,从而提高查询性能。在下列条件下,您的用程序可能受益于 xml 索引:

xml 查询在您的工作荷中很常。必数据修改程中的 xml 索引维护成本。
 
xml
对较大,而索的部分相对较小。生成索引可以避免在运行分析全部数据,并且因受益于索引找而提高查询处理的性能。

 

xml 列上的第一个索引是" xml 索引"。通过该索引,可以在 xml 列上建三种类型的 xml 索引,从而提高常见种类查询的速度,如下所述。

xml 索引
会将 xml 列中的 xml 例内部的所有标记和路径入索引。基表(即包含 xml 列的表)必表的主上具有聚集索引;主用于将索引行与基表中的行相关联。从 xml 列中索完整的 xml 例(例如 select *)。查询使用主 xml 索引,并返回或使用索引本身的 xml

示例:建主 xml 索引
在我的多数示例中,都使用有非型化 xml 列的表 t (pk int primary key, xcol xml)些示例都可以简单为类型化 xml 的形式(有使用型化 xml 的信息, sql server 2005 图书)。了便于明,将针对如下所示的 xml 数据例描述查询

<book genre="security" publicationdate="2002" isbn="0-7356-1588-2">
      
<title>writing secure code</title>
      
<author>
            
<first-name>michael</first-name>
            
<last-name>howard</last-name>
      
</author> 
      
<author>
            
<first-name>david</first-name>
            
<last-name>leblanc</last-name>
      
</author>
      
<price>39.99</price>
</book>

 

下面的句在表 t xml xcol 建了名 idx_xcol xml 索引:

create primary xml index idx_xcol on t (xcol)

xml 索引
建主 xml 索引之后,您可能希望 xml 索引来提高工作荷中的不同种类查询的速度。三种类型的 xml 索引 - pathproperty 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''

示例:使用 clr 流式表函数的解决方案
解决方案包括以下步骤(a) clr sqlreaderbase,它实现 isqlreader,并且通 xml 例上用路径表达式来生成流式表值输出;(b) 建一个程序集和一个 t-sql 函数 (udf) 来激活 clr (c) 使用 udf 插入、更新和除触器,以维护属性表。

首先,建流式 clr 函数,其主干如下所示。xml 数据型被公开为 ado.net 中的托管 sqlxml;它支持返回 xmlreader 的方法 createreader()

public class c_streaming_xml_tvf {
public static isqlreader streaming_xml_tvf 
(sqlxml xmldoc, 
string pathexpression) {
return (new testsqlreaderbase (xmldoc, pathexpression));
}

}


// class that implements isqlreader
public class testsqlreaderbase : isqlreader {
xpathnodeiterator m_iterator;         
???public sqlchars firstname;
// metadata for current resultset
private sqlmetadata[] m_rgsqlmetadata;      

public testsqlreaderbase (sqlxml xmldoc, string pathexpression) {   
// variables for xpath navigation
      xpathdocument xdoc;
      xpathnavigator xnav;
      xpathexpression xpath;

      
// set sql meta data
      m_rgsqlmetadata = new sqlmetadata[1];
      m_rgsqlmetadata[
0= new sqlmetadata ("firstname",  
sqldbtype.nvarchar,
50);   

      
//set up the navigator
      if (!xmldoc.isnull)
          xdoc 
= new xpathdocument (xmldoc.createreader());
      
else
          xdoc 
= new xpathdocument ();
      xnav 
= xdoc.createnavigator();
      xpath 
= xnav.compile (pathexpression);
      m_iterator 
= xnav.select(xpath);
}

public bool read() {
      
bool morerows = true;
      
if (morerows = m_iterator.movenext())
      
???firstname = new sqlchars (m_iterator.current.value);
      
return morerows;
}

}


接下来,建一个程序集,以及一个与 clr 函数 streaming_xml_tvf 对应 t-sql 函数 sql_streaming_xml_tvf(未示)。 udf 用于定函数 clr_udf_xml2table 以便生成行集:

create function clr_udf_xml2table (@pk int@xcol xml)
returns @ret_table table (fk int, firstname varchar(max))
with schemabinding
as
begin
      
insert into @ret_table 
select @pk, firstname 
from   sql_streaming_xml_tvf (@xcol''/book/author/first-name'')
      
return
end

最后,定器,如示例建触器以填充属性表中所示,但用函数 clr_udf_xml2table udf_xml2table。因此,插入触器将如下所示:

create trigger clr_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.clr_udf_xml2table(@fk@wantedxml)

除触器与非 clr 版本完全相同,而更新触器只是将函数 udf_xml2table() 换为 clr_udf_xml2table()

xml 架构集合
xml
架构集合是一个元数据体,其范系架构确定,包含一个或多个可能相(例如,通 )或无 xml 架构。xml 架构集合中的 xml 架构由其目命名空间标识

xml 架构集合是使用 create xml schema collection 建的,并且提供了一个或多个 xml 架构。可以向 xml 架构中添加更多的 xml 架构件,并且可以使用 alter xml schema collection 法向 xml 架构集合中添加更多的架构。可以使用 sql server 2005 中的安全模型像任何 sql 象那 xml 架构集合的安全。

型化列
xml
架构集合 c 按照多个 xml 架构将 xml xcol 型化。此外, document content 指定 xml 或片段是否可以存在列 xcol 中。

document xml 例都会按照用来验证型化的命名空,指定例中顶级元素的目命名空。另一方面, content顶级元素都可以指定 c 中的任一目命名空xml 例将按照例中存在的所有目命名空间进验证型化。

架构演
xml
架构集合用于型化 xml 列、量和参数。它提供了一 xml 架构演机制。假您将有目命名空 book-v1 xml 架构添加到 xml 架构集合 c 中。使用 c 加以型化的 xml xcol 可以存符合 book-v1 架构的 xml 数据。

某个用程序希望通新的架构件(例如复杂类型定顶级元素声明)来 xml 架构。些新的架构件可以添加到 book-v1 架构中,并且不要求 xcol 中的 xml 数据行重新验证

设该应用程序后来希望提供 xml 架构的新版本,并且为该新版本选择命名空 book-v2 xml 架构可以添加到 c 中。xml 列可以存 book-v1 book-v2 二者的例,并且符合些命名空 xml 查询和数据修改。

返回
用法
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)

xquery 型推理
嵌入到 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)

xml 架构用于对类型化 xml 检查。如果点被指定 xml 架构中的唯一点,则编译器将使用信息,并且不会出任何错误。否,需要能够选择单点的序号。特地,如果使用子代或自身 (//),例如 /book//title <title> 元素的唯一性基数推理,即使 xml 架构指定其具有这种将其改写 (/book//title)[1]

检查,需要 //first-name[1] (//first-name)[1] 的差。前者返回 点的序列,其中点是其同辈节点中最左 点。后者返回 xml 例中按照文档序的第一个唯一的 点。

示例:value() 的用法

select nref.value(''@genre''''varchar(max)'') lastname
from   t cross apply xcol.nodes(''//book''as r(nref)


下面型化 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 例中都可能出多个 点。下面的改写方式将会有效:

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"

 

型的函数和运算符
由于检查型要求真的理。以下示例述了其中两个问题

示例:型上的函数
以下型的 <r> 的元素定

<xs:element name="r">
<xs:simpletype>
   <xs:union membertypes="xs:int xs:float xs:double"/>
</xs:simpletype>
</xs:element>

xquery 上下文中,"average"函数 fn:avg (//r) 会返回静态错误,因 xquery 编译器无法 fn:avg() 的参数中 元素的不同型(xs:intxs: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 1sql 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'' "代表 pathproperty 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 架构集合中使用的命名空列表包含 xmlxsxsifn 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 架构命名空则还从表中命名空以保持一致性