由于XML本身的諸多優(yōu)點(diǎn)，XML技術(shù)已被廣泛的使用，目前的好多軟件技術(shù)同XML緊密相關(guān)，比如微軟的.net 平臺對xml提供了強(qiáng)大的支持，提供System.Xml以及其子命名空間下的類型來操作xml。Ado.net通過核心類型DataSet出色的把關(guān)系型數(shù)據(jù)庫同xml進(jìn)行了緊密集成。由于平常許多開發(fā)人員使用.net 來操作SQL Server的到數(shù)據(jù)集后再轉(zhuǎn)換成xml，所以往往忽略Transact-SQL查詢生成XML數(shù)據(jù)的強(qiáng)大功能。對于一些項(xiàng)目使用XML查詢直接通過SQL生成xml會來的更為簡便，所以我通過在實(shí)際項(xiàng)目中的使用和查閱一些資料寫成一個(gè)知識點(diǎn)，一是溫故而知新，二是對于一些開發(fā)者剛好需要這方面的技術(shù)而還沒有找到比較快捷的學(xué)習(xí)方式提供一條途徑。

在SQL SERVER 2000中查詢生成XML的語法表達(dá)式比較簡潔，整個(gè)語法如下：

SELECT

FROM

WHERE

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

下面我將以Northwind數(shù)據(jù)庫來演示上面的表達(dá)式中所包含的各項(xiàng)功能，下面的查詢語句和返回結(jié)果都通過SQL SERVER 2000查詢分析器來執(zhí)行和得到。

一．使用AUTO模式

該模式我認(rèn)為在生成單表xml數(shù)據(jù)方面是用得最多的，能滿足一般的需要。先來看他的簡單查詢。

1．簡單查詢

查詢語句：

SELECT CategoryID,

CategoryName

FROM Categories

WHERE CategoryID < 3 FOR XML AUTO

返回結(jié)果：

也可以使用別名，

查詢語句：

SELECT CategoryID AS ID,

CategoryName,

GetDate() as CurrDate

FROM Categories MyTable

WHERE CategoryID < 3 FOR XML AUTO

返回結(jié)果：

2．連接查詢

以兩個(gè)表為例，

查詢語句：

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

返回結(jié)果：

可以看到表連接查詢可以生成分層次的Xml，不過需要注意的是SELECT子句中的父表的列要排在子表的列的前面，否則會出現(xiàn)你不想看到的結(jié)果，如：

查詢語句：

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

返回結(jié)果：

3．使用ELEMENTS選項(xiàng)

使用該選項(xiàng)可以生成以元素為中心的Xml表示，默認(rèn)為屬性方式，不過屬性方式節(jié)省空間。需要注意的是使用ELEMENTS選項(xiàng)是一種全是或全否的形式，不能得到一部分是以元素表示而另一部分以屬性表示的Xml數(shù)據(jù)。

查詢語句：

SELECT CategoryID,

CategoryName

FROM Categories

WHERE CategoryID < 3

FOR XML AUTO, ELEMENTS

返回結(jié)果：

1

Beverages

2

Condiments

在連接查詢時(shí)，

查詢語句：

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

返回結(jié)果：

1

1

Chai

2

Chang

2

3

Aniseed Syrup

4．檢索對二進(jìn)制數(shù)據(jù)的XPath引用

這是對二進(jìn)制數(shù)據(jù)的操作，

查詢語句：

SELECT CategoryID,

Picture

FROM Categories

WHERE CategoryID = 1

FOR XML AUTO

返回結(jié)果：

使用ELEMENTS方式，

查詢語句：

SELECT CategoryID,

Picture

FROM Categories

WHERE CategoryID = 1

FOR XML AUTO,ELEMENTS

返回結(jié)果：

1

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

二．使用RAW模式

使用RAW模式不能使用ELEMENTS選項(xiàng)。

1．簡單查詢

查詢語句：

SELECT CategoryID,

CategoryName AS Nanme

FROM Categories

WHERE CategoryID < 3

ORDER BY CategoryID DESC

FOR XML RAW

返回結(jié)果：

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

返回結(jié)果：

三．使用EXPLICIT模式

該模式使用起來相對比較復(fù)雜，不過它可以很靈活的控制返回的xml數(shù)據(jù)結(jié)構(gòu)。在該查詢中定義了兩個(gè)表示元數(shù)據(jù)的額外列。Tag列唯一的確定用來在結(jié)果中表示每一行的xml標(biāo)記，Parent列用來控制元素之間的嵌套關(guān)系。

1．使用通用表

EXPLICIT模式下有一個(gè)通用表的概念，使用數(shù)據(jù)列的名稱來定義xml文檔中的數(shù)據(jù)。結(jié)構(gòu)如下：

