延續前一篇以純 T-SQL 剖析 XML 的做法,這裡提供一個改寫成 CLR 的版本,主要目的是把剖析工作從 T-SQL 的迴圈搬到 .NET 平台上,以取得更好的串流處理能力。整體分為三步:
SqlReaderBase,實作 ISqlReader 介面,透過在 XML 執行個體上套用路徑運算式來產生串流式的表值輸出。首先建立串流的 CLR 函數,主幹如下。XML 資料型別在 ADO.NET 中被對外公開為託管類別 SqlXml,它支援 CreateReader() 方法回傳 XMLReader:
public class c_streaming_xml_tvf {
public static ISqlReader streaming_xml_tvf(SqlXml xmlDoc, string pathExpression) {
return (new TestSqlReaderBase(xmlDoc, pathExpression));
}
}
// 實作 ISqlReader 的類別
public class TestSqlReaderBase : ISqlReader {
XPathNodeIterator m_iterator;
public SqlChars FirstName; // 目前結果集的資料
private SqlMetaData[] m_rgSqlMetaData;
public TestSqlReaderBase(SqlXml xmlDoc, string pathExpression) {
XPathDocument xDoc;
XPathNavigator xNav;
XPathExpression xPath;
m_rgSqlMetaData = new SqlMetaData[1];
m_rgSqlMetaData[0] = new SqlMetaData("FirstName", SqlDbType.NVarChar, 50);
if (!xmlDoc.IsNull)
xDoc = new XPathDocument(xmlDoc.CreateReader());
else
xDoc = new XPathDocument();
xNav = xDoc.CreateNavigator();
xPath = xNav.Compile(pathExpression);
m_iterator = xNav.Select(xPath);
}
public bool Read() {
bool moreRows = true;
if (moreRows = m_iterator.MoveNext())
FirstName = new SqlChars(m_iterator.Current.Value);
return moreRows;
}
}
接著建立組件,並對應出一支名為 SQL_streaming_xml_tvf 的 T-SQL UDF,再用它定義表值函數 CLR_udf_XML2Table 來產生資料列集:
create function CLR_udf_XML2Table (@pk int, @xCol XML)
returns @ret_Table table (FK int, FirstName varchar(max))
with schemabinding
as
begin
insert into @ret_Table
select @pk, FirstName
from SQL_streaming_xml_tvf(@xCol, '/book/author/first-name')
return
end
最後定義觸發器,做法與非 CLR 版本相同,只要把 udf_XML2Table 換成 CLR_udf_XML2Table 即可。以插入觸發器為例:
create trigger CLR_trg_docs_INS on T for insert
as
declare @wantedXML XML
declare @FK int
select @wantedXML = xCol from inserted
select @FK = PK from inserted
insert into tblPropAuthor
select * from dbo.CLR_udf_XML2Table(@FK, @wantedXML)
刪除觸發器與非 CLR 版本完全相同;更新觸發器也只是把呼叫的函數名稱替換掉而已。
XML 架構集合是一個由關聯式架構界定範圍的中繼資料實體,其中包含一個或多個彼此相關(例如透過 import 或 include 產生關聯)或彼此無關的 XML 架構。集合中的每個獨立架構,由其目標命名空間來識別。
建立語法為 CREATE XML SCHEMA COLLECTION,可一次提供多個架構;若要為既有集合補上更多架構組件,則使用 ALTER XML SCHEMA COLLECTION。此外,SQL Server 2005 的安全性模型可以像保護其他 SQL 物件一樣,對 XML 架構集合設定權限。
假設架構集合 C 依據多個 XML 架構來型別化 XML 欄位 xCol,同時可用 DOCUMENT 或 CONTENT 旗標分別指定該欄位只能存放完整的 XML 樹,還是允許存放片段。
使用 DOCUMENT 時,每個 XML 執行個體的頂層元素都必須符合用來驗證與型別化的命名空間;使用 CONTENT 時,每個頂層元素則可以指定 C 中的任一目標命名空間,執行個體會依其中實際存在的所有目標命名空間分別驗證與型別化。
XML 架構集合除了型別化欄位、變數與參數,還提供了一套架構演化的機制。以下面的情境說明:
先把目標命名空間為 BOOK-V1 的架構加入集合 C,此後型別化自 C 的欄位 xCol 就能存放符合 BOOK-V1 的資料。若應用程式希望擴充這個架構(例如新增複雜型別定義或頂層元素宣告),這些新的架構組件可以直接加進 BOOK-V1,且不需要對 xCol 中既有的 XML 資料重新驗證。
若應用程式後來決定推出新版本,並為它挑選 BOOK-V2 作為目標命名空間,這個新架構同樣可以加入 C。此後欄位 xCol 就能同時存放 BOOK-V1 與 BOOK-V2 兩種執行個體,並對符合這些命名空間的資料執行查詢與修改。這正是「向後相容」在 XML 型別化場景中的具體體現。
把 XML 資料從 SQL Server 2000 轉移到 2005 有幾種常見路徑:
[n]text 或 image 欄位中,可以先用 DTS 這類工具把整張表匯入 2005,再用 ALTER TABLE 把欄位型別改成 XML。bcp out 從 2000 大量匯出,再用 bcp in 大量匯入 2005。ntext 欄位的新表(視需要加上主鍵作為行識別碼),用 FOR XML 在伺服器端產生 XML 並寫入該欄,再套用上述技巧搬移;也可以直接把 XML 寫進 2005 的 XML 欄位。單純的型別轉換寫法如下:
ALTER TABLE R ALTER COLUMN XYZ XML
若有需要,還可以在轉換時指定一個 XML 架構集合,把目標型別化。
除了 bcp,也可以透過 OPENROWSET 把檔案中的資料直接載入 XML 欄位:
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 資料,從伺服器取回的內容也是 UTF-16;若需要其他編碼,必須自行轉換。載入不同編碼的資料時要特別小心:
varbinary()(不與碼頁互動)或採用適當碼頁的字串型別,再賦值給 XML 欄位、變數或參數。以下範例示範如何為一份沒有 XML 宣告、以 varchar(max) 存放的文件補上 iso8859-1 宣告:先串接宣告字串,轉成 varbinary(max) 以保留位元組表示,最後再轉成 XML,讓 XML 處理器能依指定編碼解析並產生對應的 UTF-16 字串值。
SELECT CAST(
CAST(('<?xml version="1.0" encoding="iso8859-1"?>' + vcdoc)
AS VARBINARY(MAX))
AS XML)
嵌入 T-SQL 的 XQuery 語言(參見 W3C 的 xquery 規格草案)支援查詢 XML 資料型別,並以 XPath 2.0 作為導覽語言,同時提供針對 XML 的資料修改語法構造。所有主要資料庫廠商都參與了這套標準的制訂。
具語法錯誤的 XQuery 運算式與 XML DML 陳述式會回傳編譯錯誤。編譯階段會檢查靜態型別正確性,對型別化 XML 還會依 XML 架構進行型別推斷。若某個運算式在執行期可能因型別安全衝突而失敗,就會引發靜態型別錯誤——例如把字串加到整數上,或在不存在的節點中查詢型別化資料。
與 W3C 標準不同的是,XQuery 的執行期錯誤會被轉換為空序列,並依呼叫端而定以空 XML 或 NULL 的形式傳遞。開發者可以透過明確轉換到正確型別來避開靜態錯誤,而執行期的轉換失敗則會被視為空序列處理。
若編譯器無法確定某個定位步驟、函數參數或運算子(例如 eq)在執行期能否保證唯一性,就會回報錯誤。這個問題經常出現在非型別化資料上:屬性查詢要求父元素唯一,因此需要用序號來確保能選出單一父節點。
下面的範例中,nodes() 為每個 book 元素產生一列,而在該節點上求值的 value() 提取 @genre 屬性值——由於屬性本身具唯一性,這裡不需要再加序號:
SELECT nref.value('@genre', 'varchar(max)') AS LastName
FROM T CROSS APPLY xCol.nodes('//book') AS R(nref)
型別化 XML 則由 XML 架構負責型別檢查:若節點在架構中被宣告為唯一,編譯器會採用這項資訊而不報錯;否則仍須加上能選出單一節點的序號。特別是當你使用子代或自身軸(//),例如 /book//title 時,唯一性資訊往往會因此失落。