XML数据类型的高级应用之SqlServer

< xmlnamespace prefix ="st1" ns ="urn:schemas-microsoft-com:office:smarttags" /> 11.7.1 使用 OPENXML 查询 XML

在 SQL Server 2005 种提供了如何将对 XML 数据的查询结果转换成数据表形式的方法。下面的例子显示了如何做到这一点。

-- 使用 OpenXML 查询

-- 第一步定义 XML 数据，注意因为使用了中文内容所以必须使用 nvarchar 类型

DECLARE @iHandle int

DECLARE @XMLData nvarchar(max)

SET @XMLData =

<Books>

上帝的色子

</Name>

<Price>

27.80

</Price>

</Author>

</Book>

土星的光环

</Name>

<Price>

38.22

</Price>

</Author>

</Book>

</Books>'

-- 第二步使用存储过程 sp_xml_preparedocument 创建 XML 数据的内存句柄

EXEC sp_xml_preparedocument @iHandle OUTPUT, @XMLData

-- 第三步使用带有 XPath 表达式的查询来转换查询的结果为表格形式

SELECT *

FROM OPENXML (@iHandle, '/Books/Book',2)

WITH (Name varchar(50),

Price decimal(9,2),

Author varchar(10))

-- 第四步销毁 XML 数据的内存句柄

EXEC sp_xml_removedocument @iHandle

上面的例子显示了如何使用 OpenXML 函数来打开一个 XML 结果集。同时也非常清楚的标示了主要的操作步骤。

上面的例子将返回如下的结果集。

Name Price Author

----------- ---------- ----------

上帝的色子 27.80 尚娣

土星的光环 38.22 张正

从结果可以看出， XML 数据的树形式样式，被转换成了类似普通 T-SQL 查询的结果集。这就是 OpenXML 查询的主要用途。

OpenXML 语句的第一个参数必须是由存储过程 sp_xml_preparedocument 生成的 XML 内存表达形式的句柄。该值用 int 型变量保存。第二个参数是一个 XPath 表达式，这个表达式指导 OpenXML 语句返回 XML 数据的那些部分。第三个参数的值指导 OpenXML 返回 XML 数据的内部节点的类型。 1 表示只返回符合第二个 XPath 表达式的节点的属性值， 2 表示返回节点的值。

OpenXML 的 With 子句用于根据提供的名称、 T-SQL 数据类型以及 XPath 表达式返回相应的结果集。在例子中第一列被命名为 Name ，对应 OpenXML 返回的 Book 节点的第一个子节点 Name 的值， T-SQL 数据类型被指定为 varchar(50) 。第二列命名为 Price 对应 OpenXML 返回的第二个节点，数据类型被指定为 decimal(9,2) 。第三列命名为 Author 对应 OpenXML 返回的第三个节点，数据类型被指定为 varchar(10) 。

如果提供的 XML 数据是以属性形式提供节点属性的，那么就可以通过设定 OpenXML 语句的第三个参数为 1 来访问 XML 数据的属性值。下面的例子脚本显示了如何返回节点的属性值。

-- 使用 OpenXML 查询

DECLARE @iHandle int

DECLARE @XMLData nvarchar(max)

SET @XMLData =

<Books>

</Books>'

EXEC sp_xml_preparedocument @iHandle OUTPUT, @XMLData

-- 注意 OpenXML 的第三个参数

SELECT *

FROM OPENXML (@iHandle, '/Books/Book',1)

WITH (Name varchar(50),

Price decimal(9,2),

Author varchar(10))

EXEC sp_xml_removedocument @iHandle

这个例子代码将返回和前一个例子相同的结果。

在有些情况下， XML 数据中不但有属性，而且还有节点值，此时就可以使用带 XPath 表达式的 With 子句来同时返回属性值和节点值。下面的例子脚本说明了如何做到这一点。

DECLARE @iHandle int

DECLARE @XMLData nvarchar(max)

-- 注意下面的 XML 数据中既有子节点值又有属性值，同属 /Books/Book 节点

SET @XMLData =

<Books>

上帝的色子

</Name>

<Price>

27.80

</Price>

</Author>

</Book>

土星的光环

