超大XML批量导入SQL单元格无长度截断的实现方法

你选的两个技术方向本身没有问题，截断和报错都是参数配置错误导致的：

• OPENROWSET方案截断：核心问题是使用了SINGLE_CLOB参数读取文件，该参数按非Unicode编码读取内容，遇到Unicode文件的0x00标识字节、或者大文件代码页转换时会自动截断内容，和XML类型的存储上限无关——SQL Server原生XML类型最大支持2GB单值存储，足够覆盖绝大多数XML文件场景。
• BCP方案报错：你写的命令没有指定非换行的行终止符，BCP默认会把XML内容里的换行符当成分隔符，把完整XML拆成多行导入，和目标表单行字段的结构不匹配，必然执行失败。

优先用修正版OPENROWSET方案，不需要额外开启组件、配置简单，支持单文件/批量导入，只要单个XML文件不超过2GB就不会截断。

单文件导入代码 #

首先建持久化存储表（不推荐用表变量存超大XML，内存占用过高容易触发异常）：

CREATE TABLE dbo.XMLStaging
(
    FileID INT IDENTITY(1,1) PRIMARY KEY,
    FileName NVARCHAR(255) NOT NULL,
    XMLData XML NOT NULL,
    LoadedTime DATETIME2 NOT NULL DEFAULT GETDATE()
)
GO

导入时把SINGLE_CLOB替换为SINGLE_BLOB，直接将文件二进制流转换为XML类型，全程不做编码转换，不会出现截断：

INSERT INTO dbo.XMLStaging (FileName, XMLData)
SELECT
    'PP015.xml' AS FileName,
    CONVERT(XML, BulkColumn) AS XMLData
FROM OPENROWSET(BULK 'C:\temp\PP015.xml', SINGLE_BLOB) AS x;

批量导入目录下所有XML文件 #

遍历指定目录下的所有xml后缀文件，循环执行导入逻辑即可：

DECLARE @TargetDir NVARCHAR(260) = 'C:\temp\'
DECLARE @CurrentFile NVARCHAR(255)
DECLARE @BulkCmd NVARCHAR(MAX)

-- 临时表存储目录文件清单
CREATE TABLE #FileList
(
    FileName NVARCHAR(255),
    Depth INT,
    IsFile BIT
)
INSERT INTO #FileList
EXEC master.sys.xp_dirtree @TargetDir, 1, 1

-- 遍历所有XML文件导入
DECLARE file_cursor CURSOR FOR
SELECT FileName FROM #FileList WHERE IsFile = 1 AND FileName LIKE '%.xml'
OPEN file_cursor
FETCH NEXT FROM file_cursor INTO @CurrentFile
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @BulkCmd = N'
    INSERT INTO dbo.XMLStaging (FileName, XMLData)
    SELECT @fName, CONVERT(XML, BulkColumn)
    FROM OPENROWSET(BULK ''' + @TargetDir + @CurrentFile + ''', SINGLE_BLOB) AS x;'
    EXEC sys.sp_executesql @BulkCmd, N'@fName NVARCHAR(255)', @fName = @CurrentFile
    FETCH NEXT FROM file_cursor INTO @CurrentFile
END

CLOSE file_cursor
DEALLOCATE file_cursor
DROP TABLE #FileList

注意事项 #

• 执行导入前要给SQL Server服务的运行账号分配XML存放目录的读取权限，否则会报文件访问错误。
• 如果单个XML文件大小超过2GB，超出XML类型的存储上限，无法直接存入字段，需要改用FILESTREAM/FileTable存储原始文件，配合外部程序做流式解析。
• 不推荐用BCP方案导入，需要额外配置格式文件、行终止符，还要开启xp_cmdshell组件，安全风险高、配置复杂度远高于上面的OPENROWSET方案。

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