模板
教程
環境
性能
功能
管理
軟件測試 > 測試開發技術 > 軟件測試開發語言 > 數據庫 > SQL Server >
SQL Server 2005 XML最佳實施策略用法
加載 XML 數據 將 XML 數據從 SQL Server 2000 傳輸到 SQL Server 2005 可以用多種方式將 XML 數據傳輸到 SQL Server 2005。在下一節中，我們將討論幾種方案。 如果您將數據存儲在 SQL Server 2000 數據庫的 [n]text 或圖像列中，可以使用 DTS 等將表導入到 S
加載 XML 數據 將 XML 數據從 SQL Server 2000 傳輸到 SQL Server 2005
可以用多種方式將 XML 數據傳輸到 SQL Server 2005。在下一節中，我們將討論幾種方案。
• 如果您將數據存儲在 SQL Server 2000 數據庫的 [n]text 或圖像列中，可以使用 DTS 等將表導入到 SQL Server 2005 數據庫 中。使用 ALTER TABLE 語句將列類型更改為 XML。
• 可以使用 bcp out 批量復制 SQL Server 2000 中的數據，使用 bcp in 將數據批量插入到 SQL Server 2005 數據庫中。
• 如果您將數據存儲在 SQL Server 2000 數據庫的關系列中，請創建一個帶有一個 ntext 列的新表，同時根據需要在該表中創建一個主鍵列以用作行標識符。使用客戶端編程檢索在服務器中通過 FOR XML 生成的 XML，并且將其寫入 ntext 列。然后，使用上述技巧將數據傳輸到 SQL Server 2005 數據庫。您可以選擇將 XML 直接寫入 SQL Server 2005 數據庫的 XML 列中。
示例：將列類型更改為 XML
假設您需要將表 R 中的 [n]text 或圖像列 XYZ 的類型更改為非類型化 XML。下面的語句可執行此類更改：
ALTER TABLE R ALTER COLUMN XYZ XML
• 如果需要，可以通過指定一個 XML 架構集合將目標類型化為 XML。
批量加載 XML 數據
可以使用 SQL Server 中的批量加載功能（如 bcp），將 XML 數據批量加載到服務器中。通過 OPENROWSET 可以將文件中的數據加載到 XML 列中。下面的示例闡明了這一點。
示例：從文件中加載 XML
該示例說明了如何在表 T 中插入行。XML 列的值作為 CLOB 從文件 C:\yukon\xmlfile.xml 中加載，并且整數列被提供了值 10。
INSERT INTO T SELECT 10, xCol FROM (SELECT * FROM OPENROWSET (BULK 'C:\Yukon\xmlfile.xml', SINGLE_CLOB) AS xCol) AS R(xCol)
SQL Server 2005 用 Unicode (UTF-16) 存儲 XML 數據。從服務器中檢索的 XML 數據采用 UTF-16 編碼；如果您需要不同的編碼，則需要對檢索到的數據執行必要的轉換。有時，您可能擁有采用不同編碼的 XML 數據，因此在數據加載過程中需要非常小心：
• 如果文本 XML 采用 Unicode (UCS-2, UTF-16)，則將其賦給 XML 列、變量或參數不會帶來任何問題。
• 如果編碼不是 Unicode 并且是隱式的（由于源代碼頁），則數據庫中的字符串代碼頁應該與要加載的代碼點相同或兼容（必要時使用 COLLATE）。如果不存在這樣的服務器代碼頁，則您必須添加帶有正確編碼的顯式 XML 聲明。
• 要使用顯式編碼，請使用 varbinary() 類型（它不與代碼頁交互）或者使用適當代碼頁的字符串類型。然后，將該數據賦給 XML 列、變量或參數。
示例：顯式指定編碼
假設您具有的 XML 文檔 (vcdoc) 被存儲為沒有顯式 XML 聲明的 varchar(max)。下面的語句將添加一個帶有編碼"iso8859-1"的 XML 聲明，將 XML 文檔串連起來，將結果轉換為 varbinary(max) 以便保留字節表示形式，最后將其轉換為 XML。這使 XML 處理器能夠按照指定的編碼"iso8859-1"來分析數據，并為字符串值生成相應的 UTF-16 表示形式。
SELECT CAST( CAST (('<?xml version="1.0" encoding="iso8859-1"?>'+ vcdoc) AS VARBINARY (MAX)) AS XML)
嵌入到 T-SQL 中的 XQuery (http://www.w3.org/TR/xquery/) 語言支持查詢 XML 數據類型。該語言正在由 WWW 聯合會 (W3C) 進行開發（在本文作者最后一次召集起來撰寫本文時），并且所有主要數據庫供應商（包括 Microsoft）都參與了開發工作。它包括了 XPath 2.0 作為導航語言。同時，還提供了針對 XML 數據類型的數據修改語言構造。有關 SQL Server 2005 中支持的 Xquery 構造、函數和運算符的信息，請參閱聯機圖書。
錯誤模型
具有語法錯誤的 Xquery 表達式和 XML DML 語句會返回編譯錯誤。編譯階段會檢查 XQuery 表達式和 DML 語句的靜態類型正確性，并且對于類型化 XML 使用 XML 架構進行類型推理。如果某個表達式可能在運行時由于類型安全沖突而失敗，它會引發靜態類型錯誤。靜態錯誤的例子有將字符串添加到整數以及在不存在的節點中查詢類型化數據。
與 W3C 標準有所不同的是，XQuery 運行時錯誤被轉換為可以作為空 XML 或 NULL 傳播給查詢結果的空序列（具體取決于調用上下文）。
通過顯式轉換到正確的類型，用戶可以避免靜態錯誤，盡管運行時轉換錯誤將被轉化為空序列。
下面的小節將詳細討論類型檢查。
唯一性檢查
如果編譯器無法確定能否在運行時保證唯一性，則要求唯一性的定位步驟、函數參數和運算符（例如 eq）將返回錯誤。問題經常出現在非類型化數據上。例如，屬性查找要求存在唯一的父元素；能夠選擇單個父節點的序號即可滿足需要。計算 node()-value() 組合（請參閱 Value()、Nodes() 和 OpenXML()）以提取屬性值，這可能不需要指定序號，如下面的示例所示。
示例：已知的唯一性
在該示例中，nodes() 方法為每個 元素生成一個單獨的行。（有關 nodes() 方法的詳細說明，請參閱 Value()、Nodes() 和 OpenXML()）。在 節點上進行求值的 value() 方法會提取 @genre（它作為屬性具有唯一性）的值。
SELECT nref.value('@genre', 'varchar(max)') LastName FROM T CROSS APPLY xCol.nodes('//book') AS R(nref)
對于類型檢查，需要記住 //first-name[1] 和 (//first-name)[1] 之間的差別。前者返回 節點的序列，其中每個節點是其同輩節點中最左邊的 節點。后者返回 XML 實例中按照文檔順序的第一個唯一的 節點。
示例：value() 的用法
下面這個對非類型化 XML 列執行的查詢會導致靜態、編譯錯誤，因為 value() 需要將一個唯一性節點作為第一個參數，但編譯器無法確定在運行時是否將只出現一個 節點：
SELECT xCol.value('//author/last-name', 'nvarchar(50)') LastName FROM T
SELECT xCol.value('//author/last-name[1]', 'nvarchar(50)') LastName FROM T
但是，這不會糾正該錯誤，因為在每個 XML 實例中都可能出現多個 節點。下面的改寫方式將會有效：
父軸
如果節點的類型無法確定，則它將成為 anyType，后者不會隱式轉換為任何其他類型。在使用父軸（例如，xCol.query('/book/@genre/../price')）進行導航的過程中，尤其會發生這種情況；該父節點類型被確定為 anyType。元素也可能被定義為 XML 架構中的 anyType。在這兩種情況下，丟失更為精確的類型信息通常會導致靜態類型錯誤，并且要求將原子值顯式轉換為它們的特定類型。
Data()、Text() 和 String() 訪問器
XQuery 具有一個可從節點中提取標量的、類型化值的函數 fn:data()，一個可返回文本節點的節點測試 text()，以及可返回節點的字符串值的函數 fn:string()。它們的用法有時會引起混亂。下面是有關在 SQL Server 2005 中正確使用它們的準則。請考慮 XML 實例 12：
• 非類型化 XML：路徑表達式 /age/text() 返回文本節點"12"。函數 fn:data(/age) 返回字符串值"12"，fn:string(/age) 也是如此。
• 類型化 XML：對于任何簡單的類型化 元素，表達式 /age/text() 都會返回靜態錯誤。另一方面，fn:data(/age) 返回整數 12，而 fn:string(/age) 會產生字符串"12"。
聯合類型的函數和運算符
由于類型檢查，聯合類型要求進行認真的處理。以下示例闡述了其中兩個問題。
示例：聯合類型上的函數
請考慮以下聯合類型的 的元素定義
在 XQuery 上下文中，"average"函數 fn:avg (//r) 會返回靜態錯誤，因為 XQuery 編譯器無法對 fn:avg() 的參數中元素的不同類型（xs:int、xs:float 或 xs:double）的值求和。為解決該問題，請將函數調用改寫為 fn:avg(for $r in //r return $r cast as xs:double ?)。
示例：聯合類型上的運算符
加法運算"+"要求精確的操作數類型，以至于表達式 (//r)[1] + 1 對上述元素 的類型定義返回靜態錯誤?？梢越鉀Q該問題的一種改寫方式是 (//r)[1] cast as xs:int?+1，其中"?"表示具體取值 0 或 1。SQL Server 2005 要求帶有"?"的"cast as"，因為任何轉換都會由于運行時錯誤而產生空序列。
Value()、Nodes() 和 OpenXML()
可以在 SELECT 子句中對 XML 數據類型使用多個 value() 方法來生成提取值的行集。nodes() 方法會為所選的每個節點生成一個內部引用，以用于進一步查詢。當行集具有多個列，并且用于生成行集的路徑表達式可能比較復雜時，將 nodes() 和 value() 方法組合使用可能會更為有效。
nodes() 方法可生成特殊 XML 數據類型的實例，每個實例都將其上下文設置為所選的不同節點。此類 XML 實例支持 query()、value()、nodes() 和 exist() 方法，并且可用在 count(*) 聚合中。所有其他用法都會導致錯誤。
示例：nodes() 的用法
假設您希望提取名字不是"David"的作者的姓名，作為由兩個列（FirstName 和 LastName）組成的行集。使用 nodes() 和 value() 方法可以達到此目的，如下所示：
在該示例中，nodes('//author') 會生成一個由對每個 XML 實例的 元素的引用組成的行集。通過相對于這些引用對 value() 方法求值，可以獲取作者的名字和姓氏。
SQL Server 2000 提供了使用 OpenXml() 從 XML 實例生成行集的功能。您可以指定行集的關系架構，并指定 XML 實例內部的值如何映射到該行集中的列。
示例：對 XML 數據類型使用 OpenXml()
我們可以像下面顯示的那樣，使用 OpenXml() 來改寫上一示例中的查詢，方法是：創建一個游標，將各個 XML 實例讀入一個 XML 變量，然后向其應用 OpenXML()：
penXml() 會創建內存中的表示形式，并使用工作表而不是查詢處理器。它依賴于 MSXML 3.0 的 XPath 1.0 處理器而不是 XQuery 引擎。工作表不在對 OpenXml() 的多個調用中共享（即使是在同一個 XML 實例上）。這限制了它的可伸縮性。在未指定 WITH 子句時，可以通過 OpenXml() 來訪問 XML 數據的邊緣表格式。而且，還可以通過它使用 XML 值在單獨的"溢出"列中的剩余部分。
nodes() 和 value() 函數的組合可以有效地使用 XML 索引。因此，這一組合可以表現出比 OpenXml 更高的可伸縮性。
SELECT xCol.value('(//author/last-name)[1]', 'nvarchar(50)') LastName FROM T This query returns the value of the first element in each XML instance.
如果節點的類型無法確定，則它將成為 anyType，后者不會隱式轉換為任何其他類型。在使用父軸（例如，xCol.query('/book/@genre/../price')）進行導航的過程中，尤其會發生這種情況；該父節點類型被確定為 anyType。元素也可能被定義為 XML 架構中的 anyType。在這兩種情況下，丟失更為精確的類型信息通常會導致靜態類型錯誤，并且要求將原子值顯式轉換為它們的特定類型。
Data()、Text() 和 String() 訪問器
XQuery 具有一個可從節點中提取標量的、類型化值的函數 fn:data()，一個可返回文本節點的節點測試 text()，以及可返回節點的字符串值的函數 fn:string()。它們的用法有時會引起混亂。下面是有關在 SQL Server 2005 中正確使用它們的準則。請考慮 XML 實例 12：
• 非類型化 XML：路徑表達式 /age/text() 返回文本節點"12"。函數 fn:data(/age) 返回字符串值"12"，fn:string(/age) 也是如此。
• 類型化 XML：對于任何簡單的類型化 元素，表達式 /age/text() 都會返回靜態錯誤。另一方面，fn:data(/age) 返回整數 12，而 fn:string(/age) 會產生字符串"12"。
聯合類型的函數和運算符
由于類型檢查，聯合類型要求進行認真的處理。以下示例闡述了其中兩個問題。
示例：聯合類型上的函數
請考慮以下聯合類型的 的元素定義
<xs:element name="r"> <xs:simpleType> <xs:union memberTypes="xs:int xs:float xs:double"/> </xs:simpleType> </xs:element>
示例：聯合類型上的運算符
加法運算"+"要求精確的操作數類型，以至于表達式 (//r)[1] + 1 對上述元素 的類型定義返回靜態錯誤?？梢越鉀Q該問題的一種改寫方式是 (//r)[1] cast as xs:int?+1，其中"?"表示具體取值 0 或 1。SQL Server 2005 要求帶有"?"的"cast as"，因為任何轉換都會由于運行時錯誤而產生空序列。
Value()、Nodes() 和 OpenXML()
可以在 SELECT 子句中對 XML 數據類型使用多個 value() 方法來生成提取值的行集。nodes() 方法會為所選的每個節點生成一個內部引用，以用于進一步查詢。當行集具有多個列，并且用于生成行集的路徑表達式可能比較復雜時，將 nodes() 和 value() 方法組合使用可能會更為有效。
nodes() 方法可生成特殊 XML 數據類型的實例，每個實例都將其上下文設置為所選的不同節點。此類 XML 實例支持 query()、value()、nodes() 和 exist() 方法，并且可用在 count(*) 聚合中。所有其他用法都會導致錯誤。
示例：nodes() 的用法
假設您希望提取名字不是"David"的作者的姓名，作為由兩個列（FirstName 和 LastName）組成的行集。使用 nodes() 和 value() 方法可以達到此目的，如下所示：
SELECT nref.value('first-name[1]', 'nvarchar(50)') FirstName, nref.value('last-name[1]', 'nvarchar(50)') LastName FROM T CROSS APPLY xCol.nodes('//author') AS R(nref) WHERE nref.exist('.[first-name != "David"]') = 1
SQL Server 2000 提供了使用 OpenXml() 從 XML 實例生成行集的功能。您可以指定行集的關系架構，并指定 XML 實例內部的值如何映射到該行集中的列。
示例：對 XML 數據類型使用 OpenXml()
我們可以像下面顯示的那樣，使用 OpenXml() 來改寫上一示例中的查詢，方法是：創建一個游標，將各個 XML 實例讀入一個 XML 變量，然后向其應用 OpenXML()：
DECLARE name_cursor CURSOR FOR SELECT xCol FROM T OPEN name_cursor DECLARE @xmlVal XML DECLARE @idoc int FETCH NEXT FROM name_cursor INTO @xmlVal WHILE (@@FETCH_STATUS = 0) BEGIN EXEC sp_xml_preparedocument @idoc OUTPUT, @xmlVal SELECT * FROM OPENXML (@idoc, '//author') WITH (FirstName varchar(50) 'first-name', LastName varchar(50) 'last-name') R WHERE R.FirstName != 'David' EXEC sp_xml_removedocument @idoc FETCH NEXT FROM name_cursor INTO @xmlVal END CLOSE name_cursor DEALLOCATE name_cursor
nodes() 和 value() 函數的組合可以有效地使用 XML 索引。因此，這一組合可以表現出比 OpenXml 更高的可伸縮性。
使用 FOR XML 從行集中生成 XML
通過新的 TYPE 指令，可以使用 FOR XML 從行集中生成 XML 數據類型實例。
可以將結果賦給 XML 數據類型列、變量或參數。而且，可以將 FOR XML 嵌套以便生成任意層次結構。這使得嵌套的 FOR XML 比 FOR XML EXPLICIT 更加便于編寫，但是對于較深的層次結構，它的性能可能不太好。FOR XML 還引入了新的 PATH 模式，該模式指定列的值應該出現在 XML 樹中的哪個路徑。
可以使用新的 FOR XML TYPE 指令，通過 SQL 語法來定義關系數據上的只讀 XML 視圖?？梢酝ㄟ^ SQL 語句和嵌入式 XQuery 來查詢該視圖，如下面的示例所示。例如，您可以在存儲過程中引用此類 SQL 視圖。
示例：返回生成的 XML 數據類型的 SQL 視圖
下面的 SQL 視圖定義可在一個關系列 (pk) 以及從一個 XML 列中檢索到的書籍作者上創建一個 XML 視圖：
視圖 V 包含一個行，該行只有一個列：XML 類型的 xmlValtype?？梢韵癫樵兂Ｒ幍?XML 數據類型實例那樣查詢它。例如，下面的查詢將返回名字為"David"的作者：
SQL 視圖定義在某種程度上類似于使用帶有批注的架構創建的 XML 視圖。然而，二者之間存在重要的區別。SQL 視圖定義是只讀的，并且必須通過嵌入式 XQuery 來操作；而使用帶有批注的架構的 XML 視圖則不是這樣。而且，SQL 視圖在應用 XQuery 表達式之前生成 XML 結果，而 XML 視圖上的 XPath 查詢在基礎表上計算 SQL 查詢。
添加業務邏輯
可以用多種方式將業務邏輯添加到 XML 數據中：
• 您可以編寫行或列約束，在插入和修改 XML 數據的過程中實施特定于域的約束。
• 您可以在 XML 列上編寫相應的觸發器，使其當您在該列中插入或更新值時引發。該觸發器可以包含特定于域的驗證規則，或者填充屬性表。
• 可以使用托管代碼編寫 SQLCLR 函數并向其傳遞 XML 值，并且使用由 System.Xml 命名空間提供的 XML 處理功能。這方面的一個例子是將 XSL 轉換應用于 XML 數據，如下所示。您還可以將 XML 反序列化為一個或多個托管類，并且使用托管代碼來操作它們。
• 您可以編寫 T-SQL 存儲過程和函數，激活 XML 列上的處理以滿足您的業務需要。
示例：應用 XSL 轉換
考慮 CLR 函數 TransformXml()，它接受一個 XML 數據類型實例和一個存儲在文件中的 XSL 轉換，將該轉換應用于 XML 數據，并且在結果中返回轉換后的 XML。用 C# 編寫的主干函數如下所示：
在注冊該程序集，并且創建了對應于 TransformXml() 的用戶定義 T-SQL 函數 SqlXslTransform() 之后，就可以像在下面的查詢中那樣從 T-SQL 中調用該函數：
查詢結果包含轉換后的 XML 的行集。
SQLCLR 打開了一個全新的世界，可以使用它將 XML 數據分解到表或屬性提升中，并使用 System.Xml 命名空間中的托管類來查詢 XML 數據。有關詳細信息，請參閱 SQL Server 2005 和 Microsoft Visual Studio"Whidbey"聯機圖書。
通過新的 TYPE 指令，可以使用 FOR XML 從行集中生成 XML 數據類型實例。
可以將結果賦給 XML 數據類型列、變量或參數。而且，可以將 FOR XML 嵌套以便生成任意層次結構。這使得嵌套的 FOR XML 比 FOR XML EXPLICIT 更加便于編寫，但是對于較深的層次結構，它的性能可能不太好。FOR XML 還引入了新的 PATH 模式，該模式指定列的值應該出現在 XML 樹中的哪個路徑。
可以使用新的 FOR XML TYPE 指令，通過 SQL 語法來定義關系數據上的只讀 XML 視圖?？梢酝ㄟ^ SQL 語句和嵌入式 XQuery 來查詢該視圖，如下面的示例所示。例如，您可以在存儲過程中引用此類 SQL 視圖。
示例：返回生成的 XML 數據類型的 SQL 視圖
下面的 SQL 視圖定義可在一個關系列 (pk) 以及從一個 XML 列中檢索到的書籍作者上創建一個 XML 視圖：
CREATE VIEW V (xmlVal) AS SELECT pk, xCol.query('/book/author') FROM T FOR XML AUTO, TYPE
SELECT xmlVal.query('//author[first-name = "David"]') FROM V
添加業務邏輯
可以用多種方式將業務邏輯添加到 XML 數據中：
• 您可以編寫行或列約束，在插入和修改 XML 數據的過程中實施特定于域的約束。
• 您可以在 XML 列上編寫相應的觸發器，使其當您在該列中插入或更新值時引發。該觸發器可以包含特定于域的驗證規則，或者填充屬性表。
• 可以使用托管代碼編寫 SQLCLR 函數并向其傳遞 XML 值，并且使用由 System.Xml 命名空間提供的 XML 處理功能。這方面的一個例子是將 XSL 轉換應用于 XML 數據，如下所示。您還可以將 XML 反序列化為一個或多個托管類，并且使用托管代碼來操作它們。
• 您可以編寫 T-SQL 存儲過程和函數，激活 XML 列上的處理以滿足您的業務需要。
示例：應用 XSL 轉換
考慮 CLR 函數 TransformXml()，它接受一個 XML 數據類型實例和一個存儲在文件中的 XSL 轉換，將該轉換應用于 XML 數據，并且在結果中返回轉換后的 XML。用 C# 編寫的主干函數如下所示：
public static SqlXml TransformXml (SqlXml XmlData, string xslPath) { // Load XSL transformation XslTransform xform = new XslTransform(); XPathDocument xslDoc = new XPathDocument (xslPath); xform.Load (xslDoc.CreateNavigator(),null); // Load XML data XPathDocument xDoc = new XPathDocument (XmlData.CreateReader()); XPathNavigator nav = xDoc.CreateNavigator (); // Return the transformed value SqlXml retSqlXml = new SqlXml (xform.Transform(nav, null)); return (retSqlXml); }
SELECT SqlXslTransform (xCol, 'C:\yukon\xsltransform.xsl') FROM T WHERE xCol.exist('/book/title/text()[contains(.,"custom")]') =1
SQLCLR 打開了一個全新的世界，可以使用它將 XML 數據分解到表或屬性提升中，并使用 System.Xml 命名空間中的托管類來查詢 XML 數據。有關詳細信息，請參閱 SQL Server 2005 和 Microsoft Visual Studio"Whidbey"聯機圖書。
原文轉自：http://www.anti-gravitydesign.com
