SQL Server 依查詢結果產生 XML 檔案：bcp 與預存程序完整實作指南

將 SQL Server 查詢結果匯出為 XML 檔案是常見的系統整合需求。本文提供兩種主流做法：命令列工具 bcp 快速匯出，與 T-SQL 預存程序動態產生結構化 XML，並解決 bcp 環境變數找不到的常見問題。

前置作業：修復「bcp 不是內部命令」錯誤

執行 bcp 若提示找不到命令，請檢查路徑 C:\Program Files\Microsoft SQL Server\80\Tools\Binn\bcp.exe 是否存在，並將該目錄加入系統環境變數 Path：

1. 右鍵「我的電腦」→「屬性」→「進階」→「環境變數」
2. 在「系統變數」找到 Path → 「編輯」
3. 在變數值末尾加入 ;C:\Program Files\Microsoft SQL Server\80\Tools\BINN

方法一：使用 bcp 命令列快速匯出

bcp "SELECT * FROM MyTable FOR XML AUTO, ELEMENTS" queryout "D:\Export.xml" -c -T -Slocalhost

• FOR XML AUTO, ELEMENTS：將結果轉為 XML 元素格式
• -c：字元模式（UTF-8）
• -T：信任連線（Windows 驗證）

方法二：預存程序動態產生多表 XML（完整碼）

適用於需依參數決定匯出哪些表、自訂 XML 結構、編碼為 Big5/GB2312 的進階情境。

CREATE PROC P_ZehuaSaveXML
    @TableCodeS VARCHAR(8000),      -- 逗點分隔的表代碼清單
    @fname VARCHAR(1000) = 'c:\tmp.xml'  -- 輸出路徑
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @sql NVARCHAR(4000), @sql1 VARCHAR(400);
    DECLARE @cnt INT, @Table_Name VARCHAR(20);
    DECLARE @i INT, @TableCode VARCHAR(20);
    DECLARE @err INT, @src VARCHAR(255), @desc VARCHAR(255);

    -- 建立暫存表組裝 XML 內容
    CREATE TABLE ##t (re NVARCHAR(4000));
    INSERT ##t SELECT Re = '';
    INSERT ##t SELECT '';

    -- 解析傳入的多表代碼
    SET @i = CHARINDEX(',', @TableCodeS);
    WHILE @i > 0
    BEGIN
        SET @TableCode = LEFT(@TableCodeS, @i - 1);

        -- 動態查詢該表的主鍵資訊
        SET @sql = N'SELECT @cnt=COUNT(*), @Table_Name=MAX(Table_Name) FROM ('
                 + N'SELECT TOP 100 PERCENT * FROM TB_TABLECOLUMN_T WHERE '
                 + N'TABLE_CODE=''' + @TableCode + ''' AND COLUMNS_ISKEY=''√'' '
                 + N'ORDER BY COLUMNS_SORT ) A';

        EXEC sp_executesql @sql,
            N'@cnt INT OUTPUT, @Table_Name VARCHAR(20) OUTPUT',
            @cnt OUTPUT, @Table_Name OUTPUT;

        -- 此處續接組裝該表的 XML 結構...
        -- (原文程式碼截斷，實際專案需補全欄位迴圈與資料列序列化邏輯)

        SET @TableCodeS = SUBSTRING(@TableCodeS, @i + 1, LEN(@TableCodeS));
        SET @i = CHARINDEX(',', @TableCodeS);
    END

    -- 處理最後一個表代碼 (同樣邏輯)

    -- 寫入檔案：利用 bcp 將 ##t 內容匯出
    SET @sql1 = 'bcp "SELECT re FROM ##t" queryout "' + @fname + '" -c -C 936 -T -S' + @@SERVERNAME;
    EXEC master..xp_cmdshell @sql1;

    DROP TABLE ##t;
END;
GO

呼叫範例

EXEC P_ZehuaSaveXML 'WQ_STINFO_B,WQ_PHY_D', 'D:\export.xml';

關鍵技術點補充

• 編碼控制：bcp 參數 -C 936 指定繁體中文代碼頁（簡體用 936），避免中文亂碼。
• 大量資料效能：百萬列以上建議改用 FOR XML PATH 配合串流寫入，避免暫存表撐爆 tempdb。
• 權限需求：執行 xp_cmdshell 需 sysadmin 或授與 proxy account；雲端資料庫（Azure SQL）不支援，需改用應用層匯出。

總結

簡單匯出用 bcp + FOR XML 最快；複雜結構、多表關聯、自訂編碼則以預存程序動態組裝再 bcp 落地最靈活。依專案規模與維護考量擇一採用。