SQL Server T-SQL 操作 XML 資料完整教學

XML 資料類型簡介

SQL Server 2005 引入原生 xml 型別，儲存為內部 BLOB 格式，支援檔案順序與遞迴結構。可直接將 XML 字串隱性轉換存入。

建立 XML 欄位與變數

CREATE TABLE docs (pk INT PRIMARY KEY, xCol XML NOT NULL);

DECLARE @xmlDoc XML;
SET @xmlDoc = '
  Jerry50
  Tom49
';

查詢函數：query() vs value()

• query('XQuery')：回傳 含標籤 的 XML 片段，型別為 xml。
• value('XQuery', 'SQLType')：回傳 純量值，指定目標 SQL 型別。

常用 XQuery 範例

-- 所有書名（含標籤）
SELECT @xmlDoc.query('/root/book/title');

-- 第 2 本書名
SELECT @xmlDoc.query('(//title)[2]');

-- 使用變數篩選
DECLARE @id VARCHAR(MAX) = '0001';
SELECT @xmlDoc.query('/root/book[@id=sql:variable("@id")]/title');

-- 取純量值（單一結果）
SELECT @xmlDoc.value('(/root/book[1]/title)[1]', 'NVARCHAR(50)');

修改 XML 資料（modify）

-- 插入新書
SET @xmlDoc.modify('insert  into (/root)[1]');

-- 更新價格
SET @xmlDoc.modify('replace value of (/root/book[@id="0001"]/price/text())[1] with "55"');

-- 刪除書籍
SET @xmlDoc.modify('delete /root/book[@id="0002"]');

索引 XML 欄位

1. 建立 主要 XML 索引：CREATE PRIMARY XML INDEX idx_xCol ON docs(xCol);
2. 建立 次要 XML 索引（PATH、VALUE、PROPERTY）加速特定查詢模式。