SQL Server 2005 原生 XML 支援深度解析：從儲存到查詢的完整指南（上）

一、為什麼在關係資料庫儲存 XML？

XML 作為平台無關的資料表示標準，已成為 B2B 整合、工作流、文件管理的通用語言。SQL Server 2005 引入原生 XML 型別，解決三大核心痛點：

• 統一管理：關係與 XML 資料共享備份/還原、複寫、安全性、ACID 交易
• 查詢融合：單一查詢同時關聯關係表與 XML 列，利用成本基礎優化器
• 索引加速：將成熟的 B+ 樹索引技術延伸至 XML 節點，支援路徑/值/屬性查詢

二、SQL Server 2000 XML 架構回顧

伺服器端

• FOR XML RAW/AUTO/EXPLICIT：查詢結果序列化為 XML 串流
• OPENXML：`sp_xml_preparedocument` → 記憶體 DOM → `SELECT * FROM OPENXML(...)` → `sp_xml_removedocument`
• 缺點：記憶體壓力大、需手動管理控制碼、XPath 1.0 功能受限

用戶端 (SQLXML)

• XML 視圖：帶註釋 XDR/XSD 映射結構描述 → 雙向關係⇄XML
• XPath 子集查詢 → 自動轉譯為 SQL → 打包為 XML 結果
• XML 範本：嵌入 `FOR XML`/XPath 的靜態/動態 XML 文件
• 存取介面：`SQLXMLOLEDB Provider` (ADO) + HTTP ISAPI 端點

三、SQL Server 2005 核心增強：XML 成為一等公民

1. XML 資料型別定義

-- 未型別 XML
CREATE TABLE T1 (Col1 XML);

-- 型別 XML：綁定結構描述集合
CREATE XML SCHEMA COLLECTION SC AS N'';
CREATE TABLE T2 (Col1 XML(SC));

2. 五大實例方法 (前述文章詳表，此處補充語法細節)

3. XML 索引架構

-- 主要索引 (必須先建立叢集主鍵)
CREATE PRIMARY XML INDEX PXI_T_Col1 ON T(Col1);

-- 次要索引
CREATE XML INDEX SXI_T_Col1_PATH ON T(Col1) USING XML INDEX PXI_T_Col1 FOR PATH;
CREATE XML INDEX SXI_T_Col1_VALUE ON T(Col1) USING XML INDEX PXI_T_Col1 FOR VALUE;
CREATE XML INDEX SXI_T_Col1_PROP ON T(Col1) USING XML INDEX PXI_T_Col1 FOR PROPERTY;

• 主要索引：將 XML 解構為內部資料表 (節點 ID、父 ID、路徑、值、型別)
• PATH 索引：加速路徑導向查詢 (如 `/Order/Item/@Price`)
• VALUE 索引：加速值導向查詢 (如 `//Item[. = 'ABC']`)
• PROPERTY 索引：加速屬性包查詢 (如 `XmlCol.query('/*/@*')`)

4. FOR XML / OPENXML 增強

• FOR XML PATH('ElementName')：替代 EXPLICIT，支援巢狀屬性/元素控制
• FOR XML ... TYPE：直接回傳 XML 型別而非串流，可嵌套
• OPENXML 仍支援，但推薦改用 nodes() + `CROSS APPLY`

四、典型應用場景與模式

場景 1：動態屬性 / EAV 模型替代方案

CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    Name NVARCHAR(100),
    Attributes XML -- 存放不固定屬性：
);
-- 查詢紅色產品
SELECT Name FROM Products
WHERE Attributes.exist('/Attrs/A[@Key="Color" and @Val="Red"]') = 1;

CREATE TABLE AuditLog (
    LogID BIGINT IDENTITY PRIMARY KEY,
    EventTime DATETIME2 DEFAULT SYSUTCDATETIME(),
    EventType NVARCHAR(50),
    Payload XML -- 完整變更前後狀態
);
-- 索引策略：主要索引 + PATH 索引 (常按 EventType/實體 ID 查詢)