SQL server 2008 关于XML类型数据功能总结及性能测试

最新推荐文章于 2024-08-21 09:51:24 发布

勇-子

最新推荐文章于 2024-08-21 09:51:24 发布

阅读量1.5w

点赞数 3

分类专栏： XML 总结

本文链接：https://blog.csdn.net/w174504744/article/details/8215456

版权

总结同时被 2 个专栏收录

44 篇文章 0 订阅

订阅专栏

XML

14 篇文章 0 订阅

订阅专栏

本文详细介绍了SQL Server在不同版本中对XML数据的支持，包括XML数据类型的引入、FORXML和OpenXML功能的扩展、XML索引的增强以及XML基本操作的实现。通过实例展示了如何在SQL Server中查询、修改、索引和操作XML数据。

摘要由CSDN通过智能技术生成

一、SQL Server 对XML数据功能支持

SQL Server 2000 对XML 的支持

在SQL Server2000 中，标准的T-SQL Select 语句引入了FOR XML 子句，FOR XML 是Select 语句的扩展，返回的查询结果是XML 流，它以XML 文档形式形成一个查询结果集。FOR XML 子句有三种模式Raw、Auto 和Explicit，每个都能对XML 文档格式提供附加标准的控制。

SQL Server 2000 中引入了OPENXML Transact-SQL 关键字。使用OPENXML 行集提供程序写入XML 数据。SQL Server 2000 增强了SQLOLEDB，这样便可以将XML 文档设置为命令文本并以流的形式返回结果集。获取XML 数据，我们可以使用URL 查询、XML 模板文件、XDR 架构和XPath 查询等方法。SQL Server2000 允许使用HTTP 来访问SQL Server，在URL 中执行的查询可以直接访问SQL Server 2000 生成XML 文档（也可以调用存储在Web 服务器上的XML 模版生成XML 数据文件）；也可以调用存储过程或是通过使用XPath 查询来获取XML 数据。SQL Server 2000 完全支持XDR（XML 数据简化）架构，具有映射XML 元素和属性到表和字段中的功能。

SQL Server 2005 对XML 支持的扩展

SQL Server 2005 对XML 的重大扩展，就是在SQL Server 2005 引入了XML 数据类型，使用XML 数据类型能够存储XML 文档，可以使用XML 数据类型存储数据库中的标记文档或半结构化数据。当以XML 数据类型列存储XML 值时，有两种选择：一种是使用类型化列（为更好的结构化或者验证XML 数据，SQL Server可将架构与特定XML 列关联起来，这种列称为类型化XML 列），在这种类型列中存储的XML 数据使用XML架构集合来确保有效性。另一种是非类型化列：在这种类型列中，只要XML 数据格式良好，则能够插入任何类型的XML 数据。这种灵活性使得开发人员可以对结构化数据使用关系型模型，同时利用XML 的半结构化数据增强该数据。

SQL Server 2005 中FOR XML 和OpenXML 的功能都得到了扩展。FOR XML 查询集成了XML 数据类型，FOR XML 子句使用引入的XML 数据类型能够使用新的TYPE 指令直接生成XML 实例。由于结果是XML 数据类型实例，所以还可以使用XQuery 表达式查询和整理结果。另外，FOR XML 和OpenXML 都支持XQuery，这样SQL Server 2005 就能够实现利用半结构化数据开发应用程序，实现对非结构化数据的管理。这使得开发人员对于数据存储和应用程序开发有了更多的选择。此外，SQL Server 2005 还提供了用于对存储在列或变量中的XML 数据执行操作的大量方法，允许使用一系列XML 特定的函数对此数据类型进行查询和处理。这些方法可接受XQuery，可以用于在实例中查询和操纵XML 数据。SQL Server 2005 支持的XQuery 语法包括FLWOR 表达式中的For、Where、Order By 和Return 语句，这些语句可用于循环访问XML 文档中的节点，也可用于返回值。

