您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

SQL Server 原生 XML 資料型別與進階處理：從儲存到索引查詢完整指南

編輯：關於SqlServer

SQL Server 2005（代號 Yukon）引入原生 XML 資料型別，將 XML 從單純文字提升為一級公民，支援結構化儲存、結構描述驗證、XQuery/XPath 查詢與高效索引，徹底改變半結構化資料處理方式。

核心功能架構

XML 資料型別 (xml Data Type)

型別分類：xml（無結構描述）與 xml(COLLECTION)（綁定 XML 結構描述集合）。
儲存格式：內部以高效二進位表現（BLOB 結構化），保留文件順序、節點身分、型別資訊。
限制：單一 XML 執行個體上限 2 GB；不支援 DTD 實體參考；最多 128 階層深度。

XML 索引策略

索引類型	建立語法	適用場景
主要 XML 索引	`CREATE PRIMARY XML INDEX idx_xml ON T(col)`	所有 XML 查詢基礎，切碎節點儲存於內部表
路徑次要索引	`CREATE XML INDEX idx_path ON T(col) USING XML INDEX idx_xml FOR PATH`	路徑導向查詢（`/a/b/c`）、exist()、value()
屬性次要索引	`CREATE XML INDEX idx_prop ON T(col) USING XML INDEX idx_xml FOR PROPERTY`	屬性值查詢（`@attr`）、PK 查找
值次要索引	`CREATE XML INDEX idx_val ON T(col) USING XML INDEX idx_xml FOR VALUE`	節點值比對、範圍查詢、不指定路徑的值搜尋

注意：主要索引佔用空間約為 XML 資料 3~5 倍，規劃容量時須納入考量。

XML 資料型別方法 (Methods)

query('XQuery')：回傳 xml 型別片段。
value('XQuery', 'sqlType')：回傳純量 SQL 型別值（需保證單一節點）。
exist('XQuery')：回傳 bit (1/0/NULL)，判斷節點是否存在，極適合 WHERE 子句。
nodes('XQuery')：將 XML 切割為關聯式資料列集，搭配 CROSS APPLY 實現「一對多」展開。
modify('XML DML')：插入/刪除/更新節點，僅限 xml 變數或資料行，不可用於 SELECT。

實務範例：訂單明細 XML 儲存與查詢

-- 建立結構描述集合
CREATE XML SCHEMA COLLECTION Sales.OrderSchema AS 
N'
  
    
      
        
          
            
            
            
          
        
      
      
      
    
  
';

-- 建立資料表
CREATE TABLE Sales.OrdersXML (
    OrderDoc xml(Sales.OrderSchema) NOT NULL,
    CreatedDate datetime DEFAULT GETDATE()
);

-- 建立主要 XML 索引
CREATE PRIMARY XML INDEX IX_OrdersXML_Doc ON Sales.OrdersXML(OrderDoc);

-- 插入資料
INSERT INTO Sales.OrdersXML (OrderDoc) VALUES (
'
  
  
');

-- 查詢：取得特定客戶的所有產品 ID
SELECT OrderDoc.value('(/Order/@OrderID)[1]', 'int') AS OrderID,
       Item.value('@ProductID', 'int') AS ProductID,
       Item.value('@Qty', 'int') AS Qty
FROM Sales.OrdersXML
CROSS APPLY OrderDoc.nodes('/Order/Item') AS T(Item)
WHERE OrderDoc.exist('/Order[@CustomerID="ALFKI"]') = 1;

效能與最佳實務

「窄表 + XML 欄位」模式適合稀疏屬性、結構多變的資料（如問卷、屬性包、日誌）。
頻繁查詢的固定路徑務必建立次要索引，避免掃描主要索引內部表。
大量載入時：先 停用索引 → 大量插入 → 重建索引，效率遠高於逐筆插入。
避免在 WHERE 中直接使用 value()，改用 exist() 利用索引求存。 exist() 可利用路徑/值索引；value() 通常導致掃描。
SQL Server 2012+ 新增 選擇性 XML 索引，可針對特定路徑建立索引，大幅縮減索引體積。