</Name>

<Price>

38.22

</Price>

</Author>

</Book>

</Books>'

EXEC sp_xml_preparedocument @iHandle OUTPUT, @XMLData

-- 注意下面 With 子句中用 ’./@ID’XPath 表达式返回 ID 属性的值

SELECT *

FROM OPENXML (@iHandle, '/Books/Book',2)

WITH (ID int './@ID',

Name varchar(50),

Price decimal(9,2),

Author varchar(10))

EXEC sp_xml_removedocument @iHandle

上面的例子中使用了 OpenXML 的 2 这种模式，此时不用在 With 子句中指定要访问的子节点值的 XPath 表达式，只需按顺序命名列即可。但是此时必须使用 XPath 表达式指定要返回的属性值。如果使用了 1 这种模式，那么就必须为要访问的节点值提供 XPath 表达式，而属性值就不用提供 XPath 表达式。这实际上提供了两种方式，可以同时返回 XML 数据的内部节点值或属性值。 < xmlnamespace prefix ="o" ns ="urn:schemas-microsoft-com:office:office" />

11.7.2 FOR XML 查询简介

在前面叙述 XML 文件的用途的时候，提到过 XML 还常用来进行数据的交换。在关系型数据库中，大量存储的是基于二维表形式的数据。这些数据在交换过程中很容易因数据间缺乏有效的分割符而损坏。虽然可以使用很多种方法来避免出现这种情况，但是最方便的方法是把数据直接存储成 XML 文件形式进行交换。

在 SQL Server 2005 中， T-SQL 查询语句针对这种情况提供了 For XML 查询模式，将一个查询的结果直接返回成 XML 形式，这样就省去了用另外的程序或工具来转换二维表形式生成 XML 的步骤。

For XML 查询一共有 4 种模式，分别是： Auto 模式、 Raw 模式、 Exclipit 模式和 Path 模式。它们提供了不同的转换能力，可以针对各种不同的情况将查询的结果集转换成 XML 形式。

在下面的各小节中将依次介绍这四种 For XML 查询模式。

11.7.3 FOR XML AUTO 模式

这种模式是一种比较简单的模式，顾名思义，可以知道这种模式是基于一种自动化的方式进行二维表的数据到 XML 形式的转换的。下面的例子显示了这种模式的最简单的形式。

Use MyTest1

Select Object_id,Name,Type_Desc

From sys.tables

For XML Auto

下面是这个查询返回的部分结果。

<sys.tables Object_id="533576939" Name="Test1" Type_Desc="USER_TABLE" />

<sys.tables Object_id="629577281" Name="MyXMLTb" Type_Desc="USER_TABLE" />

从返回的结果可以看出， Auto 模式将节点自动命名成 From 后面的表名，而将列变成了对应节点的属性和属性值。这就是 Auto 模式的默认行为。

如果想生成带子节点的 XML 数据，就可以使用 For XML 查询的 Elements 选项来做到这一点。下面的例子显示了如何使用 Elements 选项。

Use MyTest1

Select Object_id,Name,Type_Desc

From sys.tables

For XML Auto,Elements

这个脚本执行后的部分结果如下。

<sys.tables>

<Object_id>629577281</Object_id>

<Name>MyXMLTb</Name>

<Type_Desc>USER_TABLE</Type_Desc>

</sys.tables>

从结果可以看出，列被变成了子节点，数据被变成了子节点的值。

在使用 Auto 模式的查询时，还可以使用 join 式的查询来指定多个表之间的子节点嵌套模式。下面的例子显示了如何做到这一点。

Select USER_Table.Object_ID,

USER_Table.Name,

USER_Table.Type,

USER_Table.Type_Desc,

Table_Column.Column_ID,

Table_Column.Name,

Table_Column.system_type_id,

Table_Column.Max_Length

From sys.tables as USER_Table

Left join sys.columns as Table_Column

On USER_Table.Object_ID = Table_Column.Object_ID

Order by USER_Table.Object_ID,Table_Column.Column_ID

For XML Auto

这个查询返回的部分结果如下。

<USER_Table Object_ID="629577281"

Name="MyXMLTb" Type="U " Type_Desc="USER_TABLE">

