How to Export XML Files in UTF-8 Format Using SQL BCP

Описание: Learn how to convert SQL BCP output files to `UTF-8` format instead of `UCS-2`, ensuring compatibility with your importing system.
---
This video is based on the question https://stackoverflow.com/q/67872102/ asked by the user 'steve_flynn' ( https://stackoverflow.com/u/8070705/ ) and on the answer https://stackoverflow.com/a/67872294/ provided by the user 'Yitzhak Khabinsky' ( https://stackoverflow.com/u/1932311/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: SQL BCP Export XML To UTF-8 Format

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
How to Export XML Files in UTF-8 Format Using SQL BCP

When working with SQL Server and needing to export data in XML format, you may encounter situations where the character encoding doesn't meet your requirements. One common issue is exporting an XML file that defaults to UCS-2 encoding instead of the required UTF-8 format. This post will guide you through resolving this issue using SQL Server's BCP (Bulk Copy Program) utility.

Problem Overview

Steve, a user looking to export results from a stored procedure into an XML file, faced a challenge where the exported file's encoding was UCS-2. The system he was working with required a UTF-8 encoding, making it essential to find a solution to adjust the BCP export settings appropriately.

Example Scenario

Here's a simplified version of the stored procedure that Steve was using to execute the export:

[[See Video to Reveal this Text or Code Snippet]]

Solution: Changing the BCP Command

To ensure the output XML file is encoded in UTF-8, you need to adjust the parameters used in the BCP command.

Steps to Modify the Export Command

Change the Parameter from -w to -c:

Instead of using -w, which indicates wide character format (UCS-2), you should use -c. This tells the BCP to export characters as single-byte for UTF-8.

Specify Code Page 65001:

Within the BCP command, include -C 65001, which is the code page for UTF-8.

Revised BCP Command:

Here’s how your revised BCP command should look:

[[See Video to Reveal this Text or Code Snippet]]

Additional Tips

Ensure you have the necessary permissions to execute xp_cmdshell, as this may require elevated privileges on your SQL Server instance.

After making these adjustments, test the export process to confirm that the XML file is correctly encoded in UTF-8.

Conclusion

By following the steps outlined above, you can resolve the issue of XML files being generated in UCS-2 encoding when using SQL Server's BCP utility. This relatively simple change allows your system to output XML in the required UTF-8 format, ensuring compatibility for data imports without issues. If you encounter further challenges, consider revisiting your BCP command parameters or consulting SQL Server documentation for additional options. Happy exporting!