Search code examples
sqlxmlutf-8encodebcp

bcp queryout xml format file encoding utf-8 xml file validation fail

I have to generate xml file for the format of encoding="UTF-8. I used bcp queryout. xml file was generating. But my issue was the xml file validation fail. could any one help me to resolve this? This is my code.

 DECLARE @xmlBody AS VARCHAR(MAX)= '<?xml version="1.0" encoding="UTF-8"?>'+ CAST(@xmlStr  AS VARCHAR(MAX)) 


       INSERT INTO  [dbo].[MasterXml]   
           ([PurchaseOrderID] ,[Code])          
        values (@PurchaseOrderID, @xmlBody)                         
        Declare @command varchar(8000)          

        SET @command= 'bcp "SELECT TOP 1 [Code] from  [tec_Dev].[dbo].[MasterXml] where PurchaseOrderID='+          
        CAST( @PurchaseOrderID As varchar(20))+'" queryout '            
         +@uploadFolder + CAST(@PurchaseOrderID AS varchar(20))+'.xml' +' -T -N -w -c -C65001'

    print @command

EXEC xp_cmdshell @command
Solution

  • I found solution 

     +@uploadFolder + CAST(@PurchaseOrderID AS varchar(20))+'.xml' +' -T -N -w -c -C65001'

    I remove the -w from the code. then it works. But it works only for Microsoft SQL Server 2014 (SP2-CU6) (KB4019094) - 12.0.5553.0 (X64).

    https://support.microsoft.com/en-us/help/3136780/utf-8-encoding-support-for-the-bcp-utility-and-bulk-insert-transact-sq