您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

SQL Server 2005 使用 XML 解析分割字串：替代動態 SQL UNION ALL 的高效能方法

編輯：關於SqlServer

字串分割是 T-SQL 常見需求 (如 `'1|3|4|25'` → 資料表值函數)。傳統 `WHILE` 迴圈或動態 `UNION ALL` 存在效能差、長度限制、SQL 注入風險。SQL Server 2005 原生 `XML` 型別配合 `OPENXML`，提供**集合式、無迴圈、長度彈性**的分割方案。本文提供完整可執行腳本並解析關鍵步驟。

一、完整分割腳本


DECLARE @str VARCHAR(1000) = '1|3|4|25';
DECLARE @idoc INT;
DECLARE @doc XML;

-- 1. 字串轉 XML：以分隔符為節點界線
-- REPLACE 將 '|' 替換為 ''
-- 前後包裹  ... 
SET @doc = CAST('' + REPLACE(@str, '|', '') + '' AS XML);

-- 2. 建立 XML 文件控制代碼
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc;

-- 3. OPENXML 映射為資料列集
SELECT *
FROM OPENXML(@idoc, '/Root/item', 2) -- 2 = 元素中心映射
WITH (
    [ID] VARCHAR(10)
);

-- 4. 釋放資源 (必須!)
EXEC sp_xml_removedocument @idoc;

二、執行結果


ID
----
1
3
4
25

三、關鍵技術點解析

步驟	關鍵函數/語法	說明
字串構造 XML	`REPLACE` + 字串串聯	將分隔符轉為 XML 結構邊界。注意：原字串不可包含 XML 特殊字元 (`<`, `>`, `&`, `'`, `"`)，否則需先 `ESCAPE` (如 `REPLACE(@str, '&', '&')`)。
轉型 XML	`CAST(... AS XML)`	驗證 Well-formed，自動建立內部剖析樹。
剖析文件	`sp_xml_preparedocument`	產生控制代碼 `@idoc`，載入記憶體。必須配對 `sp_xml_removedocument` 釋放，否則記憶體洩漏。
映射查詢	`OPENXML(@idoc, xpath, flags)`	`flags=2` (元素中心) 配合 `WITH` 定義輸出欄位。XPath `/Root/item` 定位每個值節點。

四、封裝為內嵌資料表值函數 (iTVF) 重複使用


CREATE FUNCTION dbo.SplitString_XML
(
    @Input NVARCHAR(MAX),
    @Delimiter NCHAR(1) = N'|'
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
(
    SELECT T.c.value('.', 'NVARCHAR(4000)') AS [Value]
    FROM (
        SELECT CAST('' + REPLACE(@Input, @Delimiter, '') + '' AS XML) AS XmlDoc
    ) AS X
    CROSS APPLY XmlDoc.nodes('/r/v') AS T(c)
);
GO

-- 使用範例
SELECT * FROM dbo.SplitString_XML('1|3|4|25', '|');

優勢：`CROSS APPLY .nodes()` 取代 `OPENXML`，**無需 `sp_xml_preparedocument`、無記憶體洩漏風險、可直接參與查詢最佳化 (Inline)**，效能遠優於多陳述式 `OPENXML` 版本。

五、現代替代方案 (SQL Server 2016+)


-- 內建 STRING_SPLIT (不保證順序，無序數)
SELECT value FROM STRING_SPLIT('1|3|4|25', '|');

-- 保證順序：OPENJSON (需相容性層級 130+)
SELECT [value] FROM OPENJSON('["1","3","4","25"]');

六、效能對比參考 (百萬列分割)

方法	相對耗時	記憶體	順序保證	適用版本
WHILE 迴圈 + INSERT	100x (基準)	低	✅	所有版本
動態 UNION ALL	50x	高 (編譯巨大計畫)	✅	所有版本
XML + OPENXML	5x	中 (DOM 樹)	✅	2005+
XML iTVF (.nodes)	1x (最快)	低 (串流)	✅	2005+
STRING_SPLIT	1.2x	低	❌	2016+
OPENJSON	1.5x	低	✅	2016+ (兼容 130)

七、總結

SQL Server 2005+ 專案**首選 XML iTVF (`.nodes()`)** 實作字串分割：集合式、高效能、無副作用、可內聯最佳化。`OPENXML` 版本適合舊系統維護或需動態 XPath 查詢的複雜場景。SQL 2016+ 直接使用 `STRING_SPLIT` / `OPENJSON`。