使用Microsoft SQL Server 2000的XML查询

使用Microsoft SQL Server 2000的XML查询

2005-6-28

由于XML本身的诸多优点，XML技术已被广泛的使用，目前的好多软件技术同XML紧密相关，比如微软的.net 平台对xml提供了强大的支持，提供System.Xml以及其子命名空间下的类型来操作xml。Ado.net通过核心类型DataSet出色的把关系型数据库同xml进行了紧密集成。由于平常许多开发人员使用.net 来操作Sql server的到数据集后再转换成xml，所以往往忽略Transact-SQL查询生成XML数据的强大功能。对于一些项目使用XML查询直接通过SQL生成xml会来的更为简便，所以我通过在实际项目中的使用和查阅一些资料写成一个知识点，一是温故而知新，二是对于一些开发者刚好需要这方面的技术而还没有找到比较快捷的学习方式提供一条途径。

在SQL SERVER 2000中查询生成XML的语法表达式比较简洁，整个语法如下：

SELECT <select_list>

FROM <table_source>

WHERE <search_condition>

FOR XML AUTO | RAW | EXPLICIT [,XMLDATA ] [,ELEMENTS] [,BINARY BASE64]

下面我将以Northwind数据库来演示上面的表达式中所包含的各项功能，下面的查询语句和返回结果都通过SQL SERVER 2000查询分析器来执行和得到。

 

一． 使用AUTO模式

该模式我认为在生成单表xml数据方面是用得最多的，能满足一般的需要。先来看他的简单查询。

1．简单查询

查询语句：

SELECT CategoryID,

CategoryName

FROM Categories

WHERE CategoryID < 3 FOR XML AUTO

返回结果：

<Categories CategoryID="1" CategoryName="Beverages"/>

<Categories CategoryID="2" CategoryName="Condiments"/>

也可以使用别名，

查询语句：

SELECT CategoryID AS ID,

CategoryName,

GetDate() as CurrDate

FROM Categories MyTable

WHERE CategoryID < 3 FOR XML AUTO

返回结果：

<MyTable ID="1" CategoryName="Beverages" CurrDate="2005-06-24T11:09:52.937"/>

<MyTable ID="2" CategoryName="Condiments" CurrDate="2005-06-24T11:09:52.937"/>

2．连接查询

以两个表为例，

查询语句：

SELECT Categories.CategoryID,

Categories.CategoryName,

ProductID,

ProductName

FROM Categories 

JOIN Products ON Categories.CategoryID = Products.CategoryID AND ProductID <5

WHERE Categories.CategoryID < 3 FOR XML AUTO

返回结果：

<Categories CategoryID="1" CategoryName="Beverages">

<Products ProductID="1" ProductName="Chai"/>

</Categories>

<Categories CategoryID="2" CategoryName="Condiments">

<Products ProductID="2" ProductName="Chang"/>

</Categories>

可以看到表连接查询可以生成分层次的Xml，不过需要注意的是SELECT子句中的父表的列要排在子表的列的前面，否则会出现你不想看到的结果，如：

查询语句：

SELECT ProductID,Categories.CategoryID,Categories.CategoryName,ProductName

FROM Categories 

JOIN Products ON Categories.CategoryID = Products.CategoryID  and ProductID <5

WHERE Categories.CategoryID <3  FOR XML AUTO

返回结果：

<Products ProductID="1" ProductName="Chai">

         <Categories CategoryID="1" CategoryName="Beverages"/>

</Products>

         <Products ProductID="2" ProductName="Chang">

         <Categories CategoryID="1" CategoryName="Beverages"/>

</Products>

<Products ProductID="3" ProductName="Aniseed Syrup">

         <Categories CategoryID="2" CategoryName="Condiments"/>

</Products>

<Products ProductID="4" ProductName="Chef Anton&apos;s Cajun Seasoning">

         <Categories CategoryID="2" CategoryName="Condiments"/>

</Products>

3．使用ELEMENTS选项

使用该选项可以生成以元素为中心的Xml表示，默认为属性方式，不过属性方式节省空间。需要注意的是使用ELEMENTS选项是一种全是或全否的形式，不能得到一部分是以元素表示而另一部分以属性表示的Xml数据。

