7、XML数据类型的高级应用

来源：互联网发布：淘宝助理4.6版下载编辑：程序博客网时间：2024/04/26 06:17

在本章前面的各小节中，主要围绕在SQL Server 2005中如何对XML数据本身操作的方法进行了讲解。这一节的内容则是围绕如何将T-SQL查询的结果转换成XML形式，以及如何将对XML数据的查询转换成T-SQL结果集的形式。

< 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>

<Book>

<Name>

上帝的色子

</Name>

<Price>

27.80

</Price>

尚娣

</Author>

</Book>

<Book>

<Name>

土星的光环

</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>

上帝的色子

</Name>

<Price>

27.80

</Price>

尚娣

</Author>

</Book>

<Name>

土星的光环

</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

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

<row>

<Name>MyXMLTb</Name>

<Name>XMLKID</Name>

</Column>

</Table>

</Tables>

</row>

<row>

<Name>MyXMLTb</Name>

<Name>MyXML</Name>

</Column>

</Table>

</Tables>

</row>

从结果可以看出，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的基本技能。