SQL Server如何将BPAProcess表每行数据单独导出为XML文件

方案1：调整现有SQL脚本解决BCP识别问题 #

你遇到的BCP无法识别临时表的问题，是因为BCP运行时会创建独立的数据库会话，当前会话的局部临时表#Process仅对你当前执行查询的会话可见，BCP的新会话无法访问。
8k字符限制是旧版本BCP针对非Unicode/非大类型的默认限制，你使用-w参数输出XML类型内容时不存在该限制，300-500k的文件可以正常导出。
该方案需要数据库开放xp_cmdshell执行权限，大部分业务生产库不会开放该权限，如果执行报错可直接使用方案2。
调整后的SQL代码如下：

USE [BP6.8.1]
GO
 
DECLARE @fileName VARCHAR(175)
DECLARE @filePath VARCHAR(175)
DECLARE @sqlStr VARCHAR(2000)
DECLARE @sqlCmd VARCHAR(2000)
DECLARE @Iter INT = 1
DECLARE @MaxRowsCount INT

-- 直接统计原表总行数，不需要依赖临时表
SELECT @MaxRowsCount = COUNT(*) FROM BPAProcess

WHILE @Iter <= @MaxRowsCount
BEGIN
    -- 直接从原表取对应行的name，避免临时表依赖
    SELECT @fileName = name FROM (
        SELECT ROW_NUMBER() OVER (ORDER BY processid) row_num, name 
        FROM BPAProcess
    ) t WHERE row_num = @Iter

    SET @filePath = 'C:\Temp\sql queries\' + @fileName + '.xml'
    -- 把行号逻辑直接写到BCP的查询语句内，不需要访问临时表
    SET @sqlStr = 'SELECT processxml FROM (SELECT ROW_NUMBER() OVER (ORDER BY processid) row_num, processxml FROM BP6.8.1.dbo.BPAProcess) t WHERE row_num = ' + CAST(@Iter AS VARCHAR(3))
    
    -- 增加-S参数指定当前服务器实例，避免连接错误
    SET @sqlCmd = 'bcp "' + @sqlStr + '" queryout "' + @filePath + '" -w -T -S ' + @@SERVERNAME

    -- 先确认打印的命令符合预期再取消注释执行
    PRINT @sqlCmd
    -- EXEC xp_cmdshell @sqlCmd

    SET @Iter = @Iter + 1
END

方案2：C# 极简实现（推荐，无需数据库修改权限） #

该方案不需要修改数据库任何配置，也不需要申请特殊权限，适配所有版本的SQL Server，处理大文件更稳定，6个数据库仅需要修改连接字符串重新运行即可。

代码内容 #

using System;
using System.Data.SqlClient;
using System.IO;

class ExportXml
{
    static void Main()
    {
        // 替换为对应数据库的连接字符串
        string connStr = "Server=你的数据库实例地址;Database=BP6.8.1;Trusted_Connection=True;";
        // 替换为XML文件本地保存路径
        string exportPath = @"C:\Temp\sql queries\";
        Directory.CreateDirectory(exportPath);

        using (SqlConnection conn = new SqlConnection(connStr))
        {
            conn.Open();
            SqlCommand cmd = new SqlCommand("SELECT name, processxml FROM BPAProcess", conn);
            using (SqlDataReader reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    // 自动替换name中的非法文件名字符，避免导出报错
                    string safeName = string.Join("_", reader["name"].ToString().Split(Path.GetInvalidFileNameChars()));
                    string fileName = safeName + ".xml";
                    string xmlContent = reader["processxml"].ToString();
                    File.WriteAllText(Path.Combine(exportPath, fileName), xmlContent);
                    Console.WriteLine($"已导出：{fileName}");
                }
            }
        }
        Console.WriteLine("全部导出完成");
        Console.ReadKey();
    }
}

使用方法 #

1. 本地新建文本文档，将上述代码复制进去，重命名为ExportXml.cs
2. 打开cmd进入该文件所在目录，执行csc ExportXml.cs编译生成可执行文件
3. 双击运行生成的ExportXml.exe即可自动导出，换数据库仅需要修改连接字符串后重新运行

内容的提问来源于stack exchange，提问作者Dexter Whelan