萬盛學電腦網

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

SQL Server 資料導出 XML/JSON 實戰:BCP + PowerShell 高效匯出指南

一次性將 SQL Server 資料導出為 XML 或 JSON 供異構系統整合,使用 SSIS 過重、Import/Export Wizard 易出錯。**BCP (Bulk Copy Program) 搭配 FOR XML / 存儲程序生成 JSON** 是最輕量、可自動化的方案。本文提供完整命令列範例與 PowerShell 自動化腳本。

一、導出為 XML:原生 FOR XML 子句

SQL Server 2005+ 內建 FOR XML 將關聯結果集轉為 XML。

測試資料

測試資料

BCP 命令導出 XML 檔案

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 PATHTYPEELEMENTSROOT('RegionSales')-c-t-T-S localhost
參數說明
每列產生 <row> 元素,欄位為子元素
回傳 xml 型別而非字串,保留結構
欄位輸出為元素而非屬性
加上根元素,形成合法 XML 文件
字元模式 (char),而非原生二進位
欄位分隔符 (此處無實際分隔,FOR XML 已成型)
信任連線 (Windows 驗證)
目標伺服器

輸出結果

XML 輸出範例

結構清晰,可直接餵給 XML 解析器或 ETL 工具。

二、導出為 JSON:存儲程序 + BCP 變通方案

SQL Server 2016 前無原生 JSON 輸出 (2016+ 有 FOR JSON)。舊版本可用社群存儲程序 usp_ExportJson (參考 jb51 文章) 產生 JSON 字串,再用 BCP 匯出。

步驟

  1. 部署 usp_ExportJson 至目標資料庫。
  2. BCP 呼叫存儲程序導出:
BCP "EXEC usp_ExportJson 'SELECT TOP 30 bom_no, LEVEL FROM sqladmin.dbo.bom'" QUERYOUT "d:\temp\test.json" -c -T -S localhost

輸出結果

JSON 輸出範例

三、現代化方案:SQL Server 2016+ FOR JSON

**強烈建議升級至 2016+**,原生支援:

-- AUTO 模式 (巢狀依關聯)
SELECT TOP 30 bom_no, LEVEL FROM sqladmin.dbo.bom FOR JSON AUTO, ROOT('RegionSales');

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

搭配 BCP:

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

四、PowerShell 自動化腳本 (生產就緒)

<# 
.SYNOPSIS
    將 SQL 查詢結果導出為 XML/JSON 檔案
.PARAMETER SqlQuery
    含 FOR XML/JSON 子句的 SELECT 語句
.PARAMETER OutputPath
    輸出檔案完整路徑
.PARAMETER ServerInstance
    SQL 伺服器名稱
#>
param(
    [string]$SqlQuery = "SELECT TOP 100 * FROM dbo.Orders FOR JSON PATH, ROOT('Orders')",
    [string]$OutputPath = "C:\Exports\Orders.json",
    [string]$ServerInstance = "localhost"
)

$bcpArgs = @(
    "queryout", $OutputPath,
    "-c", "-T", "-S", $ServerInstance,
    "-q",  -- 引用識別碼
    "-r", "`n"  -- 列結束符
)

# 將查詢寫入暫存檔避免命令列長度/逸出問題
$tempQuery = [IO.Path]::GetTempFileName()
[IO.File]::WriteAllText($tempQuery, $SqlQuery, [Text.Encoding]::UTF8)

try {
    & bcp.exe @"$tempQuery" @bcpArgs
    if ($LASTEXITCODE -ne 0) { throw "BCP 結束代碼 $LASTEXITCODE" }
    Write-Host "✅ 導出成功: $OutputPath"
}
catch {
    Write-Error "❌ 導出失敗: $_"
}
finally {
    Remove-Item -ErrorAction SilentlyContinue $tempQuery
}

五、效能與編碼提示

  • 大量資料:分批導出 (如每 10 萬列一檔),避免單一巨型檔案難以傳輸/解析。
  • 編碼:BCP -c 使用用戶端代碼頁 (預設中文環境 CP950/CP936);需 UTF-8 請用 -w (Unicode) 或 PowerShell [IO.File]::WriteAllText(..., Encoding.UTF8) 後處理。
  • 壓縮:導出後立即 gzip 或 Compress-Archive,節省 80%+ 空間。

BCP + FOR XML/JSON 是 SQL Server 資料導出的「黃金組合」:零依賴、高效能、易自動化、支援超大資料集。

copyright © 萬盛學電腦網 all rights reserved