<Table_Column Column_ID="1" Name="XMLKID" system_type_id="56" Max_Length="4" />

<Table_Column Column_ID="2" Name="MyXML" system_type_id="241" Max_Length="-1" />

</USER_Table>

在例子中节点的名称变成了查询中表的别名，这为控制生成的 XML 数据中的节点名称提供了一种方法。同时属性名称也可以使用指定列别名的方式来控制。同时 Auto 模式试图合并多个子节点到同一个父节点之下，这从结果可以看出来。上面的结果中属于同一个表的字段被当作子节点插入到了同一个表的节点下。这也是 Auto 模式最主要的特点之一。但是这种合并是以行比较为代价的，在效率上有所损失。

嵌套的顺序通过 Select 中出现字段所属的表的顺序来确定，而不是 From 子句中的表顺序来确定。下面的例子说明了这一点。

Select Table_Column.Column_ID,

USER_Table.Object_ID,

USER_Table.Name,

USER_Table.Type,

USER_Table.Type_Desc,

Table_Column.Name,

Table_Column.system_type_id,

Table_Column.Max_Length

from sys.tables as USER_Table

Left join sys.columns as Table_Column

On USER_Table.Object_ID = Table_Column.Object_ID

Order by USER_Table.Object_ID,Table_Column.Column_ID

For XML Auto

这段脚本与前面的脚本区别仅在 Select 中提供的列顺序不同。执行后返回的部分结果如下。

<Table_Column Column_ID="1" Name="XMLKID" system_type_id="56" Max_Length="4">

<USER_Table Object_ID="629577281" Name="MyXMLTb"

Type="U " Type_Desc="USER_TABLE" />

</Table_Column>

<Table_Column Column_ID="2" Name="MyXML" system_type_id="241" Max_Length="-1">

<USER_Table Object_ID="629577281" Name="MyXMLTb"

Type="U " Type_Desc="USER_TABLE" />

</Table_Column>

从结果可以看出 Select 部分中的表顺序决定了最终的嵌套层次。在这种使用 join 的查询情况下也可以使用 Elements 选项，此处不再赘述，请读者自行试验。

11.7.4 FOR XML RAW 模式

这种模式与 Auto 模式最大的不同是，严格按照查询的行生成 XML ，而不会试图去合并相同父节点的子节点。下面的例子显示了如何使用 Raw 模式。

Select USER_Table.Object_ID,

USER_Table.Name As TName,

USER_Table.Type,

USER_Table.Type_Desc,

Table_Column.Column_ID,

Table_Column.Name As CName,

Table_Column.system_type_id,

Table_Column.Max_Length

from sys.tables as USER_Table

Left join sys.columns as Table_Column

On USER_Table.Object_ID = Table_Column.Object_ID

Order by USER_Table.Object_ID,Table_Column.Column_ID

For XML Raw

这个例子的查询部分与前一节中的查询完全相同。执行后返回的部分结果如下。

<row Object_ID="629577281" TName="MyXMLTb" Type="U " Type_Desc="USER_TABLE"

Column_ID="1" CName="XMLKID" system_type_id="56" Max_Length="4" />

<row Object_ID="629577281" TName="MyXMLTb" Type="U " Type_Desc="USER_TABLE"

Column_ID="2" CName="MyXML" system_type_id="241" Max_Length="-1" />

从例子看出， Raw 模式并没有合并相同的父节点。这就是 Raw 模式的最大特点。在 Raw 模式中同样可以使用 Elements 选项控制是生成属性，还是生成子节点。读者可以自行做试验验证。

默认情况下， Raw 模式为每行都生成一个名为 raw 的标记。可以通过为 Raw 提供名称参数来改变结果标记的名称。下面的例子显示了如何做到这一点。

Select USER_Table.Object_ID,

USER_Table.Name As TName,

USER_Table.Type,

USER_Table.Type_Desc,

Table_Column.Column_ID,

Table_Column.Name As CName,

Table_Column.system_type_id,

Table_Column.Max_Length

From sys.tables as USER_Table

Left join sys.columns as Table_Column

On USER_Table.Object_ID = Table_Column.Object_ID