查询语句：

SELECT CategoryID,

CategoryName

FROM Categories

WHERE CategoryID < 3

FOR XML AUTO, ELEMENTS

返回结果：

<Categories>

         <CategoryID>1</CategoryID>

         <CategoryName>Beverages</CategoryName>

</Categories>

<Categories>

         <CategoryID>2</CategoryID>

         <CategoryName>Condiments</CategoryName>

</Categories>

在连接查询时，

查询语句：

SELECT Categories.CategoryID,

ProductID,

ProductName

FROM Categories 

JOIN Products ON Categories.CategoryID = Products.CategoryID  and ProductID <4

WHERE Categories.CategoryID <3  FOR XML AUTO, ELEMENTS

返回结果：

<Categories>

         <CategoryID>1</CategoryID>

         <Products>

<ProductID>1</ProductID>

                   <ProductName>Chai</ProductName>

         </Products>

         <Products>

                   <ProductID>2</ProductID>

                   <ProductName>Chang</ProductName>

         </Products>

</Categories>

<Categories>

         <CategoryID>2</CategoryID>

         <Products>

                   <ProductID>3</ProductID>

                   <ProductName>Aniseed Syrup</ProductName>

         </Products>

</Categories>

4．检索对二进制数据的XPath引用

这是对二进制数据的操作，

查询语句：

SELECT CategoryID,

Picture

FROM Categories

WHERE CategoryID = 1

FOR XML AUTO

返回结果：

<Categories CategoryID="1" Picture="dbobject/Categories[@CategoryID='1']/@Picture"/>

使用ELEMENTS方式，

查询语句：

SELECT CategoryID,

Picture

FROM Categories

WHERE CategoryID = 1

FOR XML AUTO,ELEMENTS

返回结果：

<Categories>

         <CategoryID>1</CategoryID>

         <Picture>dbobject/Categories[@CategoryID='1']/@Picture</Picture>

</Categories>

 

二．使用RAW模式

使用RAW模式不能使用ELEMENTS选项。

1．简单查询

查询语句：

SELECT CategoryID,

CategoryName AS Nanme

FROM Categories

WHERE CategoryID < 3

ORDER BY CategoryID DESC

FOR XML RAW

返回结果：

<row CategoryID="2" Nanme="Condiments"/>

<row CategoryID="1" Nanme="Beverages"/>

2．连接查询

查询语句：

SELECT Categories.CategoryID,

Categories.CategoryName,

ProductID,

ProductName

FROM Categories 

JOIN Products ON Categories.CategoryID = Products.CategoryID  and ProductID <4

WHERE Categories.CategoryID <= 2  FOR XML RAW

返回结果：

<row CategoryID="1" CategoryName="Beverages" ProductID="1" ProductName="Chai"/>

<row CategoryID="1" CategoryName="Beverages" ProductID="2" ProductName="Chang"/>

<row CategoryID="2" CategoryName="Condiments" ProductID="3" ProductName="Aniseed Syrup"/>

 

三．使用EXPLICIT模式

该模式使用起来相对比较复杂，不过它可以很灵活的控制返回的xml数据结构。在该查询中定义了两个表示元数据的额外列。Tag列唯一的确定用来在结果中表示每一行的xml标记，Parent列用来控制元素之间的嵌套关系。

1．使用通用表

EXPLICIT模式下有一个通用表的概念，使用数据列的名称来定义xml文档中的数据。结构如下：

ElementName!TagNumber!AttributeName!Directive

下面的说明来自联机丛书，

ElementName

是所得到的元素类属标识符（例如，如果将 Customers 指定为ElementName，则 <Customers> 是元素标记）。

TagNumber

是元素的标记号。借助于通用表中的两个元数据列（Tag 和 Parent），TagNumber用于表示 XML 树中的 XML 元素嵌套。每个TagNumber都准确对应于一个ElementName。

AttributeName

是 XML 特性的名称（如果没有指定Directive）或包含的元素名（如果Directive是 xml、cdata 或 element）。如果指定Directive，则AttributeName可以为空。这种情况下，列中包含的值直接由具有指定ElementName的元素所包含。

Directive

