SQL Server 2005 XML 資料型別完整操作指南：查詢、修改與跨表關聯

SQL Server 2005 引入原生 xml 資料型別，支援 XQuery/XPath 查詢與 XML DML 修改。本文系統整理從建立欄位、插入資料、多維度查詢（節點值、屬性、跨表關聯）到資料修改的完整實作範例，並附上執行結果說明。

1. 建立 XML 欄位

CREATE TABLE xmlTest (
    id INT IDENTITY(1,1) PRIMARY KEY,
    _x XML  -- XML 型別欄位
)

2. 插入 XML 資料

-- 第一筆
INSERT INTO xmlTest (_x) VALUES ('

  item11
  item12
')

-- 第二筆
INSERT INTO xmlTest (_x) VALUES ('

  item21
  item22
')

3. 多維度查詢範例

3.1 查詢完整 XML 內容

SELECT _x FROM xmlTest

3.2 查詢第一個 item 節點

SELECT _x.query('root/item[1]') FROM xmlTest

3.3 取得第一個 item 節點的文字值

SELECT _x.value('(root/item)[1]', 'varchar(50)') FROM xmlTest

3.4 取得第二個 item 的 attr2 屬性值

SELECT _x.value('(root/item/@attr2)[2]', 'varchar(50)') FROM xmlTest

3.5 將所有 item 節點值展開為資料列

SELECT t2.c1.value('.', 'varchar(20)') AS items
FROM xmlTest
CROSS APPLY _x.nodes('root/item') AS t2(c1)

3.6 查詢特定 id 記錄的所有 attr2 屬性值

SELECT t2.c1.value('@attr2', 'varchar(20)') AS attr2
FROM xmlTest
CROSS APPLY _x.nodes('root/item') AS t2(c1)
WHERE id = 1

3.7 條件過濾：取得 attr1='a1' 的第一個匹配節點 attr2 值

SELECT _x.value('(root/item[@attr1="a1"]/@attr2)[1]', 'varchar(50)')
FROM xmlTest WHERE id = 1

3.8 跨表 XML 關聯查詢

情境：表 t2 有欄位 xmlid 關聯 xmlTest.id，及 attr1 需比對 XML 內 @attr1 值

SELECT xmlTest._x.value('(root/item[@attr1=sql:column("t2.attr1")]/@attr2)[1]', 'varchar(50)') AS attr2,
       t2.attr1
FROM xmlTest JOIN t2 ON t2.xmlid = xmlTest.id
WHERE xmlTest.id = 1

4. XML 資料修改

4.1 修改節點文字值

UPDATE xmlTest
SET _x.modify('replace value of (root/item/text())[1] with "xxxxx"')
WHERE id = 1

-- 驗證
SELECT _x.query('root/item[1]') AS item1 FROM xmlTest

4.2 修改屬性值

UPDATE xmlTest
SET _x.modify('replace value of (root/item/@attr2)[2] with "2222b"')
WHERE id = 2

-- 驗證
SELECT _x.query('root/item[2]') AS item1 FROM xmlTest

關鍵語法速查表

效能建議

• 對 XML 欄位建立 主要 XML 索引 與 次要 XML 索引（PATH、VALUE、PROPERTY）
• 避免在大量資料上使用 // 後代軸查詢，優先使用具體路徑
• 頻繁查詢的 XPath 路徑可考慮提升為計算欄位並建立一般索引
• .modify() 會觸發 XML 索引維護，批次更新時建議先停用索引