SQL BCP Export XML To UTF-8 Format

Question

I have a query that executes a stored procedure and exports the results to an XML file via BCP. The process works, except that the encoding of the XML file is showing as UCS-2 and I am told that the system I will be importing it into needs it to be UTF-8. Various threads suggest this should be possible, but I can't seem to find the right syntax. My code below...

ALTER PROCEDURE [dbo].[cli_sel_c4t_supplier_report]
                    @Consignment_id decimal(18,0),
                    @Supplier decimal(18,0),
                    @Username varchar(50)
AS
BEGIN
DECLARE             @fileName VARCHAR(50),
                    @sqlCmd VARCHAR(8000),
                    @sqlStr Varchar(8000)
SET                 @fileName = 'C:\C4T\' + CONVERT(VARCHAR(10), @Consignment_id) + CONVERT(VARCHAR(20), @Supplier) + '.xml';
SET                 @sqlStr='EXEC [Company Database].[dbo].[cli_sel_c4t_xml_data] @Consignment_id = ' + CONVERT(VARCHAR(10), @Consignment_id) + ', @Supplier = ' + CONVERT(VARCHAR(12), @Supplier) + ';';
SET                 @sqlCmd = 'bcp "' + @sqlStr + '" queryout ' + @fileName + ' -c -C65001 -T -S "SVRNAME\INSTANCE"';
EXEC                cli_ins_audit_c4t_file @Consignment_id=@Consignment_id, @Supplier_id=@Supplier, @Path=@fileName, @Username=@Username
EXEC                xp_cmdshell @sqlCmd;
END

Any assistance would be appreciated.

Thanks

Steve

Answer 1

To generate UTF-8 instead of UTF-16, use -c instead of -w parameter. The -c option in bcp export characters by single byte.

Explicitly specifying code page 1252, and UTF-8: -c -C 1252