是可选命令。如果没有指定Directive，则必须指定AttributeName。如果没有指定AttributeName且没有指定Directive（如 Customer!1），则表示元素命令（如 Customer!1!!element）且包含数据。

Directive有两种用途。该选项用于分别使用关键字 ID、IDREF 和 IDREFS 对 ID、IDREF 和 IDREFS 进行编码。还用于表示如何使用关键字 hide、element、xml、xmltext 和 cdata 将字符串数据映射到 XML。大多数情况下允许在这些组中组合指令，但是不能在组本身中进行组合。

ID

可将元素特性指定为 ID 类型的特性。然后可以使用 IDREF 和 IDREFS 特性引用它们，以启用文档内的链接。但是，如果没有请求 XMLDATA，则此关键字无效。

IDREF

指定为 IDREF 的特性可用于引用 ID 类型的特性，以启用文档内的链接。但是，如果没有请求 XMLDATA，则此关键字无效。

IDREFS

指定为 IDREFS 的特性可用于引用 ID 类型的特性，以启用文档内的链接。但是，如果没有请求 XMLDATA，则此关键字无效。

hide

不显示特性。这对于按照不出现在结果中的特性对结果进行排序可能很有用。

element

不生成特性。而是生成具有指定名称的包含元素（如果没有指定特性名则直接生成包含元素）。包含数据被编码为实体（例如，字符 < 变成 &lt;）。该关键字可以与 ID、IDREF 或 IDREFS 组合。

xml

除了不进行实体编码外，该命令与元素命令相同（例如，字符 < 仍是 <）。除了 hide 外，该命令不能与任何其它命令一起使用。

xmltext

列内容应包在单个标记内，以便与文档的其它部分集成。如果指定了AttributeName，则标记名由指定名称替换；否则通过将内容放在容器的起始处而不进行实体编码，将特性追加到包含元素的当前特性列表。含有该命令的列必须是文本类型（varchar、nvarchar、char、nchar、text、ntext）。该命令只能与 hide 一起使用。在提取存储在列中的溢出数据时该命令很有用。 如果内容不是有效的 XML，则该行为不明确。

cdata

通过用 CDATA 节环绕数据来包含数据。不对内容进行实体编码。原始数据类型必须是文本类型（varchar、nvarchar、text、ntext）。该命令只能与 hide 一起使用。当使用该命令时，不应指定AttributeName。

2．简单查询

在指定数据所在的元素的名称和标记时必须使用ElementName参数和Tag参数，也就是查询语句中表中存在的每一个实际的数据列都必须以ElementName!Tag开头。在没有指定AttributeName!Directive的情况下以元素值的形式返回。在只查询一个表的情况下Tag为1，Parent为NULL。

使用EXPLICIT模式来查询数据我认为使用倒推的方式会比较方便一些，比如我要得到如下结构，

<Category ID=CategoryID Name=CategoryName>Description</Category>

查询语句：

SELECT 1 AS Tag,NULL AS Parent,

CategoryID AS [Category!1!ID],

CategoryName AS [Category!1!Name],

Description AS [Category!1]

FROM Categories

WHERE categoryID > 6 FOR XML EXPLICIT

返回结果：

<Category ID="7" Name="Produce">Dried fruit and bean curd</Category>

<Category ID="8" Name="Seafood">Seaweed and fish</Category>

当然下面的语句同上面的等效，

SELECT 1 Tag,NULL Parent,

CategoryID  [Category!1!ID],

CategoryName  [Category!1!Name],

Description  [Category!1]

FROM Categories

WHERE categoryID > 6 FOR XML EXPLICIT

2．连接查询

在EXPLICIT模式下的多表连接查询可能相对比较复杂些，先来看一个简单的例子，

查询语句：

SELECT 1 AS Tag,NULL AS Parent,

Categories.CategoryID AS [Category!1!ID],

Categories.CategoryName AS [Category!1!!element],

ProductID  AS [Category!1!ProductID!element],

ProductName AS  [Category!1!ProductName!element]

FROM Categories 

JOIN Products ON Categories.CategoryID = Products.CategoryID AND ProductID <5

WHERE Categories.CategoryID < 3 FOR XML EXPLICIT

返回结果：

<Category ID="1">

         Beverages

         <ProductID>1</ProductID>

         <ProductName>Chai</ProductName>

