在SQL Server中如何與XML互動

在SQL Server中如何與XML互動

XML可以說是資料庫表現的一種靈活的形式，在資料庫開發過程中，很多時候我們都
會需要涉及到XML，特別是用在資料交換。而在SQL Server
2005中，XML已成為一種流行的資料類型，XML已經做為一種新型的類型儲存在資料庫中。藉助於基於XML模式的強型別化支援和基於伺服器端的XML
資料校正功能，開發人員就可以對儲存的XML文檔進行輕鬆地遠程修改。

   在SQL Server 2000中就已經包括了一些XML特徵。最常用的是使用FOR XML語句以XML形式返回結果。SQL
Server 2005的功能則明顯不同。在SQL Server
2005中，XML是一種真正的資料類型;這意味著，可以使用XML作為表和視圖中的列，XML可以用於T-SQL語句中或作為預存程序的參數。可以直接
在資料庫中儲存、查詢和管理XML檔案。

在SQL Server中，主要有以下幾種操作XML的方式：

1) FOR XML語句的使用

   FOR XML語句在使用的時候有三種模式，參數分別為：RAW、AUTO和EXPLICIT。

   先來分別看這幾種模式：

RAW模式：

   RAW 模式將查詢結果集中的每一行轉換為帶有通用標識符 或可能提供元素名稱的XML元素。預設情況下，行集中非 NULL
的每列值都將映射為 元素的一個屬性。如果將 ELEMENTS 指令添加到 FOR XML 子句，則每個列值都將映射到 元素的子項目。

   先來看RAW的實現的方式：

   建立一個表：

CREATE TABLE [dbo].[Tb_User](
[ID] [int] IDENTITY(1,1) NOT NULL,
[user_name] [varchar](20) NULL,
[user_Pwd] [varchar](20) NULL,
CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

   語句一：　

SELECT ID, User_Name, User_pwd FROM Tb_User FOR XML RAW

   輸出的結果如下：

<row ID="1" User_Name="admin" User_pwd="admin888" />
<row ID="2" User_Name="user" User_pwd="user" />

   語句二：　

SELECT ID, User_Name, User_pwd FROM Tb_User FOR XML RAW, ELEMENTS

輸出的結果如下：

<row>
<ID>1</ID>
<User_Name>admin</User_Name>
<User_pwd>admin888</User_pwd>
</row>
<row>
<ID>2</ID>
<User_Name>user</User_Name>
<User_pwd>user</User_pwd>
</row>

AUTO模式：

   AUTO 模式可確定基於查詢返回的 XML 的形狀。在確定嵌套元素的方式時，AUTO 模式試探法將比較相鄰行中的列值。在FROM
子句內，每個在 SELECT 子句中至少有一列被列出的表都表示為一個 XML 元素。如果在 FOR XML 子句中指定了可選的 ELEMENTS
選項，SELECT 子句中列出的列將映射到屬性或子項目。

   同樣來看看兩種實現的方式：

   語句一：

SELECT ID, User_Name, User_pwd FROM Tb_User FOR XML AUTO

   輸出的結果如下：

<Tb_User ID="1" User_Name="admin" User_pwd="admin888" />
<Tb_User ID="2" User_Name="user" User_pwd="user" />

   語句二：

SELECT ID, User_Name, User_pwd FROM Tb_User FOR XML AUTO,ELEMENTS

   輸出的結果如下：　

<Tb_User>
<ID>1</ID>
<User_Name>admin</User_Name>
<User_pwd>admin888</User_pwd>
</Tb_User>
<Tb_User>
<ID>2</ID>
<User_Name>user</User_Name>
<User_pwd>user</User_pwd>
</Tb_User>

EXPLICIT模式：

   在 EXPLICIT 模式中，查詢書寫器控制由執行查詢所返回的 XML 文檔的形式。必須以特定的方式編寫查詢，將有關預期嵌套的附加資訊顯式指定為查詢的一部分。當指定 EXPLICIT 模式時，必須負責確保產生的 XML 符合文法規則並且有效。

   EXPLICIT 模式會將由查詢執行產生的行集轉換為 XML 文檔。為使 EXPLICIT 模式產生 XML 文檔，行集必須具有特定的格式。這需要編寫 SELECT 查詢以產生具有特定格式的行集(通用表)，以便處理邏輯隨後可以產生所需的 XML。

首先，查詢必鬚生成下列兩個中繼資料列：

   第一列必須提供當前元素的標記號(整數類型)，並且列名必須是 Tag。查詢必須為從行集構造的每個元素提供唯一標記號。

   第二列必須提供父元素的標記號，並且此列的列名必須是 Parent。這樣，Tag 和 Parent 列將提供階層資訊。

   前兩列是 Tag 和 Parent，它們是中繼資料列。這些值確定階層。查詢必須以特定的方式提供列名，Parent 列中的 0 或 NULL 表明相應的元素沒有父級。

   在構造 XML 的過程中，處理邏輯為每行選擇一組列，然後構造一個元素。

   現在來看看下面的兩個語句：

   語句一：

SELECT 1 as Tag,
NULL as Parent,
User_Name as [TbUser!1!UserName],
User_pwd as [TbUser!2!UserPwd]
FROM Tb_User
FOR XML EXPLICIT

   輸出的結果如下：

<TbUser UserName="admin" />
<TbUser UserName="user" />

   語句二：

SELECT 2 as Tag,
NULL as Parent,
User_Name as [TbUser!1!UserName],
User_pwd as [TbUser!2!UserPwd]
FROM Tb_User
FOR XML EXPLICIT

   輸出的結果如下：

<TbUser UserPwd="admin888" />
<TbUser UserPwd="user" />

   在此樣本中，列TbUser!1!UserName和TbUser!2!UserPwd 形成一組，然後該組用於構造元素。對於第一行中的 Tag 列值 1和對於 Tag 列值為 2 的行，根據Tag的選擇的不同，產生對應表的記錄就發生了變化。

2) 使用OPENXML

   不同於以上，OPENXML主要通過讀取XML然後可以轉換為資料庫的資料。