ElementName!TagNumber!AttributeName!Directive

下面的說明來自聯(lián)機(jī)叢書，

ElementName

是所得到的元素類屬標(biāo)識符（例如，如果將 Customers 指定為ElementName，則 是元素標(biāo)記）。

TagNumber

是元素的標(biāo)記號。借助于通用表中的兩個(gè)元數(shù)據(jù)列（Tag 和 Parent），TagNumber用于表示 XML 樹中的 XML 元素嵌套。每個(gè)TagNumber都準(zhǔn)確對應(yīng)于一個(gè)ElementName。

AttributeName

是 XML 特性的名稱（如果沒有指定Directive）或包含的元素名（如果Directive是 xml、cdata 或 element）。如果指定Directive，則AttributeName可以為空。這種情況下，列中包含的值直接由具有指定ElementName的元素所包含。

Directive

是可選命令。如果沒有指定Directive，則必須指定AttributeName。如果沒有指定AttributeName且沒有指定Directive（如 Customer!1），則表示元素命令（如 Customer!1!!element）且包含數(shù)據(jù)。

Directive有兩種用途。該選項(xiàng)用于分別使用關(guān)鍵字 ID、IDREF 和 IDREFS 對 ID、IDREF 和 IDREFS 進(jìn)行編碼。還用于表示如何使用關(guān)鍵字 hide、element、xml、xmltext 和 cdata 將字符串?dāng)?shù)據(jù)映射到 XML。大多數(shù)情況下允許在這些組中組合指令，但是不能在組本身中進(jìn)行組合。

ID

可將元素特性指定為 ID 類型的特性。然后可以使用 IDREF 和 IDREFS 特性引用它們，以啟用文檔內(nèi)的鏈接。但是，如果沒有請求 XMLDATA，則此關(guān)鍵字無效。

IDREF

指定為 IDREF 的特性可用于引用 ID 類型的特性，以啟用文檔內(nèi)的鏈接。但是，如果沒有請求 XMLDATA，則此關(guān)鍵字無效。

IDREFS

指定為 IDREFS 的特性可用于引用 ID 類型的特性，以啟用文檔內(nèi)的鏈接。但是，如果沒有請求 XMLDATA，則此關(guān)鍵字無效。

hide

不顯示特性。這對于按照不出現(xiàn)在結(jié)果中的特性對結(jié)果進(jìn)行排序可能很有用。

element

不生成特性。而是生成具有指定名稱的包含元素（如果沒有指定特性名則直接生成包含元素）。包含數(shù)據(jù)被編碼為實(shí)體（例如，字符 < 變成 <）。該關(guān)鍵字可以與 ID、IDREF 或 IDREFS 組合。

xml

除了不進(jìn)行實(shí)體編碼外，該命令與元素命令相同（例如，字符 < 仍是 <）。除了 hide 外，該命令不能與任何其它命令一起使用。

xmltext

列內(nèi)容應(yīng)包在單個(gè)標(biāo)記內(nèi)，以便與文檔的其它部分集成。如果指定了AttributeName，則標(biāo)記名由指定名稱替換；否則通過將內(nèi)容放在容器的起始處而不進(jìn)行實(shí)體編碼，將特性追加到包含元素的當(dāng)前特性列表。含有該命令的列必須是文本類型（varchar、nvarchar、char、nchar、text、ntext）。該命令只能與 hide 一起使用。在提取存儲在列中的溢出數(shù)據(jù)時(shí)該命令很有用。 如果內(nèi)容不是有效的 XML，則該行為不明確。

cdata

通過用 CDATA 節(jié)環(huán)繞數(shù)據(jù)來包含數(shù)據(jù)。不對內(nèi)容進(jìn)行實(shí)體編碼。原始數(shù)據(jù)類型必須是文本類型（varchar、nvarchar、text、ntext）。該命令只能與 hide 一起使用。當(dāng)使用該命令時(shí)，不應(yīng)指定AttributeName。

2．簡單查詢

在指定數(shù)據(jù)所在的元素的名稱和標(biāo)記時(shí)必須使用ElementName參數(shù)和Tag參數(shù)，也就是查詢語句中表中存在的每一個(gè)實(shí)際的數(shù)據(jù)列都必須以ElementName!Tag開頭。在沒有指定AttributeName!Directive的情況下以元素值的形式返回。在只查詢一個(gè)表的情況下Tag為1，Parent為NULL。

使用EXPLICIT模式來查詢數(shù)據(jù)我認(rèn)為使用倒推的方式會比較方便一些，比如我要得到如下結(jié)構(gòu)，

Description

