SQL Server 2005 XML 資料類型處理實戰筆記

前言

SQL Server 2005 引入原生 XML 資料類型，提供豐富的方法進行查詢、擷取、驗證與修改。本文記錄核心方法用法與實戰範例。

核心 XML 方法速查

環境建置

CREATE TABLE VisioXML (ID INT, Doc XML)
GO

-- 從檔案載入 XML（需檔案位於伺服器端）
INSERT INTO VisioXML (ID, Doc)
SELECT 4, * FROM OPENROWSET(BULK 'e:\xxx.xml', SINGLE_BLOB) AS x

範例 XML 結構：

<ROOT>
  <ROW><ID>1</ID><NAME SEX="MALE">WBQ</NAME></ROW>
  <ROW><ID>2</ID><NAME SEX="FEMALE">CZH</NAME></ROW>
</ROOT>

value() 與 query() 範例

SELECT
    Doc.value('(/ROOT/ROW[1]/ID/text())[1]', 'int') AS RootRowID1,
    Doc.value('(/ROOT/ROW[2]/ID/text())[1]', 'int') AS RootRowID2,
    Doc.value('(/ROOT/ROW[1]/NAME/text())[1]', 'varchar(20)') AS RootRowNAME1,
    Doc.value('(/ROOT/ROW[1]/NAME/@SEX)[1]', 'varchar(20)') AS RootRowNAME1SEX,
    Doc.query('/ROOT') AS Root,
    Doc.query('/ROOT/ROW[1]') AS RootRow1,
    Doc.query('/ROOT/ROW[2]') AS RootRow2
FROM VisioXML WHERE ID = 4

關鍵點：value() 需加 [1] 指定單一節點，並強制轉型；query() 保留 XML 結構。

exist() 方法應用

條件判斷（純量回傳 1/0）

SELECT
    Doc.exist('/ROOT/ROW[1]/NAME[(@SEX cast as xs:string?) = xs:string("MALE")]') AS Row1EQStringMale,
    Doc.exist('/ROOT/ROW[1]/NAME[(@SEX cast as xs:string?) = "MALE"]') AS Row1EQMale,
    Doc.exist('/ROOT/ROW[1]/ID[(text()[1] cast as xs:float?) = xs:float(1)]') AS Row1EQfloat1,
    Doc.exist('/ROOT/ROW[2]/ID[(text()[1] cast as xs:float?) = 2]') AS Row1EQ1
FROM VisioXML WHERE ID = 4

WHERE 子句中過濾

SELECT ID, Doc
FROM VisioXML
WHERE ID = 4
  AND Doc.exist('/ROOT/ROW[1]/NAME[@SEX]') = 1  -- 存在 SEX 屬性
-- AND Doc.exist('/ROOT/ROW[1]/NAME[not(@SEX)]') = 1  -- 不存在 SEX 屬性

進階技巧：nodes() 將 XML 拆解為資料列

-- 將每個 ROW 節點拆為一列
SELECT
    T.C.value('(ID/text())[1]', 'int') AS ID,
    T.C.value('(NAME/text())[1]', 'varchar(20)') AS Name,
    T.C.value('(NAME/@SEX)[1]', 'varchar(10)') AS Sex
FROM VisioXML
CROSS APPLY Doc.nodes('/ROOT/ROW') AS T(C)
WHERE ID = 4

效能與最佳實務

• 對 XML 欄位建立 XML 索引（主 XML 索引 + 次要 XML 索引）大幅提升查詢速度。
• 使用 value() 而非 query() 擷取純量值，避免 XML 序列化開銷。
• 大量 XML 資料建議儲存於檔案系統或 Blob 儲存，資料庫僅存路徑或摘要。
• SQL Server 2016+ 支援 JSON 功能，若資料結構簡單可考慮 JSON 替代。