使用BCP从SQL Server生成XML文件异常：21M记录生成空文件

首先直接回答你的第一个问题：SQL Server处理XML对象确实存在容量上限，这正是你遇到空文件问题的核心原因。

为什么2100万条记录会生成空文件？ #

你的代码是把整个视图的XML结果一次性转换为VARCHAR(MAX)后导出，但VARCHAR(MAX)的最大存储容量是2GB。当2100万条记录生成的XML超过这个阈值时，SQL Server无法完成转换操作，最终导致BCP输出空文件。而400万条记录的XML大小还在2GB以内，所以能正常运行。

另外，一次性生成如此庞大的XML对象会消耗大量内存，容易触发SQL Server的内存压力，进一步加剧操作失败的概率。

无需拆分多文件的解决办法 #

核心思路是避免一次性生成整个XML文档，而是逐行导出每个节点的XML片段，再手动补充XML声明和根节点，这样既绕开了VARCHAR(MAX)的容量限制，也降低了内存消耗。

方法1：BCP逐行导出+外部工具合并文件

• 先导出所有Customer节点的XML片段：

DECLARE @bcpCommand VARCHAR(8000), @FileName VARCHAR(100) = 'Customer_20190830.xml';
SET @bcpCommand = 'bcp "SELECT (SELECT * FROM dbo.vw_Customers c WHERE c.CustomerID = t.CustomerID FOR XML PATH(''Customer''), TYPE).value(''.'', ''VARCHAR(MAX)'') FROM dbo.vw_Customers t;" queryout E:\Share\Customer\Temp_' + @FileName + ' -S -d Test-T -c -b 10000';
PRINT @bcpCommand;
EXEC master..xp_cmdshell @bcpCommand;

这里的-b 10000参数设置批量导出10000行，可以减少内存占用，提升导出效率。

• 用DOS命令补充XML结构并合并文件（可以直接通过xp_cmdshell执行）：

-- 创建XML声明和根节点开头文件
EXEC master..xp_cmdshell 'echo ^<?xml version="1.0"?^>^<Customers^> > E:\Share\Customer\Header.txt';
-- 创建根节点结尾文件
EXEC master..xp_cmdshell 'echo ^</Customers^> > E:\Share\Customer\Footer.txt';
-- 合并文件
EXEC master..xp_cmdshell 'copy /b E:\Share\Customer\Header.txt + E:\Share\Customer\Temp_' + @FileName + ' + E:\Share\Customer\Footer.txt E:\Share\Customer\' + @FileName;
-- 清理临时文件
EXEC master..xp_cmdshell 'del E:\Share\Customer\Header.txt E:\Share\Customer\Footer.txt E:\Share\Customer\Temp_' + @FileName;

方法2：使用OPENROWSET流式写入XML（更优雅）

这种方式不需要临时文件，直接在目标文件中逐行追加内容：

• 写入XML声明和根节点开头：

DECLARE @FullFileName VARCHAR(200) = 'E:\Share\Customer\Customer_20190830.xml';
EXEC master..xp_cmdshell 'echo ^<?xml version="1.0"?^>^<Customers^> > ' + @FullFileName;

• 逐行写入每个Customer节点：

INSERT INTO OPENROWSET(BULK @FullFileName, SINGLE_BLOB)
SELECT (SELECT * FROM dbo.vw_Customers c WHERE c.CustomerID = t.CustomerID FOR XML PATH(''Customer''), TYPE)
FROM dbo.vw_Customers t;

• 写入根节点结尾：

EXEC master..xp_cmdshell 'echo ^</Customers^> >> ' + @FullFileName;

这种方式利用OPENROWSET的批量写入能力，每次只处理一条记录的XML，完全避开了大对象内存限制的问题。

额外注意事项 #

• 确保SQL Server服务账户拥有目标目录的读写权限，否则BCP或OPENROWSET会因权限不足失败。
• 使用FOR XML ... TYPE生成的XML会自动转义特殊字符（如&、<、>），比直接拼接字符串更安全，避免XML格式错误。
• 如果视图中有大字段（如TEXT、VARCHAR(MAX)），建议单独处理这些字段，避免单个节点的XML过大。

内容的提问来源于stack exchange，提问作者Jay Desai