查詢語句：

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

返回結(jié)果：

Dried fruit and bean curd

Seaweed and fish

當(dāng)然下面的語句同上面的等效，

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模式下的多表連接查詢可能相對比較復(fù)雜些，先來看一個(gè)簡單的例子，

查詢語句：

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

返回結(jié)果：

Beverages

1

Chai

Beverages

2

Chang

Condiments

3

Aniseed Syrup

Condiments

4

Chef Anton&apos;s Cajun Seasoning

從上面的結(jié)構(gòu)可以看出數(shù)據(jù)的xml結(jié)構(gòu)不是很舒服，存在比較大的冗余。想象中改進(jìn)后的結(jié)構(gòu)如下：

3

Aniseed Syrup

4

Chef Anton&apos;s Cajun Seasoning

1

Chai

2

Chang

從上面看出Category元素的Tag為1，Parent為NULL，而Product元素的Tag為2，Parent為1（即Category的Tag值）。這里有一個(gè)問題需要解決，那就是在Tag和Parent中存在兩套值，這里就需要使用UNION ALL運(yùn)算符來實(shí)現(xiàn)這項(xiàng)功能，UNION ALL運(yùn)算符的一項(xiàng)功能就是消除查詢返回的重復(fù)的行。使用UNION ALL時(shí)需要注意的是結(jié)果集的列數(shù)必須相同。

查詢語句：

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

返回結(jié)果：

3

Aniseed Syrup

4

Chef Anton&apos;s Cajun Seasoning

1

Chai

2

Chang

不帶FOR XML EXPLICIT語句的結(jié)果為：

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模式查詢中的指令

這些指令是指通用表列的第四部分，通過它對數(shù)據(jù)進(jìn)行進(jìn)一步控制。

1> 最常用的element和xml指令

通過該指令把列的數(shù)據(jù)顯示為一個(gè)子元素而不是屬性。

查詢語句：

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

返回結(jié)果：

Dried fruit and bean curd

Seaweed and fish

查詢語句：

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

返回結(jié)果：

Dried fruit and bean curd

Seaweed and fish

查詢語句：

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

返回結(jié)果：

Produce

Dried fruit and bean curd

Seafood

Seaweed and fish

xml指令同element指令差不多，只是element指令會將一些xml下面的符號進(jìn)行編碼，如>被編碼成>5，而xml指令不會。

2> 使用hide指令

該指令是用來處理那些不想返回結(jié)果的列，許多人會有疑問覺得沒有必要，只要在列中不增加就行了，條件和排序也可以直接使用列。我們可以想得到使用UNION ALL時(shí)就用得上了，看下面的例子。

查詢語句：

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

返回結(jié)果：

3

Aniseed Syrup

4

Chef Anton&apos;s Cajun Seasoning

1

Chai

2

Chang

3> 使用xmltext指令

該指令是用來處理包含xml片斷內(nèi)容的數(shù)據(jù)列，沒找到現(xiàn)成的表結(jié)構(gòu)和數(shù)據(jù)進(jìn)行測試，所以就在Categories表中增加了XmlData列名，類型為nvarchar，默認(rèn)值設(shè)置為“”。

查詢語句：

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

返回結(jié)果：

查詢語句：

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

返回結(jié)果：

4> 使用cdata指令

使用該指令可以創(chuàng)建CDATA節(jié)防止一些字符數(shù)據(jù)被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

返回結(jié)果：

]]>

5> 使用ID、IDREF和IDREFS指令以及XMLDATA選項(xiàng)

由于這里只能返回XDR構(gòu)架，不支持XSD架構(gòu)的檢索，我就只給出一個(gè)例子，

查詢語句：

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

返回結(jié)果：

四．使用BINARY BASE64 選項(xiàng)

使用該選項(xiàng)可以檢索二進(jìn)制字段，比如圖像數(shù)據(jù)以base64的形式返回到xml文檔。

1．使用RAW方式

查詢語句：

SELECT CategoryID,

Picture

FROM Categories

WHERE CategoryID =1

ORDER BY CategoryID DESC

FOR XML RAW,BINARY BASE64

返回結(jié)果：

2．使用AUTO方式

查詢語句：

SELECT CategoryID,

Picture

FROM Categories

WHERE CategoryID =2

ORDER BY CategoryID DESC

FOR XML AUTO,BINARY BASE64

返回結(jié)果：

查詢語句：

SELECT CategoryID,

Picture

FROM Categories

WHERE CategoryID =1

ORDER BY CategoryID DESC

FOR XML AUTO,ELEMENTS,BINARY BASE64

返回結(jié)果：

1

FRwvAAIAAAANAA4AFAAhAP////9CaXRt...