SQL Server 2005 还支持XML 索引。为了增强XML 的查询功能，可以为类型XML 列创建主XML 索引和辅助XML 索引。主XML 索引是XML 实例中所有节点的细化表示，查询处理器可以使用它快速查找XML 值中的节点。创建主XML 索引后，可以创建辅助XML 索引改善特定查询类型的性能。

SQL Server 2008 对XML 支持的增强特性

在SQL Server 2000 和SQL Server 2005 中支持的XML 功能，在SQL Server 2008 这个版本中不但全面支持，而且还扩展了构建能够合并关系数据和XML 数据库解决方案的功能。通过对XML 架构验证进行改进、增强XQuery 的支持和执行XML 数据操作语言的插入功能，SQL Server 2008 进一步增强了对XML 的支持。使用XQuery 表达式对XML 数据执行操作时，SQL Server 2008 增加了对Let 语句的支持，该语句用于向XQuery 表达式中的变量赋值；而且XML 数据类型通过其Modify 方法支持Insert、Replace Value of 和Delete 这些XML DML 表达式。可以使用这些XML DML 表达式操纵XML 列或变量中的XML 数据。另外，SQL Server 2008 添加了对使用Insert 表达式中的XML 变量向现有XML 结构插入XML 数据的支持。SQL Server 2008 对XML 的支持和不断增强的势头，对应用程序开发人员具有极大的吸引力。

图1 SQL Server 2008 Management Studio

SQL Server 2012 对XML 支持的增强特性

XQuery 函数可识别代理项XQuery 函数和运算符的 W3C 建议要求它们对代理项对进行计数，该代理项对用 UTF-16 编码将大范围 Unicode 字符表示单个符号。但是，在 SQL Server 2012 之前的 SQL Server 版本中，字符串函数不能将代理对识别为单个字符。某些字符串操作（例如，字符串长度计算和子字符串提取）会返回不正确的结果。 SQL Server 2012 现在完全支持 UTF-16，可正确处理代理项对。

SQL Server 中的 XML 数据类型只允许格式正确的代理项对。但在某些情况下，一些函数仍会返回未定义的结果或意外结果，这是因为可能会将无效的或部分代理项对作为字符串值传递给 XQuery 函数。在 SQL Server 中使用 XQuery 时，请考虑以下生成字符串值的方法：提供常量字符串值作为二进制值。在使用此方法时，仍可能传递无效的或部分代理项对。通过提供字符实体来提供常量字符串值。使用此方法时，仍可能传递无效或不完整代理对。 XQuery 函数需要高级字符的单字符实体。如果提供代理项对字符的字符实体，这些函数将引发错误。通过使用 sql:column 或 sql:variable 导入外部值。在使用这些方法时，仍可能引入无效的或部分代理项对。受影响的 XQuery 函数和运算符以下 XQuery 函数和运算符现在可在 SQL Server 2012 中正确处理 UTF-16 代理项对：fn:string-length。但是，如果将无效或不完整代理对作为参数传递，则 string-length 的行为未定义。fn:substring。fn:contains。但是，如果将部分代理项对作为值传递，则 contains 可能会返回意外结果，因为它可能会找到格式正确的代理项对中包含的部分代理项对。fn:concat。但是，如果将部分代理项对作为值传递，则 concat 会生成不正确的代理项对或部分代理项对。比较运算符和 order by 子句。比较运算符包括 +、<、>、<=、>=、eq、lt、gt、le 和 ge。

二、SQL Server 中对XML数据的五种基本操作

1.xml.exist

输入为XQuery表达式，返回0，1或是Null。0表示不存在，1表示存在，Null表示输入为空。

示例：

select * from XMLTablewhere XMLCol.exist('(/authorinfo/personinfo/lastname)[5]= "魏"') = 1

详细介绍参考：http://msdn.microsoft.com/zh-cn/library/ms189869.aspx

2.xml.value

输入为XQuery表达式，返回一个SQL Server标量值。

示例：

select * from xml where xml.value('(/authorinfo/personinfo/firstname)[1]','nvarchar(50)') ='维春'

详细介绍参考：http://msdn.microsoft.com/zh-cn/library/ms178030.aspx

