SQL Server: How to convert an XML variable to UTF-8 encoded varbinary(max)?

Question

In SQL Server the default conversion from the xml type to varbinary uses XML text encoding (start and end tags with angled brackets etc) using UTF-16 character encoding. E.g.

declare @xml xml = '<foo><bar>abc</bar></foo>';
declare @foo varbinary(max);
set @foo = CONVERT(varbinary(max), @xml, 2);
select @foo

Yields:

0xFFFE3C0066006F006F003E003C006200610072003E006100620063003C002F006200610072003E003C002F0066006F006F003E00

Notice the zeros, indicating the double byte characters. Is there a way to convert into UTF-8?

msdn.microsoft.com/en-us/library/ms180886%28v=sql.110%29.aspx — Anon
– Anon, Commented Dec 19, 2013 at 20:44

Gustavo F · Accepted Answer · 2013-12-19 19:22:13Z

1

Maybe it's not the best way to do this, but I solved this converting the xml to varchar first:

declare @xml xml = '<foo><bar>abc</bar></foo>';
declare @xmlvarchar varchar(max) = CONVERT(varchar(max), @xml)
declare @foo varbinary(max) = CONVERT(varbinary(max), @xmlvarchar);
select @foo

The output is:

0x3C666F6F3E3C6261723E6162633C2F6261723E3C2F666F6F3E

Of course, varchar depends on your database collation, in my case (non special characters) this solution works perfectly.

answered Dec 19, 2013 at 19:22

Gustavo F

2,20614 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Server: How to convert an XML variable to UTF-8 encoded varbinary(max)?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related