SQL Server 資料匯出 XML 與 JSON：BCP + FOR XML PATH 實作與 JSON 預存程序方案

一次性將查詢結果匯出 XML/JSON 供異構系統整合，無需建立 SSIS 套件。XML 使用原生 FOR XML + BCP；JSON 於 SQL Server 2016 前無原生支援，採用自訂預存程序轉換 XML 為 JSON 再用 BCP 匯出。

一、匯出 XML：原生 FOR XML PATH + BCP

查詢轉 XML

SELECT TOP 30 [bom_no], [LEVEL] 
FROM [sqladmin].[dbo].[bom] 
FOR XML PATH, TYPE, ELEMENTS, ROOT('RegionSales');

• PATH：每列產生 <row> 元素（可自訂名稱）。
• TYPE：回傳 XML 型別而非字串，保留型別資訊。
• ELEMENTS：欄位輸出為子元素而非屬性。
• ROOT('RegionSales')：最外層根元素。

BCP 匯出檔案

BCP "SELECT TOP 30 [bom_no],[LEVEL] FROM [sqladmin].[dbo].[bom] FOR XML PATH, TYPE, ELEMENTS, ROOT('RegionSales')" 
QUERYOUT "d:\temp\test.XML" -c -t -T -S localhost

• -c：字元模式（而非原生二進位）。
• -t：欄位結束符（預設 Tab），此處單一 XML 欄位無影響。
• -T：信任連線（Windows 整合驗證）。
• -S localhost：目標伺服器。

輸出 XML 範例：

二、匯出 JSON：XML 轉 JSON 預存程序 + BCP（SQL Server 2016 前方案）

核心邏輯

1. 查詢 FOR XML RAW, TYPE, ELEMENTS 產生標準 XML。
2. T-SQL 字串剖析 XML：逐 <row>、逐 <field> 擷取 Key/Value，拼接 JSON 字串。
3. BCP 匯出結果字串。

預存程序：SerializeJSON（簡化版）

CREATE PROCEDURE dbo.SerializeJSON @ParameterSQL VARCHAR(MAX) AS
BEGIN
    DECLARE @XML XML, @SQL NVARCHAR(MAX), @ParamList NVARCHAR(1000) = N'@XML XML OUTPUT';
    SET @SQL = N'WITH PrepareTable(XMLString) AS (' + @ParameterSQL + N' FOR XML RAW, TYPE, ELEMENTS) SELECT @XML = XMLString FROM PrepareTable';
    EXEC sp_executesql @SQL, @ParamList, @XML = @XML OUTPUT;

    DECLARE @JSON VARCHAR(MAX) = '', @RowStart INT, @RowEnd INT, @FieldStart INT, @FieldEnd INT;
    DECLARE @Key VARCHAR(MAX), @Value VARCHAR(MAX);
    DECLARE @XMLStr VARCHAR(MAX) = CAST(@XML AS VARCHAR(MAX));
    
    SET @RowStart = CHARINDEX('', @XMLStr);
    WHILE @RowStart > 0 BEGIN
        SET @RowStart += 5; SET @RowEnd = CHARINDEX('', @XMLStr, @RowStart);
        DECLARE @Row VARCHAR(MAX) = SUBSTRING(@XMLStr, @RowStart, @RowEnd - @RowStart);
        SET @JSON += '{';
        SET @FieldStart = CHARINDEX('<', @Row);
        WHILE @FieldStart > 0 BEGIN
            SET @FieldStart += 1; SET @FieldEnd = CHARINDEX('>', @Row, @FieldStart);
            SET @Key = SUBSTRING(@Row, @FieldStart, @FieldEnd - @FieldStart);
            SET @JSON += '"' + @Key + '":';
            SET @FieldStart = @FieldEnd + 1; SET @FieldEnd = CHARINDEX(' 0 SET @JSON = SUBSTRING(@JSON, 1, LEN(@JSON) - 1);
        SET @JSON += '},';
        SET @RowStart = CHARINDEX('', @XMLStr, @RowEnd);
    END
    IF LEN(@JSON) > 0 SET @JSON = SUBSTRING(@JSON, 1, LEN(@JSON) - 1);
    SET @JSON = '[' + @JSON + ']';
    SELECT @JSON;
END;

BCP 呼叫預存程序匯出 JSON

BCP "EXEC dbo.SerializeJSON 'SELECT * FROM Employee_TBL'" 
QUERYOUT "d:\temp\employees.json" -c -T -S localhost

輸出 JSON 範例：

現代替代方案（SQL Server 2016+ / Azure SQL）

• FOR JSON PATH/AUTO：SELECT ... FOR JSON PATH, ROOT('Data') 原生產生 JSON，無需預存程序。
• BCP 直接匯出：BCP "SELECT ... FOR JSON PATH" QUERYOUT file.json ...
• OPENJSON：反向剖析 JSON 進資料表。

總結

XML 匯出採用原生 FOR XML + BCP 最穩健；JSON 在舊版本以 XML 轉換預存程序過渡，新版本請直接使用 FOR JSON 內建功能。