[轉]SQL - Create XML - How to set Unicode UTF-8

標籤：sele   port   this   SQ   txt   des   ber   lines   and   

本文轉自：https://stackoverflow.com/questions/44754356/sql-create-xml-how-to-set-unicode-utf-8

 

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.

DECLARE @OLE            INT EXECUTE sp_OACreate ‘ADODB.Stream‘,  @OLE OUTPUTDECLARE @Text XMLSET @Text =  N‘This is the 測試測試 test string‘;  --(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 = textEXECUTE sp_OASetProperty             @OLE,    ‘Mode‘,             3                           --0 = not set, 1 read, 2 write, 3 read/writeEXECUTE 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 streamEXECUTE sp_OAMethod                  @OLE,    ‘SaveToFile‘,       NULL,       ‘d:\1.txt‘, 2   --1 = notexist 2 = overwriteEXECUTE sp_OAMethod                  @OLE,    ‘Close‘EXECUTE sp_OADestroy                 @OLEEXECUTE sp_OADestroy @OLE 

[轉]SQL - Create XML - How to set Unicode UTF-8