萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL Server 資料匯出 XML/JSON 實戰:BCP + FOR XML PATH / 存儲程序轉 JSON

一次性將資料導出為 XML/JSON 供異構系統消費,SSIS 過重、Import/Export Wizard Bug 多。本文示範 BCP + FOR XML PATH 原生導出 XML,與 自訂存儲程序 + BCP 導出 JSON,無需外部工具、命令列一鍵完成。

導出 XML:BCP + FOR XML PATH

資料準備

SELECT TOP 30 bom_no, [LEVEL] FROM sqladmin.dbo.bom;

來源資料

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

參數解析

參數含義
FOR XML PATH每行生成 元素
TYPE回傳 XML 型別(避免截斷/編碼)
ELEMENTS欄位輸出為子元素而非屬性
ROOT('RegionSales')包裹根元素
QUERYOUT查詢結果導出檔案
-c字元模式(非原生)
-t欄位分隔符(此處無實際分隔,XML 結構自描述)
-T信任連線 (Windows 驗證)
-S localhost目標伺服器

輸出結果

BCP 執行畫面

生成 XML 內容

<RegionSales>
  <row>
    <bom_no>BOM001</bom_no>
    <LEVEL>1</LEVEL>
  </row>
  ...
</RegionSales>

導出 JSON:自訂存儲程序 + BCP

SQL Server 2016 前無原生 JSON 支援(2016+ 有 FOR JSON),舊版可用社群存儲程序 usp_ConvertQueryToJSON(來源:jaminquimby.com)。

部署存儲程序

在目標資料庫執行該文章提供的 CREATE PROCEDURE usp_ConvertQueryToJSON 腳本。

BCP 呼叫存儲程序導出

BCP "EXEC sqladmin.dbo.usp_ConvertQueryToJSON 
    @Query = 'SELECT TOP 30 bom_no, [LEVEL] FROM sqladmin.dbo.bom',
    @RootName = 'RegionSales'" 
QUERYOUT "D:\temp\test.json" -c -T -S localhost

輸出結果

BCP JSON 執行

生成 JSON 內容

{
  "RegionSales": [
    {"bom_no": "BOM001", "LEVEL": 1},
    {"bom_no": "BOM002", "LEVEL": 2}
  ]
}

SQL Server 2016+ 原生 FOR JSON(推薦)

-- AUTO 模式:巢狀依據 JOIN 關係
SELECT TOP 30 bom_no, [LEVEL] 
FROM sqladmin.dbo.bom 
FOR JSON AUTO, ROOT('RegionSales');

-- PATH 模式:完全控制結構
SELECT TOP 30 
    bom_no AS 'Item.BOMNo',
    [LEVEL] AS 'Item.Level'
FROM sqladmin.dbo.bom 
FOR JSON PATH, ROOT('RegionSales');

可直接搭配 BCP:

BCP "SELECT ... FOR JSON PATH, ROOT('RegionSales')" 
QUERYOUT "D:\temp\test.json" -c -T -S localhost

大量資料導出優化建議

  • 分批導出:WHERE id BETWEEN @start AND @end 循環 BCP,避免單一巨型檔案
  • 並行導出:多執行緒 BCP 不同分區/範圍,合併檔案
  • 壓縮傳輸:gzip 壓縮 XML/JSON 再傳輸
  • 編碼一致:-c 字元模式預設代碼頁,跨語系建議 -w (Unicode) 或指定 -C 65001 (UTF-8)

BCP 是 SQL Server 「瑞士刀」:高效能、無依賴、可腳本化。配合 FOR XML/JSON,能在幾分鐘內完成 TB 級資料的結構化導出,是資料整合、異構遷移、API 餵數的最佳輕量方案。

copyright © 萬盛學電腦網 all rights reserved