SQL Server存储过程生成无BOM的UTF-8 XML文件问题求助

原代码的核心问题在于使用ADODB.Stream的Charset='utf-8'时，默认会写入UTF-8 BOM，且代码中存在冗余操作（重复销毁OLE对象、错误调用OpenTextFile）。以下是修正后的方案：

修正思路 #

• 避免依赖ADODB.Stream的字符集自动编码，直接将XML内容转换为无BOM的UTF-8字节数组
• 使用二进制模式写入文件，彻底绕过BOM生成逻辑

修正后的代码 #

DECLARE @OLE INT, @File VARCHAR(300)
DECLARE @Text XML
DECLARE @Converted NVARCHAR(MAX)
DECLARE @UTF8Bytes VARBINARY(MAX)

-- 生成XML内容
SET @Text = (
    SELECT (
        SELECT S1 AS [BuyerOrderNumber]
        FROM Table1 WHERE tday='94218'
        FOR XML PATH('OrderParty'), TYPE
    ) AS 'InvoiceParty'
    FOR XML PATH(''), ROOT('Invoice'), ELEMENTS XSINIL
)

-- 添加XML声明并转换为NVARCHAR
SET @Converted = N'<?xml version="1.0" encoding="utf-8"?>' + CONVERT(NVARCHAR(MAX), @Text)

-- 将NVARCHAR转换为无BOM的UTF-8字节数组
SET @UTF8Bytes = CAST(@Converted AS VARBINARY(MAX))

SET @File = '\\server\company\abc.xml'

-- 初始化ADODB.Stream
EXEC sp_OACreate 'ADODB.Stream', @OLE OUTPUT
EXEC sp_OASetProperty @OLE, 'Type', 1 -- 1 = adTypeBinary
EXEC sp_OASetProperty @OLE, 'Mode', 3 -- 3 = adModeReadWrite
EXEC sp_OAMethod @OLE, 'Open'

-- 写入UTF-8字节数组并保存
EXEC sp_OAMethod @OLE, 'Write', NULL, @UTF8Bytes
EXEC sp_OAMethod @OLE, 'SaveToFile', NULL, @File, 2 -- 2 = adSaveCreateOverWrite

-- 清理资源
EXEC sp_OAMethod @OLE, 'Close'
EXEC sp_OADestroy @OLE

关键说明 #

1. 字节数组转换：CAST(NVARCHAR(MAX) AS VARBINARY(MAX))会将UTF-16的NVARCHAR直接转换为UTF-8字节流，且不会添加BOM
2. 二进制模式写入：设置Type=1（二进制类型）后，Stream会直接写入原始字节，不会额外添加编码标识
3. 移除冗余操作：删除了原代码中错误的OpenTextFile调用，以及重复的sp_OADestroy

执行此存储过程后，生成的XML文件在Notepad++中会显示为UTF-8（无BOM）编码。

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