title: Output XML Files with encoding UTF-8 using SQL Server
body: <p>I have a query that generates XML files and loads them to FTP with <code>&lt;?xml version="1.0"?&gt;</code>.</p>
  
  <p>I need to switch encoding to UTF-8 as follows: </p>
  
  <pre><code>&lt;?xml version="1.0" encoding="utf-8"?&gt; 
  </code></pre>
  
  <p>I can do it manually in the text editor. But cannot do it in SQL Server. </p>
  
  <p>I also read this article but that did not contribute in finding solution for the issue. </p>
  
  <p><a href="https://docs.microsoft.com/en-us/sql/relational-databases/xml/create-instances-of-xml-data" rel="nofollow noreferrer">https://docs.microsoft.com/en-us/sql/relational-databases/xml/create-instances-of-xml-data</a></p>
  
  <p><a href="https://i.stack.imgur.com/B9zcV.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/B9zcV.png" alt="enter image description here"></a> </p>
  
  <p><a href="https://i.stack.imgur.com/gDHHw.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/gDHHw.png" alt="enter image description here"></a></p>
  
  <p>My code:</p>
  
  <pre><code>USE [Audit_DBA]
  GO
  
  SET ANSI_NULLS ON
  GO
  
  SET QUOTED_IDENTIFIER ON
  GO
  
  -- to call the procedure use the code below but assign your own path: 
  -- EXEC [Audit_DBA].[dbo].[StateAndLocalAward_ToXML] 'C:\NLP\Test\NewFolder'
  
  CREATE PROCEDURE [dbo].[StateAndLocalAward_ToXML_VC]
      @FileDestinationDir VARCHAR(2000)
  AS 
      DECLARE  @FilePath VARCHAR(4000)
  
      DECLARE @SQLStr VARCHAR(8000),
              @Cmd VARCHAR(1000),
              @Ret INT
  
      IF EXISTS (SELECT * FROM InputTemp.SYS.TABLES WHERE NAME = 'StateAndLocalAward_output') 
         DROP TABLE InputTemp.dbo.StateAndLocalAward_output;
  
      ;WITH CTE_StateAndLocalAward_output AS 
      (
          SELECT TOP 200
              sc.stateContractId,
              ca.OnviaId AS [Reference], --AS OnviaID,
              ca.contractTitle AS [Title], 
              nigp.NIGPCodeTx AS [NIPGCode],
              nigp.NIGPDescriptionLongTx AS [NIPGDescription],
              ca.Description,
              vm.parentName AS [VendorName],
              offering.offeringTx AS [PrimaryOffering],
              ca.StartDate,
              ca.AwardDt
          FROM 
              ISCore.dbo.StateContract sc WITH (NOLOCK)
          INNER JOIN 
              ISCore.dbo.ContractAward ca WITH (NOLOCK) ON sc.contractAwardId = ca.contractAwardID
          INNER JOIN 
              ISLibrary.dbo.VendorMaster vm WITH (NOLOCK) ON ca.vendorId = vm.vendorID
          LEFT OUTER JOIN 
              ISCore.dbo.CompanyProfile cp WITH (NOLOCK) ON ca.vendorId = cp.vendorId
          LEFT OUTER JOIN 
              ISCore.dbo.Offering offering WITH (NOLOCK) ON ca.offeringID = offering.offeringID
          LEFT OUTER JOIN 
              ISCore.dbo.NIGPSrvc nigp WITH (NOLOCK) ON ca.NIGPCode = nigp.NIGPCodeTx
          WHERE 
              vm.showUnverified = 1 AND sc.stateContractId = -- 464482 stateContractId
              AND StartDate &gt;= DATEADD(month, -12, GETDATE())
      )
      SELECT 
          *
      INTO 
          InputTemp.dbo.StateAndLocalAward_output 
      FROM 
          CTE_StateAndLocalAward_output;
  
      --select * from InputTemp.dbo.StateAndLocalAward_output
  
      DECLARE @StateContractId INT;
      --DECLARE @xml XML;
  
      DECLARE Cur_StateAndLocalAward_StateContractId CURSOR FOR
          SELECT StateContractId
          FROM inputtemp.dbo.StateAndLocalAward_output t1 WITH (NOLOCK)
  
      OPEN Cur_StateAndLocalAward_StateContractId;
  
      FETCH NEXT FROM Cur_StateAndLocalAward_StateContractId INTO @StateContractId -- @xml;
  
      WHILE @@FETCH_STATUS = 0 
      BEGIN
          SELECT 
              @SQLStr = 'SELECT Body.[Reference], Body.[Title], Body.[NIPGCode], Body.[NIPGDescription], Body.[Description], Body.[VendorName], Body.[PrimaryOffering] FROM InputTemp.dbo.StateAndLocalAward_output AS Body where StateContractId = ''' + str(@StateContractId) + ''' FOR XML AUTO, ELEMENTS;'
  
          --SELECT N'&lt;?xml version="1.0" encoding="UTF-8"?&gt;' 
          SELECT
              CAST((SELECT N'&lt;?xml version="1.0" encoding="UTF-8"?&gt;' + (@SQLSTr)) as varchar(8000) /*as XML*/) as SQLStr 
  
          -- select CAST((SELECT N'&lt;?xml version="1.0" encoding="UTF-8"?&gt;' + 
          --  (@SQLSTr)) as XML) as SQLStr 
  </code></pre>
  
  <p>If I run this code instead of the same above but cast as <code>XML type</code>, I get an error: </p>
  
  <blockquote>
    <p>Msg 9402, Level 16, State 1, Procedure StateAndLocalAward_ToXML, Line 86 [Batch Start Line 10]<br>
    XML parsing: line 1, character 38, unable to switch the encoding</p>
  </blockquote>
  
  <pre><code>select @FilePath = @FileDestinationDir+'\NewFolder'+ltrim(rtrim(str(@StateContractId)))+'.xml' 
  select @Cmd = ' bcp " ' + @SQLStr + '" queryout '+@FilePath+' -w -r "" -T -S ' +@@ServerName 
  exec  @Ret = master.dbo.xp_cmdshell @Cmd 
  FETCH NEXT FROM Cur_StateAndLocalAward_StateContractId INTO @StateContractId -- @xml;
  END
  CLOSE Cur_StateAndLocalAward_StateContractId;
  DEALLOCATE Cur_StateAndLocalAward_StateContractId;
  GO
  </code></pre>