淺談SQL Server 2005 XQuery新功能

作者:董建弘

1. 契子

  以往,使用關聯式資料庫系統儲存XML文件的方式可區分為兩種方式。

  第一種是在關聯式資料庫系統中,將XML文件以文字(text)或二進位(binary)資料的格式存入表格的一個大型欄位(large objectLOB),在存取XML文件時只須對此欄位進行資料的輸入、輸出即可。此方式雖然具有設計簡單,不需複雜的表格拆解動作即可處理結構複雜的XML文件等優點,十分適合應用於以整份XML文件作為輸入、輸出的應用,並可直接套用在既有的關聯式資料庫系統之上。但此方式無法在資料庫中直接存取XML中的特定元素(Element)或屬性(Attribute)資料,此時必需輸出整份XML文件,再進行資料剖析(parsing),才能處理XML文件所需要的資料。

  第二種方式是透過事先定義好的對應規則,將XML文件拆解成多個部份存放在不同的表格(Table)。當要取回原本的XML文件時,則透過SQL查詢語言取得各表格的資料,再組合成XML文件。此方式的優點是可快速的處理結構固定的XML文件,並可直接套用在關聯式資料庫系統之上。但當XML文件結構很複雜時,此方法將產生大量的表格。若要將分散在各表格中的資料組合成完整的XML文件,必需透過複雜的資料結合(join)才能達成。此外,一旦對應規則訂定後,若因資料需求的改變而造成XML文件結構需要更動時,則可能需重新定義對應規則。因此,在處理不同結構的XML文件時較無彈性。

  在SQL Server 2005的版本中,新增加了XML的資料型態來儲存XML文件,並可使用XQuery快速的存取XML文件中的資料,等於結合的上述兩種方式的優點,以下筆者簡單介紹XQuerySQL Server 2005XML的支援。

2. XQuery

  為了制定XML的查詢方式,因此W3C 2000 年的1 月首先提出了XML Query Requiremets,討論對XML 資料做查詢時的需求。於20016W3CJonathan Robie (Software AG) Don Chamberlin (IBM Almaden Research Center) Daniela Florescu (INRIA) 三人共同開發的Quilt語言為基礎定義XQuery,此後XQuery便XML查詢語言的標準,目前最新版本已於2006 6 8 日出版,發展的過程概略如圖2-1所示。

 

2-1 XMLXQuery的發展

 

  XQuery 是針對各類XML 資料所設計,不論其儲存為文件或是在關聯式資料庫中。XQuery 其資料模型 ( data model ) 視一個XML 文件為一棵標記樹( label tree ) ,並考慮標記樹中每個節點及所有值之順序關係。其特性是路徑表示法的使用,使用者可以利用路徑表示法在XML 資料中尋找任意長度路徑的資料。因此透過XQuery 的執行,便可取得XML 文件中符合路徑表示法的資料序列,其順序是依照文件中各個元素之間的順序關係而定。另外使用者也可以從資料中取得關於XML 文件schema 的資訊。

2.1 XQuery Expressions

  一個XQuery 的敘述式為一組FLWOR (發音同 “flower”) 表示法,是由ForLetWhereOrder byReturn子句等結構而成,並以特定的順序組合而成如圖2-2所示。

 

2-2 FLWOR表示法之資料結構順序

 

  FOR 讓變數可以遞迴取得 ( iterate over ) 一個路徑表示法的結果, 而LET 則是將變數直接與某一個的路徑表示法結合( binding ) WHERE 則允許對變數做條件的限制,ORDER BY可依據條件重新排序FOR語法的元素進而影響接下來RETURN的輸出,RETURN 則可以根據原有的、或是建構新的XML元素為查詢的輸出。SQL Server 2005目前僅支援FORWHEREORDER BYRETURN四個語法。 

3. Using XQuery in SQL Server 2005

