SQL Server 資料匯出為 XML 與 JSON 完整指南：BCP、FOR XML、自訂預存程序

為什麼不直接用 SSIS / 匯入匯出精靈？

• SSIS 套件開發、部署、維護成本高，一次性匯出「殺雞用牛刀」。
• 匯入匯出精靈 Bug 多、介面操作慢、難以自動化腳本化。
• BCP (Bulk Copy Program)：命令列工具、極速、支援腳本自動化、無額外元件。

一、匯出為 XML

1. T-SQL：FOR XML 子句 (SQL Server 2005+ 原生支援)

-- 基本語法
SELECT TOP 30 BomNo, [Level]
FROM dbo.Bom
FOR XML PATH('Item'), TYPE, ELEMENTS, ROOT('RegionSales');

-- 關鍵選項說明
-- PATH('Item')        : 每列包裹在  元素
-- TYPE                : 回傳 xml 型別 (而非 nvarchar)，保留型別資訊
-- ELEMENTS            : 欄位輸出為子元素 (預設為屬性)
-- ROOT('RegionSales') : 最外層根元素

輸出範例：

  
    BOM-001
    1
  
  
    BOM-002
    2
  
  ...

2. BCP 命令列匯出 XML 檔案

BCP "SELECT TOP 30 [BomNo],[Level] FROM [sqladmin].[dbo].[Bom] FOR XML PATH('Item'), TYPE, ELEMENTS, ROOT('RegionSales')" QUERYOUT "D:\Temp\Test.XML" -c -T -S localhost

參數說明：

3. 常見問題：中文亂碼

將 -c 改為 -w (Unicode 模式) 即可解決：

BCP "..." QUERYOUT "D:\Temp\Test.XML" -w -T -S localhost

二、匯出為 JSON

SQL Server 2016 以前**無原生 JSON 匯出**，2016+ 支援 FOR JSON。本文提供 2016 前版本通用方案：自訂預存程序 + BCP。

方案：建立通用 JSON 轉換預存程序 (參考 Jamin Quimby 作法)

-- 簡化版核心邏輯：利用 FOR XML PATH 產生屬性式 XML，再以 XQuery 轉 JSON
-- 完整程式碼請參考：https://jaminquimby.com/servers/95-sql/sql-2008/145-code-tsql-convert-query-to-json
CREATE PROCEDURE util_ConvertQueryToJSON
    @Query NVARCHAR(MAX),
    @RootName NVARCHAR(100) = 'Root',
    @ItemName NVARCHAR(100) = 'Item'
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @XML XML;
    SET @Query = @Query + ' FOR XML PATH(''' + @ItemName + '''), TYPE, ELEMENTS, ROOT(''' + @RootName + ''')';
    EXEC sp_executesql @Query; -- 這裡需進一步 XQuery 轉換，完整邏輯較長，建議部署現成方案
END

BCP 呼叫預存程序匯出 JSON

BCP "EXEC util_ConvertQueryToJSON @Query='SELECT TOP 10 * FROM dbo.Products'" QUERYOUT "D:\Temp\Products.JSON" -w -T -S localhost

三、SQL Server 2016+ 原生 FOR JSON (強烈建議升級後使用)

-- AUTO 模式：自動依 JOIN 關聯產生巢狀結構
SELECT TOP 10
    P.ProductID, P.Name, P.Color,
    SOD.UnitPrice, SOD.OrderQty
FROM Production.Product P
JOIN Sales.SalesOrderDetail SOD ON P.ProductID = SOD.ProductID
FOR JSON AUTO, ROOT('Products');

-- PATH 模式：完全控制結構 (類似 FOR XML PATH)
SELECT
    ProductID AS 'Product.Id',
    Name AS 'Product.Name',
    Color AS 'Product.Color'
FROM Production.Product
FOR JSON PATH, ROOT('Products');

輸出範例 (PATH)：

{
  "Products": [
    { "Product": { "Id": 1, "Name": "Handlebar", "Color": "Black" } },
    { "Product": { "Id": 2, "Name": "Chain", "Color": "Silver" } }
  ]
}

四、BCP 進階技巧總結

總結

• XML 匯出：FOR XML PATH/TYPE/ELEMENTS/ROOT + BCP QUERYOUT -w，全版本通用、標準化。
• JSON 匯出：
  • SQL 2016+：直接用 FOR JSON AUTO/PATH + BCP，最簡單、效能最好。
  • SQL 2014 及以前：部署自訂預存程序轉換 + BCP，一次設定、長期受用。
• BCP 是**最輕量、最高效、最易自動化**的匯出利器，DBA 必備技能。