</Category>

<Category ID="1">

         Beverages

         <ProductID>2</ProductID>

         <ProductName>Chang</ProductName>

</Category>

<Category ID="2">

         Condiments

         <ProductID>3</ProductID>

         <ProductName>Aniseed Syrup</ProductName>

</Category>

<Category ID="2">

         Condiments

         <ProductID>4</ProductID>

         <ProductName>Chef Anton&apos;s Cajun Seasoning</ProductName>

</Category>

从上面的结构可以看出数据的xml结构不是很舒服，存在比较大的冗余。想象中改进后的结构如下：

<Category ID="2" Name="Condiments">

         <Product>

                   <ProductID>3</ProductID>

                   <ProductName>Aniseed Syrup</ProductName>

         </Product>

         <Product>

                   <ProductID>4</ProductID>

                   <ProductName>Chef Anton&apos;s Cajun Seasoning</ProductName>

         </Product>

</Category>

<Category ID="1" Name="Beverages">

         <Product>

                   <ProductID>1</ProductID>

                   <ProductName>Chai</ProductName>

         </Product>

         <Product>

                   <ProductID>2</ProductID>

                   <ProductName>Chang</ProductName>

         </Product>

</Category>

从上面看出Category元素的Tag为1，Parent为NULL，而Product元素的Tag为2，Parent为1（即Category的Tag值）。这里有一个问题需要解决，那就是在Tag和Parent中存在两套值，这里就需要使用UNION ALL运算符来实现这项功能，UNION ALL运算符的一项功能就是消除查询返回的重复的行。使用UNION ALL时需要注意的是结果集的列数必须相同。

查询语句：

SELECT 1 AS Tag,NULL AS Parent,

CategoryID AS [Category!1!ID],

CategoryName AS [Category!1!Name],

NULL  AS [Product!2!ProductID!element],

NULL  AS [Product!2!ProductName!element]

FROM Categories  WHERE CategoryID < 3

UNION ALL

SELECT 2 AS Tag,1 AS Parent,

Categories.CategoryID AS [Category!1!ID],

Categories.CategoryName AS [Category!1!Name],--这里可以使用NULL代替

ProductID  AS [Product!2!ProductID!element],

ProductName  AS [Product!2!ProductName!element]

FROM Categories

JOIN Products ON Categories.CategoryID = Products.CategoryID AND ProductID <5

WHERE Categories.CategoryID < 3

ORDER BY [Category!1!ID] DESC,[Product!2!ProductID!element] ASC

FOR XML EXPLICIT

返回结果：

<Category ID="2" Name="Condiments">

         <Product>

                   <ProductID>3</ProductID>

                   <ProductName>Aniseed Syrup</ProductName>

         </Product>

         <Product>

                   <ProductID>4</ProductID>

                   <ProductName>Chef Anton&apos;s Cajun Seasoning</ProductName>

         </Product>

</Category>

<Category ID="1" Name="Beverages">

         <Product>

                   <ProductID>1</ProductID>

                   <ProductName>Chai</ProductName>

         </Product>

         <Product>

                   <ProductID>2</ProductID>

                   <ProductName>Chang</ProductName>

         </Product>

</Category>

不带FOR XML EXPLICIT语句的结果为：

Tag  Parent      Category!1! ID Category!1!Name         Product!2!            Product!2!          

                                                                             ProductID!element      ProductName!element 

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

1    NULL    2             Condiments     NULL                      NULL

2    1         2             Condiments      3                            Aniseed Syrup

2    1         2             Condiments      4                            Chef Anton's Cajun Seasoning

1    NULL    1              Beverages        NULL                      NULL

2    1         1             Beverages        1                            Chai

2    1         1             Beverages        2                            Chang

3．使用EXPLICIT模式查询中的指令

这些指令是指通用表列的第四部分，通过它对数据进行进一步控制。

1>     最常用的element和xml指令

通过该指令把列的数据显示为一个子元素而不是属性。

查询语句：

SELECT 1 AS Tag,NULL AS Parent,

CategoryID AS [Category!1!ID],

CategoryName  [Category!1!Name],

Description  [Category!1!!element]

FROM Categories

