SQL Server 自訂函數：XML 變數轉 JSON 字串

需求背景

在 T-SQL 中直接將 XML 型別資料轉換為 JSON 格式字串，方便前端消費或跨系統傳遞。SQL Server 2016+ 內建 FOR JSON，但舊版本或特定場景下，自訂純 T-SQL 函數仍具參考價值。

函數實作

CREATE FUNCTION [dbo].[fnXmlToJson] (@XmlData XML)
RETURNS NVARCHAR(MAX)
AS
BEGIN
    RETURN (
        SELECT STUFF((
            SELECT *
            FROM (
                SELECT ',{' + STUFF((
                    SELECT ',"' + COALESCE(b.c.value('local-name(.)', 'NVARCHAR(MAX)'), '') + '":"' + b.c.value('text()[1]', 'NVARCHAR(MAX)') + '"'
                    FROM x.a.nodes('*') b(c)
                    FOR XML PATH(''), TYPE
                ).value('(./text())[1]', 'NVARCHAR(MAX)'), 1, 1, '') + '}'
                FROM @XmlData.nodes('/root/*') x(a)
            ) JSON(theLine)
            FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
    );
END;
GO

測試資料與驗證

-- 建立測試表
CREATE TABLE [dbo].[PivotExample] (
    [Country] NVARCHAR(50) NULL,
    [Year] SMALLINT NOT NULL,
    [SalesAmount] MONEY NULL
);

INSERT INTO [dbo].[PivotExample] VALUES ('Australia', 2007, 1000.00);
INSERT INTO [dbo].[PivotExample] VALUES ('Germany', 2007, 2000.00);
INSERT INTO [dbo].[PivotExample] VALUES ('United States', 2007, 3000.00);
INSERT INTO [dbo].[PivotExample] VALUES ('France', 2008, 922179.04);

-- 轉 XML
DECLARE @xml XML;
SET @xml = (SELECT TOP 100 PERCENT * FROM [dbo].[PivotExample] FOR XML PATH, ROOT);

-- 呼叫函數
SELECT dbo.fnXmlToJson(@xml);

輸出範例

{"Country":"Australia","Year":"2007","SalesAmount":"1000.00"}, {"Country":"Germany","Year":"2007","SalesAmount":"2000.00"}, {"Country":"United States","Year":"2007","SalesAmount":"3000.00"}, {"Country":"France","Year":"2008","SalesAmount":"922179.04"}

原理解析

1. @XmlData.nodes('/root/*') 將每個子節點拆為單列。
2. 內層 nodes('*') 進一步拆解屬性/子元素，取 local-name() 為 Key，text() 為 Value。
3. FOR XML PATH('') 串聯成 ,"Key":"Value" 片段。
4. STUFF(...,1,1,'') 移除開頭逗號，包裹成 {...}。
5. 外層再次 FOR XML PATH('') 將所有物件以逗號連接，最終 STUFF 去除首逗號。

適用場景與限制

• 適用於 SQL Server 2008~2014 等無 FOR JSON 支援的版本。
• 僅處理單層結構；巢狀 XML 需遞迴擴展。
• 大量資料時效能不如原生 FOR JSON，建議 2016+ 直接使用 SELECT * FROM table FOR JSON AUTO。