OPENXML的文法如下：

OPENXML( idoc int, rowpattern nvarchar , [ flags byte ] )
[ WITH ( SchemaDeclaration | TableName ) ]

   Idoc：XML 文檔的內部表式形式的文檔控制代碼。

   Rowpattern：XPath 模式，用來標識要作為行處理的節點。

   Flags：指示應在 XML 資料和關係行集間使用映射以及應如何填充溢出列。

   0 預設為“以屬性為中心”的映射。

   1 使用“以屬性為中心”的映射。

   2 使用“以元素為中心”的映射。

   8 可與XML_ATTRIBUTES 或 XML_ELEMENTS 組合使用(邏輯或)。

   WITH後面就是對應的欄位名及欄位對應的屬性。

   我們先來看一個微軟關於這個調用的執行個體：　

DECLARE @DocHandle int
DECLARE @XmlDocument nvarchar(1000)
SET @XmlDocument = N'<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
<Order OrderID="10248" CustomerID="VINET" EmployeeID="5"
OrderDate="1996-07-04T00:00:00">
<OrderDetail ProductID="11" Quantity="12"/>
<OrderDetail ProductID="42" Quantity="10"/>
</Order>
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
<Order OrderID="10283" CustomerID="LILAS" EmployeeID="3"
OrderDate="1996-08-16T00:00:00">
<OrderDetail ProductID="72" Quantity="3"/>
</Order>
</Customer>
</ROOT>'
-- Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @DocHandle OUTPUT, @XmlDocument
-- Execute a SELECT statement using OPENXML rowset provider.
SELECT *
FROM OPENXML (@DocHandle, '/ROOT/Customer',1)
WITH (CustomerID varchar(10),
ContactName varchar(20))
EXEC sp_xml_removedocument @DocHandle

   運行這個執行個體，結果如下：　

CustomerID ContactName
---------- --------------------
VINET Paul Henriot
LILAS Carlos Gonzlez

現在來分析以上這個例子：

  
在定義好了@XmlDocument的XML文檔後，然後調用系統程式sp_xml_preparedocument，它表示在XML文檔存在記憶體的時
候，允許XML文檔作為調用參數。然後就可以調用OPENXML函數。通過指定為 /ROOT/Customer 的標識要處理的
節點，讀取CustomerID和ContactName的值，然後就調用系統程式sp_xml_removedocument刪除SQL Server
伺服器記憶體中的XML資料。

   但在調用OPENXML函數時要注意記憶體的使用方式。系統程式sp_xml_preparedocument 的傳回值是指向記憶體中XML文檔的控制代碼。所以不要使用OPENXML載入較大的XML文檔，否則可能會造成伺服器的記憶體溢出。

3) 針對 xml 資料類型的 XQuery

   XQuery 是一種可以查詢結構化或半結構化 XML 資料的語言。由於 SQL Server 2005 資料庫引擎中提供 xml 資料類型支援，因此可以將文檔儲存在資料庫中，然後使用 XQuery 進行查詢。

   在此我們只舉一個簡單的例子：

DECLARE @x xml
SET @x = '<Myroot><Element1>First</Element1><Element2>Second</Element2>
</Myroot>'
SELECT @x.query('/Myroot')

   輸出的結果如下：

<Myroot>
<Element1>First</Element1>
<Element2>Second</Element2>
</Myroot>

DECLARE @x xml
SET @x = '<Myroot><Element1>First</Element1><Element2>Second</Element2>
</Myroot>'
SELECT @x.query('/Myroot/Element1')

   輸出的結果如下：

<Element1>First</Element1>

   可以看到，它通過讀取XML文檔的節點，而輸出對應的值。

   XQuery功能非常強大，所涉及到的內容也非常廣泛，感興趣的讀者可以查看SQL Server 2005 Books Online中的XQuery詳細介紹。

   儘管在SQL Server 2005中XML資料類型就象許多其它資料類型一樣對待，它還是存在以下的一些限制：

   · XML類型不能轉換成text或ntext資料類型。

   · 除了string類型，沒有其它資料類型能夠轉換成XML。

   · XML列不能應用於GROUP BY語句中。

   · 分布式局部(partitioned)視圖不能包含XML資料類型。

   · sql_variant執行個體的使用不能把XML作為一種子類型。

   · XML列不能成為主鍵或外鍵的一部分。

   · XML列不能指定為唯一的。

   · COLLATE子句不能被使用在XML列上。

   · XML列不能加入到規則中。

   · 唯一可應用於XML列的內建純量涵式是ISNULL和COALESCE。沒有任何其它內建純量涵式支援使用XML類型。

   · 表中最多隻能擁有32個XML列。

   · 具有XML列的表不能有一個超過15列的主鍵。

   · 具有XML列的表不能有一個timestamp資料類型作為它們的主鍵的一部分。

   · 儲存在資料庫中的XML僅支援128級的層次。