3.1 xml Data Type

  在Microsoft R SQL ServerR 2005以前的版本雖然可以透過「FOR XML」以及「OPEN XML」來處理XML型態的資料,但其主要的工作環境仍舊拘泥在關聯式的架構,無法直接存取XML型態裡面的元素以及屬性的資料。2005這個版本中新增XML資料型態與相關的方法以及函數,大幅提高其對XML的支援。

  「xml Datatype」是一個原生的資料類型可以用來定義欄位、變數與參數的資料型態,它儲存的XML資料必須符合Well-formed的規定,所以在儲存資料的同時它會自動檢查該資料是否符合Well-formed格式。在存取方面可以使用XQueryXML資料的內容進行查詢,也可以使用XML-DML修改XML資料格式與內容。在效能方面可以對xml資料類型建立索引機制 (Indexing) ,以提高查詢效率。

 

建立xml資料型態的欄位

CREATE TABLE T1(Col1 int primary key, Col2 xml)

建立一的xml資料型態的變數

declare @x xml 

 

  在SQL Server 2005XMLBLOB型態存在,最大可到2GB,而儲存的編碼格式是UTF-16,對於XML中文或多語系亂碼的問題也降低發生的可能,另外還可以針對XML資料欄位建立索引,提高XML資料的查詢效率。 

3.2 方法(Method)

  SQL Server® 2005針對xml資料類型提供有query() value() exist() modify() 以及nodes() 等五個方法(Methods) 進行資料的存取與維護:

3.2.1 query():針對xml資料類型的執行個體指定 XQuery。結果為 xml 類型。該方法會傳回不具類型的XML執行個體。

3.2.2 value():對XML執行XQuery並傳回SQL類型的值,此方法會傳回純量值而非集合。可以使用此方法,從儲存在XML Datatype的欄位資料、參數或變數中的XML資料擷取值,可以指定「SELECT」查詢來結合或比較 XML 資料與非XML資料行的資料。

3.2.3 exist():用來確認一個查詢陳述式是否有結果。傳回值為10或是NULL1 代表True,即查詢中的 XQuery 運算式傳回非空的結果,也就是,它至少會傳回一個 XML 節點;0 代表False,即查詢傳回空的結果;如果查詢所執行的xml資料類型執行個體包含 NULL,則會傳回 NULL

3.2.4 modify():此方法使用XML-DML陳述式來來修改 XML類型變數或欄位的內容,如插入、更新或刪除XML資料的節點。modify() 這個方法只能用在「UPDATE」陳述式的「SET」子句中。

3.2.5 nodes():可以將XML資料切割成多個資料列,以便將部份 XML 文件傳播成資料列集,就如同關聯式資料。每個xml資料類型執行個體都有隱含提供的內容節點。針對儲存在資料行或變數中的 XML 執行個體,這是指文件節點。文件節點是位在每個xml資料類型執行個體最上方的隱含節點。 nodes() 方法的結果,會是一個包含原始 XML 執行個體之邏輯副本的資料列集。在這些邏輯副本中,每個資料列執行個體的內容節點,都會設成可用查詢運算式來識別的節點之一,讓後續的查詢能夠比對這些內容節點來進行導覽。

