问题描述
我在从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:
当我在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的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！