WHERE categoryID > 6 FOR XML EXPLICIT

返回结果：

<Category ID="7" Name="Produce">Dried fruit and bean curd</Category>

<Category ID="8" Name="Seafood">Seaweed and fish</Category>

查询语句：

SELECT 1 AS Tag,NULL AS Parent,

CategoryID AS [Category!1!ID],

CategoryName  [Category!1!Name],

Description  [Category!1!Description!element]

FROM Categories

WHERE categoryID > 6 FOR XML EXPLICIT

返回结果：

<Category ID="7" Name="Produce">

         <Description>Dried fruit and bean curd</Description>

</Category>

<Category ID="8" Name="Seafood">

         <Description>Seaweed and fish</Description>

查询语句：

SELECT 1 AS Tag,NULL AS Parent,

CategoryID AS [Category!1!ID],

CategoryName  [Category!1],

Description  [Category!1!Description!element]

FROM Categories

WHERE categoryID > 6 FOR XML EXPLICIT

返回结果：

<Category ID="7">

         Produce

         <Description>Dried fruit and bean curd</Description>

</Category>

<Category ID="8">

         Seafood

         <Description>Seaweed and fish</Description>

</Category>

xml指令同element指令差不多，只是element指令会将一些xml下面的符号进行编码，如>被编码成&gt;5，而xml指令不会。

2>     使用hide指令

该指令是用来处理那些不想返回结果的列，许多人会有疑问觉得没有必要，只要在列中不增加就行了，条件和排序也可以直接使用列。我们可以想得到使用UNION ALL时就用得上了，看下面的例子。

查询语句：

SELECT 1 AS Tag,NULL AS Parent,

CategoryID AS [Category!1!ID!hide],

CategoryName AS [Category!1!Name],

NULL  AS [Product!2!ProductID!element],

NULL  AS [Product!2!ProductName!element]

FROM Categories  WHERE CategoryID < 3

UNION ALL

SELECT 2 ,1 ,

Categories.CategoryID,

NULL,

ProductID,

ProductName

FROM Categories

JOIN Products ON Categories.CategoryID = Products.CategoryID AND ProductID <5

WHERE Categories.CategoryID < 3

ORDER BY [Category!1!ID!hide] DESC,[Product!2!ProductID!element] ASC

FOR XML EXPLICIT

返回结果：

<Category Name="Condiments">

         <Product>

                   <ProductID>3</ProductID>

                   <ProductName>Aniseed Syrup</ProductName>

         </Product>

                   <Product>

                   <ProductID>4</ProductID>

         <ProductName>Chef Anton&apos;s Cajun Seasoning</ProductName>

         </Product>

</Category>

<Category Name="Beverages">

         <Product>

                   <ProductID>1</ProductID>

                   <ProductName>Chai</ProductName>

         </Product>

                   <Product><ProductID>2</ProductID>

                   <ProductName>Chang</ProductName>

         </Product>

</Category>

3>     使用xmltext指令

该指令是用来处理包含xml片断内容的数据列，没找到现成的表结构和数据进行测试，所以就在Categories表中增加了XmlData列名，类型为nvarchar，默认值设置为“<customData email="xxx(at)hotmail.com" />”。

查询语句：

SELECT 1 AS Tag,NULL AS Parent,

CategoryID AS [Category!1!ID],

XmlData AS [Category!1!CustomData!xmltext]

FROM Categories WHERE CategoryID = 8 FOR XML EXPLICIT

返回结果：

<Category ID="8"><CustomData email="xxx(at)hotmail.com"/></Category>

查询语句：

SELECT 1 AS Tag,NULL AS Parent,

CategoryID AS [Category!1!ID],

XmlData AS [Category!1!!xmltext]

FROM Categories WHERE categoryID = 8 FOR XML EXPLICIT

返回结果：

<Category ID="8" email="xxx(at)hotmail.com"></Category>

4>     使用cdata指令

使用该指令可以创建CDATA节防止一些字符数据被xml解析器。

查询语句：

SELECT 1 AS Tag,NULL AS Parent,

CategoryID AS [Category!1!ID],

XmlData AS [Category!1!!cdata]

FROM Categories WHERE categoryID = 6 FOR XML EXPLICIT

返回结果：

