如何将SQL Server通过xp_cmdshell生成的XML头部改为UTF-8编码声明

I’ve run into this exact issue before—getting that barebones <?xml version="1.0"?> header when generating XML via SQL Server’s xp_cmdshell is super common, but there are a few straightforward ways to fix it. Here are the methods I rely on:

Method 1: Post-Generation File Edit with PowerShell #

If you’re already generating the XML file and just need to tweak the header, use PowerShell to replace the first line. This works well when you can’t modify the initial XML generation command:

-- Assume your generated XML file is at C:\temp\output.xml
EXEC xp_cmdshell 'powershell -Command "(Get-Content C:\temp\output.xml) -replace ''^<\?xml version=""1.0""\?>'', ''<?xml version=""1.0"" encoding=""UTF-8""?>'' | Set-Content C:\temp\output.xml -Encoding UTF8"'

Breakdown: #

• Get-Content reads the existing XML file
• The -replace regex targets the exact default header and swaps it with the UTF-8 version
• Set-Content -Encoding UTF8 ensures the file itself is saved with UTF-8 encoding (not just the header saying it is)

Note: You’ll need to escape quotes properly in the SQL string—double quotes inside the PowerShell command become "" in SQL.

Method 2: Generate XML with Encoding Directly via BCP #

If you’re using xp_cmdshell to run bcp for XML exports, you can specify UTF-8 encoding directly in the bcp command, which will automatically add the correct header:

DECLARE @bcpCmd NVARCHAR(4000)
SET @bcpCmd = 'bcp "SELECT * FROM YourTable FOR XML PATH(''Item''), ROOT(''Dataset'')" queryout "C:\temp\output.xml" ' +
              '-S ' + @@SERVERNAME + ' -d YourDatabase -T -w -C 65001'

EXEC xp_cmdshell @bcpCmd

Key Flags: #

• -w: Uses Unicode characters (required for proper UTF-8 handling)
• -C 65001: Explicitly sets the encoding to UTF-8 (65001 is the code page for UTF-8)
• -T: Uses trusted authentication (adjust to -U/-P if using SQL auth)

This method is cleaner because it generates the correct header and encoding in one step, no post-processing needed.

Method 3: Manually Construct the XML Header #

If you’re building the XML content directly in SQL (e.g., concatenating with FOR XML), you can prepend the UTF-8 header before writing the file:

DECLARE @xmlContent NVARCHAR(MAX)

-- Build XML with custom header
SET @xmlContent = '<?xml version="1.0" encoding="UTF-8"?>' + 
                  (SELECT Column1, Column2 FROM YourTable FOR XML PATH('Row'), ROOT('Data'))

-- Write to file using PowerShell (avoids echo character limits)
EXEC xp_cmdshell 'powershell -Command "Set-Content -Path ''C:\temp\output.xml'' -Value ''' + REPLACE(@xmlContent, '''', '''''') + ''' -Encoding UTF8"'

Important Note: #

Use REPLACE(@xmlContent, '''', '''''') to escape any single quotes in the XML content—this prevents syntax errors when passing the string to PowerShell.

Critical Prerequisites #

• Ensure xp_cmdshell is enabled (you can check via sp_configure 'xp_cmdshell', 1; RECONFIGURE;)
• The SQL Server service account must have read/write permissions to the target file path
• PowerShell execution policy may need to be set to allow script execution (e.g., Set-ExecutionPolicy RemoteSigned)

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