3.xml.query

输入为XQuery表达式，返回一个SQL Server XML类型流。

示例：

select XML.query('(/authorinfo/personinfo/lastname)= "王"') as xml from [XML]

详细介绍参考：http://msdn.microsoft.com/zh-cn/library/ms191474.aspx

4.xml.nodes

输入为XQuery表达式，返回一个XML格式文档的一列行集。

示例：

SELECTT2.Loc.query('.')

FROM T

CROSSAPPLY Instructions.nodes('/root/Location') as T2(Loc)

详细介绍参考：http://msdn.microsoft.com/zh-cn/library/ms188282.aspx

5.xml.modify

使用XQuery表达式对XML的节点进行insert , update 和 delete 操作。

详细介绍参考：http://msdn.microsoft.com/zh-cn/library/ms187093.aspx

三、SQL Server中的XML 索引介绍

可以对 xml 数据类型列创建 XML 索引。它们对列中 XML 实例的所有标记、值和路径进行索引，从而提高查询性能。在下列情况下，您的应用程序可以从 XML 索引中获益：对 XML 列进行查询在您的工作负荷中很常见。必须考虑数据修改过程中的 XML 索引维护开销。

XML 值相对较大，而检索的部分相对较小。生成索引避免了在运行时分析所有数据，并能实现高效的查询处理，从而使索引查找受益。

XML 索引分为下列类别：

主 XML 索引

辅助 XML 索引

xml 类型列的第一个索引必须是主 XML 索引。使用主 XML 索引时，支持下列类型的辅助索引：PATH、VALUE 和 PROPERTY。根据查询类型的不同，这些辅助索引可能有助于改善查询性能。

XML 实例作为二进制大型对象 (BLOB) 存储在 xml 类型列中。这些 XML 实例可以很大，并且存储的 xml 数据类型实例的二进制表示形式最大可以为 2 GB。如果没有索引，则运行时将拆分这些二进制大型对象以计算查询。此拆分可能非常耗时。例如，请看以下查询：

WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription'AS "PD")