<Category ID="6"><![CDATA[<customData email="xxx(at)hotmail.com" />]]></Category>

5>     使用ID、IDREF和IDREFS指令以及XMLDATA选项

由于这里只能返回XDR构架，不支持XSD架构的检索，我就只给出一个例子，

查询语句：

SELECT 1 AS Tag,NULL AS Parent,

CategoryID AS [Category!1!ID!ID],

CategoryName AS [Category!1!Name],

NULL  AS [Product!2!ProductID!IDREF],

NULL  AS [Product!2!ProductName]

FROM Categories  WHERE CategoryID < 3

UNION ALL

SELECT 2 ,1 ,

Categories.CategoryID,

NULL,

ProductID,

ProductName

FROM Categories

JOIN Products ON Categories.CategoryID = Products.CategoryID AND ProductID <5

WHERE Categories.CategoryID < 3

ORDER BY [Category!1!ID!ID] ,[Product!2!ProductID!IDREF]

FOR XML EXPLICIT,XMLDATA

返回结果：

<Schema name="Schema2" xmlns="urn:schemas-microsoft-com:xml-data" xmlns:dt="urn:schemas-microsoft-com:datatypes">

         <ElementType name="Category" content="mixed" model="open">

                   <AttributeType name="ID" dt:type="id"/>

                   <AttributeType name="Name" dt:type="string"/>

                   <attribute type="ID"/>

                   <attribute type="Name"/>

         </ElementType>

         <ElementType name="Product" content="mixed" model="open">

                   <AttributeType name="ProductID" dt:type="idref"/>

                   <AttributeType name="ProductName" dt:type="string"/>

                   <attribute type="ProductID"/>

                   <attribute type="ProductName"/>

         </ElementType>

</Schema>

<Category xmlns="x-schema:#Schema2" ID="1" Name="Beverages">

         <Product ProductID="1" ProductName="Chai"/>

         <Product ProductID="2" ProductName="Chang"/>

</Category>

<Category xmlns="x-schema:#Schema2" ID="2" Name="Condiments">

         <Product ProductID="3" ProductName="Aniseed Syrup"/>

         <Product ProductID="4" ProductName="Chef Anton&apos;s Cajun Seasoning"/>

</Category>

 

四．使用BINARY BASE64 选项

使用该选项可以检索二进制字段，比如图像数据以base64的形式返回到xml文档。

1．使用RAW方式

查询语句：

SELECT CategoryID,

Picture

FROM Categories

WHERE CategoryID =1

ORDER BY CategoryID DESC

FOR XML RAW,BINARY BASE64

返回结果：

<row CategoryID="1" Picture="FRwvAAIAAAANAA4AFAAhAP9CaXRtYXAgSW..."/>

2．使用AUTO方式

查询语句：

SELECT CategoryID,

Picture

FROM Categories

WHERE CategoryID =2

ORDER BY CategoryID DESC

FOR XML AUTO,BINARY BASE64

返回结果：

<Categories CategoryID="2" Picture="FRwvAAIAAAANAA4AFAAhAP..."/>

查询语句：

SELECT CategoryID,

Picture

FROM Categories

WHERE CategoryID =1

ORDER BY CategoryID DESC

FOR XML AUTO,ELEMENTS,BINARY BASE64

返回结果：

<Categories>

         <CategoryID>1</CategoryID>

         <Picture>FRwvAAIAAAANAA4AFAAhAP9CaXRt...</Picture>

</Categories>

 

         总结，通过上文比较全面的熟悉了 SQL SERVER 2000 下检索生成 XML 数据的功能。在平常的项目开发中你也许就会用到上述功能，使得项目的开发省去一部分的时间和精力。上述的 Xml 查询功能在 ado.net 下得到了很好的支持，这也是本人比较感兴趣的地方。本文是我对 《SQL SERVER 2000与XML数据库编程（第2版）》 一书第二章所讲技术的再次实践。如果需要联系可以访问我的 Blog ： http://blog.csdn.net/zhzuo 。

posted on 2005-10-08 21:42  秋枫 阅读( ...) 评论( ...) 编辑 收藏

转载于:https://www.cnblogs.com/zhzuo/archive/2005/10/08/250542.html