3.3 函數(Functions

  SQL Server 2005針對XQuery所提供的函數依其用途可分為個12種類,以下分別介紹這些函數與其用法。

3.3.1 數值函數 (Functions on Numeric Values) :可以對數值資料進行無條件進位、無條件捨去以及四捨五入運算,其函數說明如表3-1所示。

3-1 SQL Server 2005 XQuery數值函數

函數名稱

說明

ceiling ($arg as numeric)

傳回不含小數的最小數字,不小於其引數的值。如果引數是空的序列,它會傳回空的序列。

floor ($arg as numeric)

傳回不含小數、不大於其引數值的最大數字。如果引數是空的序列,它會傳回空的序列。

($arg as numeric)

傳回最接近引數且去掉小數部份的數字。如果這樣的數字不止一個,則傳回最接近正無限數的那一個。

其用法如下:

Example:

declare @x xml

set @x='

<root>

           <item price="13.5">a</item>

           <item price="15.4">b</item>

</root>'

select @x.query('for $i in //item

return

<item price="{$i/@price}">

<name>{data($i) }</name>

<ceiling>{ceiling($i/@price) }</ceiling>

<floor>{floor($i/@price) }</floor>

<round>{round($i/@price) }</round>

</item>

')

Result:

<item price="13.5">

  <name>a</name>

  <ceiling>14</ceiling>

  <floor>13</floor>

  <round>14</round>

</item>

<item price="15.4">

  <name>b</name>

  <ceiling>16</ceiling>

  <floor>15</floor>

  <round>15</round>

</item>

 

3.3.2 字串值相關函數 (Functions on String Values) :對於字串資料可以進行字串連接、字串比較、取得部份字串以及取得字串長度等運算,其函數說明如表3-2所示。

3-2 SQL Server 2005 XQuery字串值相關函數

函數名稱

說明

concat ($string as xs:string, $string as xs:string [, ...])

接受零或更多的字串以做為引數,並傳回串連每個引數的值所建立的字串。

contains ($arg1 as xs:string, $arg as xs:string)

傳回 xs:boolean 類型的值,指出 $arg1 的值是否包含 $arg2 指定的字串值。

Substring($sourceString) as xs:string,$startingLoc as xs:decimal [,$length as xs:decimal])

傳回 $sourceString 值的一部份,從 $startingLoc, 值所指示的位置開始,一直到 $length 值所指示的字元數為止。

string-length($arg as xs:string)

傳回字元的字串長度。

其用法如下:

Example:

declare @x xml

set @x='

<root>

           <item price="13.5">MP5</item>

           <item price="15.4">G36C</item>

</root>'

select @x as x into #t

select x.query('for $i in //item

return

<item price="{$i/@price}">

<name>{data($i) }</name>

<concat>{concat("price:", $i/@price,

"; ceiling:",

string(ceiling($i/@price))) }</concat>

<substring>{substring(data($i) ,2,2) }</substring>

<string-length>{string-length(data($i)) }</string-length>

</item>

')

from #t

where x.value('contains((//item/text()) [1],"MP5") ','bit') =1

Result:

<item price="13.5">

  <name>MP5</name>

  <concat>price:13.5; ceiling:14</concat>

  <substring>P5</substring>

  <string-length>3</string-length>

</item>

<item price="15.4">

  <name>G36C</name>

  <concat>price:15.4; ceiling:16</concat>

  <substring>36</substring>

  <string-length>4</string-length>

</item>

 

3.3.3 布林值相關函數 (Functions on Boolean Values) :用來檢查陳述式的結果是否不成立,其函數說明如表3-3所示。

3-3 SQL Server 2005 XQuery布林值相關函數

函數名稱

說明

not($arg as item() *)

如果 $arg 的有效布林值為 false,就會傳回 TRUE,而如果 $arg 的有效布林值為 true,就會傳回 FALSE

其用法如下:

Example:

declare @x xml

set @x='

<root>

           <item price="13.5">MP5</item>

           <item price="15.4">G36C</item>

</root>'

select @x.query('for $i in //item[not(@price="13.5") ]

return $i')

Result:

<item price="15.4">G36C</item>

 

3.3.4 節點相關函數 (Functions on Nodes) :此類函數可以取得XQuery陳述式所指出的節點的資料值、名稱或是URI等資料,其函數說明如表3-4所示。

3-4 SQL Server 2005 XQuery節點相關函數

函數名稱

說明

number

傳回 $arg 所指出的節點數值。

local-name 函數 (XQuery)

xs:string 傳回 $arg 名稱的本機部份,它有可能是零長度的字串或是將會有 xs:NCName 的語彙格式。如果沒有提供引數,預設值是內容節點。

namespace-uri 函數 (XQuery)

傳回在 $arg 中指定為 xs:string QName 命名空間 URI

 

3.3.5 內容函數 (Context Functions) :此類函數在XQuery陳述式所指出的節點中依指定的位置取得資料,其函數說明如表3-5所示。

3-5 SQL Server 2005 XQuery內容函數

函數名稱

說明

last()

傳回目前所處理序列中的項目號碼。具體而言,它會傳回序列中最後一個項目的整數索引。序列中第一個項目的索引值為 1

position()

傳回整數值,以指出目前所處理的項目序列中內容項目的位置。

其用法如下:

Example:

declare @x xml

set @x='

<gun bore="9mm"><item>M92F</item>

<item>G-19</item>

<item>MP5</item>

</gun>

<gun bore="5.56"><item>M16</item>

<item>G36C</item>

<item>M4A1</item>

<item>M249</item>

</gun>'

select @x.query('for $i in /gun[last() ]

return

$i/*[position() <=2]')

Result:

<item>M16</item>

<item>G36C</item>

 

3.3.6 序列的相關函數 (Functions on Sequences) :此類函數在處理XQuery陳述式所指出的節點的資料序列,可做空序列檢查、排除重覆資料等運算,其函數說明如表3-6所示。

3-6 SQL Server 2005 XQuery序列的相關函數

函數名稱

說明

empty($arg as item() *)

如果 $arg 的值是空序列,則傳回 True。否則,此函數會傳回 False

distinct-values($arg as xdt:anyAtomicType*)

移除 $arg 所指定的時序中的重複值。如果 $arg 是空白時序,則函數會傳回空白時序。

id($arg as xs:IDREF*)

傳回含有 xs:ID 值的元素節點序列,這些值符合 $arg 所提供的一或多個 xs:IDREF 值。

其用法如下:

Example:

declare @x xml

set @x='

<gun><item>M16</item>

<item>G36C</item>

<item>G36C</item>

<item>M4A1</item>

<item>M4A1</item>

<item>M249</item>

</gun>

<gun bore="7.62"><item>T74</item>

<item>AK47</item>

</gun>'

select @x.query('

for $i in distinct-values(/gun[empty(@bore) ]/item)

return <item>{$i}</item>')

Result:

<item>M16</item>

<item>G36C</item>

<item>M4A1</item>

<item>M249</item>

 

3.3.7 彙總函數 (Aggregate Functions) :針對XQuery中指定的節點的資料,求取其總數,對於數值型資料可以計算其平均、最大值、最小值以及加總等彙總運算,其函數說明如表3-7所示。

3-7 SQL Server 2005XQuery彙總函數

函數名稱

說明

count($arg as item() *)

傳回 $arg 指定時序中所包含的項目號碼。

avg($arg as xdt:anyAtomicType*)

傳回數字序列的平均值。

min($arg as xdt:anyAtomicType*)

從不可部份完成值 $arg 的序列傳回,一個值的項目少於所有其他的值。

max($arg as xdt:anyAtomicType*)

從不可部份完成值的序列 $arg 中,傳回其值大於所有其他項目之值的項目。

sum($arg as xdt:anyAtomicType*)

傳回數字序列的總和。

其用法如下:

Example:

declare @x xml

set @x = '<root>

<item value="1">a</item>

<item value="3">b</item>

<item value="2">c</item>

<item value="5">d</item>

<item value="4">e</item>

</root>'

select @x.query('

for $i in /root

return

<s>count: {count($i/item) };

avg:{avg($i/item/@value[1]) };

min:{min($i/item/@value[1]) };

max:{max($i/item/@value[1]) };

sum:{sum($i/item/@value[1]) }</s>

')

Result:

<s>count: 5;

avg:3;

min:1;

max:5;

sum:15</s>

 

3.3.8 建構函式函數 (Constructor Functions) :可以讓使用者自行訂義XSD類型,其函數說明如表3-8所示。

3-8 SQL Server 2005 XQuery建構函式函數

函數名稱

說明

TYP($atomicvalue as xdt:anyAtomicType)

建構函式函數可由指定輸入,建立任何 XSD 內建或使用者自訂的不可部份完成類型。

 

3.3.9 Data Accessor函數 (Data Accessor Functions) :取得XQuery所指出的節點資料,其函數說明如表3-9所示。

3-9 SQL Server 2005 XQuery Data Accessor函數

函數名稱

說明

string($arg as item())

傳回以字串表示的 $arg 值。

data ($arg as item() *)

傳回 $arg 指定的每一個項目的具類型值。

使用方式如下所示:

Example:

declare @x xml

set @x = '<?xml version="1.0" encoding="UTF-8" ?>

<!--  This is a comment -->

<root>

  <a>10</a>

just text

  <b attr="x">20</b>

</root>

'

select @x.query('string(/) ')

select @x.query('data(/) ')

Result:

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

This is a comment 10

just text

  20

(1 row(s) affected)

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

10

just text

  20

(1 row(s) affected)

 

3.3.10 布林建構函式 (Boolean Constructor Functions) :產生布林值,其函數說明如表3-10所示。

3-10 SQL Server 2005 XQuery布林建構函式

函數名稱

說明

true()

傳回 xs:boolean True。這相當於 xs:boolean("1")

False()

傳回 xs:boolean False。這相當於 xs:boolean("0")

使用方式如下所示:

Example:

DECLARE @x XML

SET @x= '<ROOT><elem attr="aaa">bbb</elem></ROOT>'

select @x.value('

if ((/ROOT/elem/@attr) [1] eq "aaa") then

fn:true()

else fn:false() ','bit')

Result:

1

 

3.3.11 SQL Server XQuery 擴充函數:使用 sql:column() sql:variable() XQuery 擴充程式函數,在 XML 內繫結非 XML 關聯式資料。column() 用以指定針對儲存在 xml 類型的變數或資料行中之XML執行個體的查詢,當查詢使用另一個非 XML資料行的值,以同時查詢關聯式資料與XML資料。variable() 可以在XQuery陳述式中用使用 Transact-SQL 變數或參數中的值,以將關聯式資料及 XML 資料合併在一起。其函數說明如表3-11所示。

3-11 SQL Server 2005 XQuery擴充函數

函數名稱

說明

column("columnName")

讀取同一筆資料中其他非xml資料類型的欄位資料。

variable("variableName")

XML 資料執行個體中公開含有 SQL 關聯式值的變數。

使用方式如下所示:

Example:

declare @x xml

declare @i varchar(10)

set @i='b'

set @x = '<root>

<item value="1">a</item>

<item value="3">b</item>

<item value="2">c</item>

<item value="3">d</item>

<item value="4">e</item>

</root>'

select '4' as v, @x as x into #t

select @x.query('

for $i in /root/item

where data($i) =sql:variable("@i")

or $i/@value=sql:column("v")

return

$i')

from #t

Result:

<item value="3">b</item>

<item value="4">e</item>

 4. 後記

  很可惜的,SQL Server 2005不支援XQueryLet語法,也無法像在使用Table Join一樣可以直接整合兩個XML文件的資料,雖然可以使用sql:columnsql:variable來引用其他欄位與變數,卻也有種種限制。但種而言之,可以將XML訂為資料型態並引用XQuery來查詢,在關聯式資料庫系統對於XML的支援,已經可以說是跨出一大步了,讓使用者不需要在定義複雜的表格或繕寫剖析程式、甚至另外建置原生型XML資料庫系統,便可以在原有的關聯式資料庫系統架構中整合XML的應用。資訊技術的演進是如此快速,不久的將來各家關聯式資料庫系統會對XML有更完善的整合與支援。 

參考文獻

[1] Don Chamberlin, Jonathan Robie, and Daniela Florescu, "Quilt: An XML Query Language for Heterogeneous Data Sources,” Proceedings of WebDB 2000 Conference, in Lecture Notes in Computer Science, Springer-Verlag, 2000.

[2] SQL Server 2005 Books Online

[3] “Standard Generalized Markup Language (SGML) ,” ISO 8879:1986.

[4] Bourret, XML and Databases, http://www.rpbourret.com/XML/XMLAndDatabases.htm, 2006.

[5] World Wide Web Consortium, Extensible Markup Language (XML) , http://www.w3.org/XML/.

[6] World Wide Web Consortium, XML Query (XQuery) , http://www.w3.org/XML/Query/.

[7] World Wide Web Consortium, XQuery 1.0: An XML Query Language, http://www.w3.org/TR/xquery/.

[8] X-Hive/DB. http://www.x-hive.com.

[9] XML 台北資訊網 http://www.xml.org.tw/Default.asp