SQL Server XML 資料解析全攻略：從入門到進階實戰

前言

隨著系統整合需求日益增長，XML 在資料庫中作為半結構化資料儲存或傳輸格式的場景越來越多。SQL Server 內建強大的 XML 資料類型與 XQuery 支援，讓我們能直接在 T-SQL 中高效查詢、修改、撕析 XML。本文將系統梳理 5 大核心方法、屬性/元素操作、動態解析技巧，並提供實用的通用儲存程式碼。

一、XML 資料類型的五大核心方法

SQL Server 的 xml 型別提供 5 個實例方法，是所有操作的基石：

二、實戰範例：多樣化的節點讀取技巧

宣告測試 XML 變數：

DECLARE @x XML = 
'
    
        dongsheng@xxyy.com
        678945546
        36575
    
';

1. 精確定位單一值 (.value())

-- 方法 1: data() 函數取文字節點
SELECT @x.value('data(/People/dongsheng/Info[@Name="Email"])[1]', 'varchar(30)');
-- 方法 2: 直接取元素節點 (隱含文字)
SELECT @x.value('(/People/dongsheng/Info[@Name="Email"])[1]', 'varchar(30)');

關鍵點：路徑末尾必須加 [1] 指定單一節點，否則報錯；data() 明確語意為取文字內容。

2. 將節點集拆解為資料列 (.nodes() + CROSS APPLY)

-- 方法 3: 直接針對 Info 節點
SELECT C.value('.', 'varchar(30)') 
FROM @x.nodes('/People/dongsheng/Info[@Name="Email"]') AS T(C);

-- 方法 4: 先定位父節點 dongsheng，再取子節點
SELECT C.value('(Info[@Name="Email"])[1]', 'varchar(30)') 
FROM @x.nodes('/People/dongsheng') AS T(C);

進階：.nodes() 會將每個匹配節點產生一列，配合 CROSS APPLY 即可像操作資料表一樣操作 XML 節點，極適合「一轉多」場景。

3. 條件過濾與存在性判斷

-- 方法 6: WHERE 子句過濾
SELECT C.value('.', 'varchar(30)') 
FROM @x.nodes('/People/dongsheng/Info') AS T(C)
WHERE C.value('(@Name)[1]', 'varchar(20)') = 'Email';

-- 方法 7: 使用 .exist() 判斷 (效能較佳)
SELECT C.value('.', 'varchar(30)') 
FROM @x.nodes('/People/dongsheng/Info') AS T(C)
WHERE C.exist('@Name[.="Email"]') = 1;

三、屬性操作大全

1. 讀取屬性值

DECLARE @x XML = 
'
    
    
    
';

SELECT 
    v.value('@Name[1]', 'VARCHAR(20)') AS Name, 
    v.value('@Sex[1]', 'VARCHAR(20)') AS Sex 
FROM @x.nodes('/Peoples/People') AS x(v);

2. 多屬性條件過濾

-- 查詢 dept="IT" 的員工 Info 屬性
SELECT 
    C.value('@NAME[1]', 'VARCHAR(10)') AS NAME,
    C.value('@SEX[1]', 'VARCHAR(10)') AS SEX,
    C.value('@QQ[1]', 'VARCHAR(20)') AS QQ
FROM @x.nodes('/Employees/Employee[@dept="IT"]/Info') AS T(C);

-- 多條件 AND: dept="IT" AND type="合同工"
SELECT ... 
FROM @x.nodes('//Employee[@dept="IT"][@type="合同工"]/*') AS T(C);

3. 動態屬性名稱/值提取 (未知結構)

-- 取得第一個 Employee 的第 3 個屬性名稱與值
SELECT 
    @x.value('local-name((/Employees/Employee[1]/@*)[3])', 'VARCHAR(20)') AS AttrName,
    @x.value('(/Employees/Employee[1]/@*)[3]', 'VARCHAR(20)') AS AttrValue;

-- 透過變數動態指定位置
DECLARE @ElePos INT = 2, @AttPos INT = 4;
SELECT @x.value('local-name((/Employees/Employee[sql:variable("@ElePos")]/@*[position()=sql:variable("@AttPos")])[1])', 'VARCHAR(20)');

4. 判斷屬性是否存在

DECLARE @x XML = '';
DECLARE @AttName VARCHAR(20) = 'QQ';

IF @x.exist('/Employee/@*[local-name()=sql:variable("@AttName")]') = 1
    SELECT 'Exists' AS Result;
ELSE
    SELECT 'Does not exist' AS Result;

四、XML 修改操作 (.modify())

DECLARE @x XML = '土豆男5345454554';

-- 刪除第一個 People 下的 SEX 元素
SET @x.modify('delete (/Peoples/People/SEX)[1]');
SELECT @x;

五、通用動態解析儲存程式：未知結構 XML 轉扁平化表格

實務上常遇到結構不固定的 XML (如日誌、設定檔)，需動態解析為 屬性名 / 屬性值 兩欄格式。下列儲存程式支援指定層級路徑：

CREATE PROCEDURE dbo.usp_ParseXmlDynamic
    @x XML,
    @LayerPath NVARCHAR(MAX) -- 例如: '/*/*/*' 表示第三層所有元素
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @SQL NVARCHAR(MAX) = N'
        SELECT 
            C.value(''local-name(.)'', ''VARCHAR(128)'') AS AttrName,
            C.value(''.'', ''NVARCHAR(MAX)'') AS AttrValue
        FROM @xmlParam.nodes(''' + @LayerPath + ''') AS T(C)';
    
    EXEC sp_executesql @SQL, N'@xmlParam XML', @xmlParam = @x;
END;
GO

-- 使用範例
DECLARE @x XML = 'a@b.com123';
EXEC usp_ParseXmlDynamic @x, '/*/*/*';

六、效能與最佳實踐建議

1. 型別化 XML：若結構固定，建立 XML Schema Collection，使用 XML(schema_name) 型別，查詢效能大幅提升且自動驗證結構。
2. 避免 // 雙斜線：//Element 會掃描整棵樹，明確路徑 /Root/Child/Element 效能好得多。
3. 善用 .exist() 過濾：在 .nodes() 前先用 .exist() 在 WHERE 條件中過濾掉不符合的資料列，減少拆解開銷。
4. 大量資料改用 CLR：極複雜的 XML 轉換邏輯，考慮撰寫 CLR 函數 (C#)，效能遠超 T-SQL XQuery。

總結

SQL Server 的 XML 支援已臻成熟，掌握 .value() 取值、.nodes() 拆列、.modify() 改寫三大核心，配合 XQuery 路徑表達式，幾乎能應對所有 XML 處理場景。本文提供的通用解析 SP 更能應付「結構不定」的實務挑戰，建議收藏備用。