Order by USER_Table.Object_ID,Table_Column.Column_ID

For XML Raw('Tables')

11.7.5 FOR XML EXCLIPIT 模式

这种模式与 Auto 和 Raw 模式的区别在于两个主要方面，第一个方面是必须按照要求提供查询中的列；第二个方面是使用指定列中的 Tag 列和 Parent 来确定节点的嵌套关系。下面的例子显示了如何使用 Exclipit 模式。

Select 1 as Tag,

NULL as Parent,

t.name as [Table!1!Name],

NULL as [Column!2!Name],

NULL as [Column!2!SQLType],

NULL as [Column!2!Length]

From sys.tables t

union all

Select 2,

t.name,

c.name,

y.name,

c.max_length

From sys.tables t

left join sys.columns c

on t.object_id = c.object_id

left join sys.types y

on c.system_type_id = y.system_type_id

order by [Table!1!Name],[Column!2!Name]

for xml explicit

注意：在 Explicit 模式中，查询的第一列必须命名为 Tag ，第二列必须命名为 Parent 。其值必须来自 Tag 的值中，表示这一行的父节点行的 Tag 值。在上面的例子中为了使 Column 列信息总是出现在对应的 Table 节点之下，使用了一个 Union All 查询做到了这一点。通常在使用 Explicit 模式都得使用这种查询。顶级节点的 Parent 值可以使 0 或 NULL ，在例子中使用了 NULL 。这也说明，不能出现 Tag 为 0 或 NULL 的情况，否则将无法生成 XML 数据。特别要注意的是查询中的 order by 子句，因为 Tag 列中的值实际上是重复的，所以在嵌套时，子节点总是找到最靠近自己的前一个 Tag 值为 Parent 值得父节点来作为自己的父节点。所以排序就保证了同一个表的列最终总是嵌套进同一个表节点的内部。

上面的例子很好的展示了 Explicit 模式的查询如何实现。其中从第三列开始的命名规则是：标记名 ! 对应得 Tag 值 ! 属性名称 ! 指示符。其中标记名表示最终将生成的标记名。对应的 Tag 值必须是正整数值，如果 Tag 列值等于该值的行，都会生成对应的标记名称的标记节点。属性名称则具体指该列值生成标记的属性或子节点的名称。指示符通常被用来定义其前的属性名称究竟是生成标记的属性，还是生成标记的子节点。如果要生成子节点只需为指示符提供值 Element 即可。如果连指示符和其前的“ ! ”一起省略，那么该列就会生成属性值。下面的例子说明了如何使用指示符。

Select 1 as Tag,

NULL as Parent,

t.name as [Table!1!Name!element],

NULL as [Column!2!Name!Element],

NULL As [Column!2!ID],

NULL as [Column!2!SQLType!ELEMENT],

NULL as [Column!2!Length!ELEMENT]

From sys.tables t

union all

Select 2,

t.name,

c.name,

c.Column_id,

y.name,

c.max_length

From sys.tables t

left join sys.columns c

on t.object_id = c.object_id

left join sys.types y

on c.system_type_id = y.system_type_id

order by [Table!1!Name!element],[Column!2!Name!Element]

for xml explicit

注意：上面的例子中 Element 指示符有大小写混拼的情况，这是为了说明这个按要求提供的列别名是不区分大小写的。

综合而言， Explicit 模式提供了一种比 Auto 和 Raw 模式更灵活的模式。可以按需要指定某列生成属性，还是生成子节点。这是 Auto 和 Raw 模式无法做到的。

仔细观察所有这三种 For XML 查询模式的结果，以及将要介绍的 Path 模式的结果，就可以发现，所有这些结果都缺乏顶级标记，或者说缺乏一个最外层的根节点。这样生成的 XML 结果就是不符合 XML 规范的结果。在 For XML 任一模式下可以使用 Root 选项来为结果 XML 添加根节点，从而使生成的 XML 符合规范要求。下面的例子显示了如何使用 Root 选项。

Select 1 as Tag,

NULL as Parent,

t.name as [Table!1!Name!element],

NULL as [Column!2!Name!Element],

NULL As [Column!2!ID],