SELECTCatalogDescription.query('

/PD:ProductDescription/PD:Summary

')as Result

FROMProduction.ProductModel

WHERECatalogDescription.exist('/PD:ProductDescription/@ProductModelID[.="19"]') = 1

为了选择满足 WHERE 子句中条件的 XML 实例，表 Production.ProductModel 的每行中的 XML 二进制大型对象 (BLOB) 将在运行时拆分。然后，计算 exist() 方法中的表达式(/PD:ProductDescription/@ProductModelID[.="19"])。此运行时拆分有可能开销较大，这取决于存储在列中的实例的大小和数目。

如果在应用程序环境中经常查询 XML 二进制大型对象 (BLOB)，则对 xml 类型列创建索引很有用。但是，在数据修改过程中维护索引会带来开销。

1、主 XML 索引

主 XML 索引对 XML 列中 XML 实例内的所有标记、值和路径进行索引。若要创建主 XML 索引，相应 XML 列所在的表必须对该表的主键创建了聚集索引。SQL Server 使用此主键将主 XML 索引中的行与包含此 XML 列的表中的行关联起来。

主 XML 索引是 xml 数据类型列中的 XML BLOB 的已拆分和持久的表示形式。对于列中的每个 XML 二进制大型对象 (BLOB)，索引将创建数个数据行。该索引中的行数大约等于 XML 二进制大型对象中的节点数。当查询检索完整的 XML 实例时，SQL Server 会提供此 XML 列中的实例。XML 实例中的查询使用主 XML 索引，并可以通过使用索引本身返回标量值或 XML 子树。

每行存储以下节点信息：

标记名（如元素名称或属性名称）。

节点值。

节点类型（如元素节点、属性节点或文本节点）。

文档顺序信息（由内部节点标识符表示）。

从每个节点到 XML 树的根的路径。搜索此列可获得查询中的路径表达式。

基表的主键。基表的主键将复制到主 XML 索引中，用于向后和基表进行联接，并且基表的主键中的最大列数限制为 15。

此节点信息用于计算和构造指定查询的 XML 结果。出于优化的需要，标记名和节点类型信息编码为整数值，且 Path 列使用同样的编码。另外，路径以相反的顺序存储，以便在仅知道路径后缀的情况下能够匹配路径。例如：

//ContactRecord/PhoneNumber，其中只有最后两个步骤是已知的

或

/Book/*/Title，其中通配符 (*) 是在表达式中间指定的。

对于涉及 XML 数据类型方法的查询，查询处理器使用主 XML 索引，并返回主索引自身中的标量值或 XML 子树。（此索引存储重新构造 XML 实例所需的所有信息。）

例如，以下查询将返回 ProductModel 表的 CatalogDescriptionxml 类型列中存储的摘要信息。只有当产品型号的目录说明中还存储 <Features> 说明时，该查询才会返回 <Summary> 信息。

WITHXMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription'AS "PD")SELECT CatalogDescription.query(' /PD:ProductDescription/PD:Summary') asResultFROM Production.ProductModelWHERE CatalogDescription.exist('/PD:ProductDescription/PD:Features') = 1

关于主 XML 索引，为 exist() 方法中指定的表达式按顺序搜索与每个 XML 二进制大型对象相对应的索引中的行，而不是拆分基表中的每个 XML 二进制大型对象实例。如果路径是在索引中的 Path 列中找到的，则从主 XML 索引检索 <Summary> 元素及其子树，并将它们转换为 XML 二进制大型对象以作为 query() 方法的结果。

注意，检索完整的 XML 实例时不使用主 XML 索引。例如，下面的查询从表中检索描述了特定产品型号的生产说明的整个 XML 实例。

USEAdventureWorks2012;SELECT InstructionsFROM Production.ProductModel WHEREProductModelID=7;

2、辅助 XML 索引

为了增强搜索性能，可以创建辅助 XML 索引。必须有了主 XML 索引才能创建辅助索引。辅助索引的类型如下：

PATH 辅助 XML 索引

VALUE 辅助 XML 索引

PROPERTY辅助 XML 索引

以下为创建一个或多个辅助索引的一些准则：

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

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

如果工作负荷涉及查询 XML 实例中的值，但不知道包含那些值的元素名称或属性名称，则您可能希望创建 VALUE 索引。这通常出现在 descendant 轴查找中，例如 //author[last-name="Howard"]，其中 <author> 元素可以出现在层次结构的任何级别上。这种情况也出现在通配符查询中，例如 /book [@* = "novel"]，其中查询将查找具有某个值为“novel”的属性的 <book> 元素。

PATH 辅助XML 索引

如果查询通常对 xml 类型列指定路径表达式，则 PATH 辅助索引可以提高搜索的速度。如本主题前面所述，当查询在 WHERE 子句中指定 exist() 方法时主索引非常有用。如果添加 PATH 辅助索引，则您还可以改善此类查询的搜索性能。

虽然主 XML 索引避免了在运行时拆分 XML 二进制大型对象，但是它不会为基于路径表达式的查询提供最好的性能。由于是按顺序在与 XML 二进制大型对象相对应的主 XML 索引中的所有行中搜索大 XML 实例，所以按顺序搜索可能会很慢。这种情况下，对主索引中的路径值和节点值生成辅助索引可以有效地提高索引搜索的速度。在 PATH 辅助索引中，路径值和节点值是允许在搜索路径时使用更高效的查找功能的键列。查询优化器可以将 PATH 索引用于如下所示的表达式：

/root/Location，仅指定一个路径

或

/root/Location/@LocationID[.="10"]，其中路径和节点值均指定。

以下查询介绍了适用 PATH 索引的情形：

WITHXMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription'AS "PD")

SELECTCatalogDescription.query('

/PD:ProductDescription/PD:Summary

')AS Result

FROMProduction.ProductModel

WHERECatalogDescription.exist('/PD:ProductDescription/@ProductModelID[.="19"]') = 1

在该查询中，exist() 方法中的路径表达式 /PD:ProductDescription/@ProductModelID 和值 "19" 对应于 PATH 索引的键字段。这便允许在 PATH 索引中直接查找，并为主索引中的路径值提供优于顺序搜索的搜索性能。

VALUE 辅助XML 索引

如果查询是基于值的查询，例如 /Root/ProductDescription/@*[. = "Mountain Bike"] 或 //ProductDescription[@Name = "Mountain Bike"]，且没有完全指定路径或路径包含有通配符，则生成基于主 XML 索引中的节点值所创建的辅助 XML 索引可以更快地获得结果。

VALUE 索引的键列是主 XML 索引的节点值和路径。如果您的工作负荷涉及到查询 XML 实例中的值，但不知道包含这些值的元素名称或属性名称，则 VALUE 索引可能会很有用。例如，以下表达式受益于 VALUE 索引：

//author[LastName="someName"]，其中 <LastName> 元素的值已知，但是 <author> 父级可以出现在任何地方。

/book[@*= "someValue"]，其中查询将查找包含值为 "someValue" 的属性的 <book> 元素。

以下查询从 Contact 表中返回 ContactID。 WHERE 子句指定一个筛选器，该筛选器查找 AdditionalContactInfoxml 类型列中的值。只有当相应的其他联系信息 XML 二进制大型对象包含具体的电话号码时，才会返回联系 ID。由于 <telephoneNumber> 元素可以显示在 XML 中的任意位置，因而路径表达式指定 descendent-or-self 轴。

WITHXMLNAMESPACES (

'http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo'AS CI,

'http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes'AS ACT)

SELECTContactID

FROM Person.Contact

WHERE AdditionalContactInfo.exist('//ACT:telephoneNumber/ACT:number[.="111-111-1111"]')= 1

在这种情况下，<number> 的搜索值是已知的，但是它可以作为 <telephoneNumber> 元素的子级在 XML 实例中的任意位置出现。这种查询可能受益于基于特定值的索引查找。

PROPERTY 辅助索引

从单个 XML 实例检索一个或多个值的查询适用 PROPERTY 索引。当使用 xml 类型的 value() 方法检索对象属性并且知道对象的主键值时，会发生这种情况。

PROPERTY 索引是对主 XML 索引的列（PK、Path 和节点值）创建的，其中 PK 是基表的主键。

例如，对于产品样式 19，以下查询使用 value() 方法检索 ProductModelID 属性值和 ProductModelName 属性值。使用 PROPERTY 索引代替主 XML 索引或其他辅助 XML 索引可以使执行速度更快。

WITHXMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription'AS "PD")

SELECTCatalogDescription.value('(/PD:ProductDescription/@ProductModelID)[1]', 'int')as ModelID,

CatalogDescription.value('(/PD:ProductDescription/@ProductModelName)[1]','varchar(30)') as ModelName

FROMProduction.ProductModel

WHEREProductModelID = 19

除了稍后在本主题中介绍的区别之外，对 xml 类型列创建 XML 索引和对非 xml 类型列创建索引类似。可以使用下列 Transact-SQL DDL 语句创建和管理 XML 索引：

CREATEINDEX (Transact-SQL)

ALTERINDEX (Transact-SQL)

DROPINDEX (Transact-SQL)

四、SQL Server 2008 测试结果：

1、插入数据

declare @x int

declare @y int

declare @count int

set @x = 0

while @x < 100

begin

set@y = 0

while @y < 100

begin

set @count = 0

while @count < 100

begin

declare @path nvarchar(200)

set @path = N'insert into[XML].[dbo].[XML]([XML])select * from openrowset(bulk''G:\NXDData\xmlTestData\xmlTestData\00\' +right('0'+cast(@x as nvarchar),2) + N'\' + right('0'+cast(@y as nvarchar),2) + N'\00' + right('0'+cast(@x as nvarchar),2) + right('0'+cast(@y as nvarchar),2)+ right('0'+cast(@count as nvarchar),2)+ N'.xml'',SINGLE_CLOB) asx';