SQL Server XML 資料型別 DML 完整操作指南：INSERT、UPDATE、DELETE 詳細範例

SQL Server 2005 引入 xml 型別與 xml.modify() 方法，支援三種 XML DML 操作：insert、delete、replace value of。本文以完整範例 XML 文件為基礎，系統展示節點插入 (位置控制、多節點、屬性、文字、註解、處理指令、條件插入)、刪除 (屬性、節點、文字、整棵子樹)、取代 (屬性值、文字值、條件取代) 的完整語法與結果。

基準 XML 文件

DECLARE @XMLVar XML = '

  
    
    Bill Zack
    49.99
  
  
    
    Andrew Brust
    39.93
  
  
    
    Stephen Forte
    59.99
  
';

1. INSERT 操作 (xml.modify('insert ...'))

A. 位置控制：as first / as last / before / after

-- 作為第一個子節點插入
SET @XMLVar.modify('insert  as first into (/catalog[1]/book[1])');

-- 作為最後一個子節點插入
SET @XMLVar.modify('insert  as last into (/catalog[1]/book[1])');

-- 在 author 前插入
SET @XMLVar.modify('insert  before (/catalog[1]/book[1]/author[1])');

-- 在 author 後插入
SET @XMLVar.modify('insert  after (/catalog[1]/book[1]/author[1])');

SELECT @XMLVar.query('/catalog[1]/book[1]');

結果： first、before、author、after、price、last 依序排列

B. 多節點一次插入

-- 方法 1：變數
DECLARE @newFeatures XML = 'one elementsecond element';
SET @XMLVar.modify('insert sql:variable("@newFeatures") into (/catalog[1]/book[1])');

-- 方法 2：直接序列
SET @XMLVar.modify('insert (one element,second element) into (/catalog[1]/book[1]/author[1])');

C. 屬性插入

-- 變數插入屬性
DECLARE @var NVARCHAR(10) = N'變數插入';
SET @XMLVar.modify('insert (attribute var {sql:variable("@var")}) into (/catalog[1]/book[1])');

-- 直接插入屬性
SET @XMLVar.modify('insert (attribute name {"直接插入"}) into (/catalog[1]/book[1]/title[1])');

-- 多屬性一次插入
SET @XMLVar.modify('insert (attribute Id {"多值插入1"}, attribute name {"多值插入2"}) into (/catalog[1]/book[1]/author[1])');

D. 文字節點插入

SET @XMLVar.modify('insert text{"at first"} as first into (/catalog[1]/book[1])');
-- 同樣支援 as last / before / after

E. 註解節點插入

SET @XMLVar.modify('insert  before (/catalog[1]/book[1]/title[1])');

F. 處理指令插入

SET @XMLVar.modify('insert  before (/catalog[1]/book[1]/title[1])');

G. 條件插入 (XQuery if-then-else)

SET @XMLVar.modify('
insert 
if (/catalog[1]/book[1]/title[2]) then
    text{"this is a 1 step"}
else
    text{"this is a 2 step"}
into (/catalog[1]/book[1]/price[1])');
-- 因 title[2] 不存在，插入 "this is a 2 step"

2. DELETE 操作 (xml.modify('delete ...'))

-- 刪除屬性
SET @XMLVar.modify('delete /catalog[1]/book[1]/@category');

-- 刪除節點 (含子樹)
SET @XMLVar.modify('delete /catalog[1]/book[1]/title[1]');

-- 刪除文字內容 (保留元素)
SET @XMLVar.modify('delete /catalog[1]/book[1]/author[1]/text()');

-- 刪除整個 book[2] 元素
SET @XMLVar.modify('delete /catalog[1]/book[2]');

SELECT @XMLVar.query('/catalog[1]');

結果： book[1] 僅剩 <book><author/><price>49.99</price></book>，book[2] 整個移除，book[3] 保留

3. REPLACE VALUE OF 操作 (xml.modify('replace value of ...'))

-- 取代屬性值
SET @XMLVar.modify('replace value of (/catalog[1]/book[1]/@category) with ("取代屬性")');

-- 取代文字節點值
SET @XMLVar.modify('replace value of (/catalog[1]/book[1]/author[1]/text()[1]) with ("取代內容")');

-- 條件取代
SET @XMLVar.modify('replace value of (/catalog[1]/book[2]/@category) with (
    if (count(/catalog[1]/book) > 4) then "條件取代1" else "條件取代2"
)');
-- 因 book 共 3 筆，取代為 "條件取代2"

SELECT @XMLVar.query('/catalog[1]');

語法速查總表

效能與最佳實務

• xml.modify() 會觸發 XML 索引維護，大量批次修改建議：
  ① 停用 XML 索引 → ② 批次修改 → ③ 重建 XML 索引
• 單一修改影響大型 XML 文件時，考慮將 XML 拆解為關聯式資料表
• 頻繁修改的 XPath 路徑建立 PATH 次要 XML 索引
• 修改前後搭配 TRY...CATCH 確保交易一致性
• 生產環境大量 XML DML 作業建議在維護窗口執行

適用版本

• SQL Server 2005+ (所有版本)
• Azure SQL Database / Managed Instance
• SQL Server on Linux