NULL as [Column!2!SQLType!ELEMENT],

NULL as [Column!2!Length!ELEMENT]

From sys.tables t

union all

Select 2,

t.name,

c.name,

c.Column_id,

y.name,

c.max_length

From sys.tables t

left join sys.columns c

on t.object_id = c.object_id

left join sys.types y

on c.system_type_id = y.system_type_id

order by [Table!1!Name!element],[Column!2!Name!Element]

for xml explicit,Root('Tables')

注意：在即将要介绍的 Path 模式中也可以使用 Root 选项来指定根元素。

11.7.6 FOR XML PATH 模式

For XML Path 模式是一种利用 XPath 表达式决定结果中列的嵌套关系来生成 XML 数据的 For XML 查询模式。由于使用了灵活而强大的 XPath 表达式，所以这种查询模式也是也是最灵活的 For XML 查询模式。它也别前一种 Explicit 模式要简单。下面的例子显示了如何使用 For XML Path 模式。

-- 注意查询中列的别名就是 XPath 表达式，

--For XML Path 将根据 XPath 来决定对应列数据的嵌套关系

Select t.Object_id as "Tables/Table/@Table_ID",

t.name as "Tables/Table/Name",

c.column_id as "Tables/Table/Column/@Column_ID",

c.name as "Tables/Table/Column/Name"

From sys.tables t

Left join sys.columns c

On t.object_id = c.object_id

for xml path

这个查询的部分结果如下所示。

<Name>MyXMLTb</Name>

<Name>XMLKID</Name>

</Column>

</Table>

</Tables>

<Name>MyXMLTb</Name>

<Name>MyXML</Name>

</Column>

</Table>

</Tables>

从结果可以看出， XPath 在行集的处理上类似于 Raw 模式。都是按行来生成 XML 节点。这就带来了像上面结果中的一个问题，相同表的列被分到了两个 row 标记中。如果要避免出现这种情况，可以使用给 Path 模式提供行名称，同时使用子查询来做到。下面例子显示了如何做到这一点。

-- 注意子查询被一个 Convert 函数进行了一个类型转换，转成了 XML 类型

-- 如果不这样转换，子查询的结果就不会被当作 XML 子节点来处理

Select t.Object_id as "@Table_ID",

t.name as "Name",

Convert(xml,

Select c.column_id as "@Column_ID",

c.name as "Name",

p.name as "SQL-Type",

c.Max_Length As "Length"

From sys.columns c

Left join sys.types p

On c.system_type_id = p.system_type_id

Where t.object_id = c.object_id

For XML Path('Column')

As "Columns"

From sys.tables t

for xml path('Table')

上面的脚本返回的部分结果如下所示。

<Name>MyXMLTb</Name>

<Name>XMLKID</Name>

<SQL-Type>int</SQL-Type>

</Column>

<Name>MyXML</Name>

<SQL-Type>xml</SQL-Type>

</Column>

</Columns>

</Table>

从结果可以看出，这个查询的结果的格式已经比较合理了。从 XML 可以一眼看出这是一个数据表对象的 XML 数据。其中包含两个列。同时可以容易的知道这些列的名称、数据类型、长度等信息。

仔细分析这个结果还可以发现，如果使用前三种 For XML 模式都无法容易的生成这个结果 XML 。这也是 Path 模式比前三种 For XML 查询模式灵活方便的原因。

11.8 小结

在这一章中，认识了什么是 XML ，认识了 SQL Server 2005 中 XML 数据类型的使用方法。认识了什么是 XML 架构，以及如何用 XML 架构定义 XML 数据的格式。认识了什么是 XML 架构集合，以及如何在 SQL Server 2005 中管理 XML 架构集合。掌握了如何使用 XQuery 来查询 XML 数据的内容。掌握了如何管理 XML 索引，同时了解了 XML 索引如何改进了 XQuery 的效率。最后掌握了如何使用 OpenXML 将对 XML 数据的查询结果转化成二维结果集的形式，以及这一过程的逆过程 For XML 查询。

通过这一章的学习就基本了解了 XML 在 SQL Server 2005 中的主要方面。并具备了在 SQL Server 2005 中全面使用 XML 的基本技能。