SQL-创建XML-如何设置Unicode UTF-8 [英] SQL - Create XML - How to set Unicode UTF-8

问题描述

我在从SQL PROCEDURE内部创建XML文件时遇到问题. XML被创建并放置在正确的文件夹中.虽然当我在XMLSpy中打开文件时，它显示以下错误:

I have a problem with creating an XML file from inside a SQL PROCEDURE. The XMLs get created and placed in the correct folder. Although when I open the file in XMLSpy, it says the following error:

您的文件包含3个字符，使用Unicode UTF-8编码不应出现在文件中... 令人讨厌的字符是è(0xE8)，ü(0xFC)，é(0xE9)

Your file contains 3 character(s) that should not be present in a file using the Unicode UTF-8 encoding... The offending characters are è (0xE8), ü (0xFC), é (0xE9)

当我在Notepad ++中打开XML文件并检查编码(通过顶部的菜单编码")时，它说它是ANSI而不是UTF-8.

When I open the XML file in Notepad++ and check for encoding (via menu Encoding on top), it says it is in ANSI and not in UTF-8.

所以我的问题是:如何将其转换为UTF-8?有什么建议? 我的过程的代码可以在下面找到:

So my question is: How do I get it to turn into UTF-8? Any suggestions? The code of my procedure can be found below:

@File        VARCHAR(2000) // parameter
AS 

BEGIN 
DECLARE @OLE            INT 
DECLARE @FileID         INT 

EXECUTE sp_OACreate 'Scripting.FileSystemObject', @OLE OUT 

EXECUTE sp_OAMethod @OLE, 'OpenTextFile', @FileID OUT, @File, 8, 1 

DECLARE @Text XML
SET @Text = (SELECT TOP(1) [xml] from VW_WARP_LEVERANCIERS_XML)     

EXECUTE sp_OAMethod @FileID, 'WriteLine', Null, @Text

EXECUTE sp_OADestroy @FileID 
EXECUTE sp_OADestroy @OLE 

END

编辑1

更改了我的代码，导出为ASCII而不是Unicode(Wolf在下面建议)，但这也无济于事.但是这是代码的一部分:

Changed my code, exporting to ASCII instead of Unicode (as suggested below by Wolf), but this didn't help too. But here is that part of code:

DECLARE @OLE            INT 
DECLARE @FileID         INT

EXECUTE sp_OACreate 'Scripting.FileSystemObject', @OLE OUT 

EXECUTE sp_OAMethod @OLE, 'CreateTextFile', @FileID  OUT, @File, 2, False

DECLARE @Text XML
SET @Text = (SELECT TOP(1) [xml] from VW_WARP_LEVERANCIERS_XML)     

EXECUTE sp_OAMethod  @FileID , 'Write', Null, @Text
EXECUTE sp_OAMethod  @FileID , 'Close'


EXECUTE sp_OADestroy @FileID 
EXECUTE sp_OADestroy @OLE 

END 

推荐答案

我通过另一种方法找到了解决方案. (不记得我找到哪个网站了) 请参阅下面的代码.此代码将以UTF-8-BOM编码导出我的文件.

I found a solution via another way. (don't remember which website I found it) See code below. This code will export my file in UTF-8-BOM encoding.

@File        VARCHAR(2000)
AS 

BEGIN 

DECLARE @OLE            INT 

EXECUTE sp_OACreate 'ADODB.Stream',  @OLE OUTPUT

DECLARE @Text XML
SET @Text = (SELECT TOP(1) [xml] from VW_WARP_LEVERANCIERS_XML)     
DECLARE @Converted NVARCHAR(MAX)
SET @Converted = CONVERT(nvarchar(MAX), @Text)

EXECUTE sp_OASetProperty             @OLE,    'Type',             2                           --1 = binary, 2 = text
EXECUTE sp_OASetProperty             @OLE,    'Mode',             3                           --0 = not set, 1 read, 2 write, 3 read/write
EXECUTE sp_OASetProperty             @OLE,    'Charset',          'UTF-8'                     --'ISO-8859-1'
EXECUTE sp_OASetProperty             @OLE,    'LineSeparator',    'adLF'
EXECUTE sp_OAMethod                  @OLE,    'Open'  
EXECUTE sp_OAMethod                  @OLE,    'WriteText',        NULL,       @Converted      --text method

--Commit data and close text stream
EXECUTE sp_OAMethod                  @OLE,    'SaveToFile',       NULL,       @File, 2   --1 = notexist 2 = overwrite
EXECUTE sp_OAMethod                  @OLE,    'Close'
EXECUTE sp_OADestroy                 @OLE

EXECUTE sp_OADestroy @OLE 

END 

这篇关于SQL-创建XML-如何设置Unicode UTF-8的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！