Write to file with xp_cmdshell in UTF-8

Question

I am creating files with xp_cmdshell like this:

SELECT @command = 'echo ' + @fileContent + ' > e:\out\' + @fileName + '.csv'
exec master.dbo.xp_cmdshell 'mkdir "e:\out\"'
exec master..xp_cmdshell @command 

The problem is that the file contents is not in UTF-8 and so some special characters are wrong.

Can i create the file in UTF-8 encoding?

Answer 1

I finally succeeded doing this by writing the output to a temp.txt file and adding the next PowerShell command to convert it to UTF-8:

-- Change encoding to UTF-8 with PowerShell 
SET @command = 'powershell -Command "Get-Content '+@Path+'\temp.txt -Encoding Unicode | Set-Content -Encoding UTF8 '+@path+'\'+@filename+'"';
EXEC xp_cmdshell @command;