在 SQL Server 2016 引入 `STRING_SPLIT` 之前,利用 **XML 解析** 是將分隔字串轉為資料列集合的標準高效寫法。本文詳細解析 XML 拆分原理、效能特性、邊界情況處理,並對比現代 `STRING_SPLIT`、`OPENJSON`、CLR、Numbers Table 等方案,助您選擇最適合的拆分策略。
DECLARE @csv VARCHAR(MAX) = '10,20,30,40,50';
DECLARE @xml XML = CAST('- ' + REPLACE(@csv, ',', '
- ') + '
' AS XML);
SELECT
x.value('.', 'VARCHAR(10)') AS Token
FROM @xml.nodes('/root/item') AS t(x);
CREATE FUNCTION dbo.fn_SplitString_XML
(
@Input NVARCHAR(MAX),
@Delimiter NCHAR(1) = N','
)
RETURNS TABLE
WITH SCHEMABINDING
AS RETURN
(
SELECT
ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS ItemNumber,
x.value('.', 'NVARCHAR(MAX)') AS ItemValue
FROM (
SELECT CAST('' + REPLACE(@Input, @Delimiter, '') + '' AS XML) AS XmlData
) d
CROSS APPLY XmlData.nodes('/i') AS t(x)
);
GO
-- 使用範例
SELECT * FROM dbo.fn_SplitString_XML('Apple,Banana,Cherry', ',');
-- 結果:ItemNumber=1, ItemValue='Apple' ...
**優點**:SCHEMABINDING 允許內展、可並行、無 RBAR。**缺點**:XML 剖析 CPU 較高、記憶體佔用大、超大字串 (10 萬+ 項) 可能 OOM。
| 情況 | XML 行為 | 處理建議 |
|---|---|---|
| 空字串 `''` | 產生一列空值 | 外層 `WHERE ItemValue <> ''` 過濾 |
| 連續分隔符 `',,'` | 產生空值列 | 同上,或預先 `REPLACE(@Input, ',,', ',')` |
| 含 XML 特殊字元 `A&B`, ` | CAST 失敗 | 預先 `REPLACE(@Input, '&', '&')` 等實體編碼,或改用其他方法 |
| 超長字串 (> 2 GB) | XML 型別上限 2 GB | 必須分批處理或改用 CLR/STRING_SPLIT |
| 分隔符為多字元 `||` | REPLACE 僅支援單字元 | 先 `REPLACE(@Input, '||', CHAR(1))` 再用單字元分隔 |
-- 測試資料:10 萬個逗號分隔的數字
DECLARE @big CSV = REPLICATE(CAST('1,' AS VARCHAR(MAX)), 100000);
| 方法 | SQL 版本 | CPU 時間 (ms) | 邏輯讀取 | 記憶體授與 | 並行支援 | 備註 |
|------|----------|---------------|----------|------------|----------|------|
| **XML + .nodes()** | 2005+ | ~1,200 | 高 | 高 (XML 文件物件) | ❌ | 通用、舊版相容 |
| **STRING_SPLIT** | 2016+ | ~180 | 低 | 低 | ✅ (2022+) | **無順序保證**、無項次、需相容性 130+ |
| **OPENJSON** | 2016+ | ~220 | 低 | 低 | ✅ | 需轉 JSON 陣列 `['1','2']`、保留順序、可指定型別 |
| **Numbers Table / Tally Table** | 2005+ | ~300 | 中 | 低 | ✅ | 需預建數字表、最穩定、無 XML/JSON 解析開銷 |
| **CLR (C# Split)** | 2005+ | ~90 | 低 | 低 | ✅ | **最快**、需啟用 CLR、部署組件 |
SELECT value, ordinal
FROM STRING_SPLIT('z,a,b', ',', 1) -- 1 = 啟用 ordinal
ORDER BY ordinal;
-- 保證輸出順序與原字串一致
-- 一次建立永久使用
CREATE TABLE dbo.Numbers (n INT NOT NULL PRIMARY KEY);
WITH cte AS (
SELECT 1 AS n UNION ALL SELECT n+1 FROM cte WHERE n < 1000000
)
INSERT INTO dbo.Numbers SELECT n FROM cte OPTION (MAXRECURSION 0);
GO
-- 拆分函數
CREATE FUNCTION dbo.fn_SplitString_Tally
(
@Input NVARCHAR(MAX),
@Delimiter NCHAR(1) = N','
)
RETURNS TABLE
WITH SCHEMABINDING
AS RETURN
(
SELECT
ROW_NUMBER() OVER (ORDER BY n) AS ItemNumber,
SUBSTRING(@Input, n, CHARINDEX(@Delimiter, @Input + @Delimiter, n) - n) AS ItemValue
FROM dbo.Numbers
WHERE n <= LEN(@Input) + 1
AND SUBSTRING(@Delimiter + @Input, n, 1) = @Delimiter
);
GO
XML 拆分是 SQL Server 2005–2014 時代的**經典智慧**,至今仍具通用性。但隨著 `STRING_SPLIT`、`OPENJSON`、Numbers Table、CLR 等方案成熟,**新專案應優先使用原生函數**,僅在舊版相容或特殊字元處理時保留 XML 方案。理解各方法的**效能曲線、順序保證、記憶體模型**,才能在實際負載下做出正確架構決策。