I have an Image saved as varbinary(max) in SQL Server 2014:

0xFFD8FFE115064578696600004D4D002A0000000800070...........

I want to convert it to Base64 To use it in Flutter. I tried

SELECT CAST('' as varbinary(max)) FOR XML PATH(''), BINARY BASE64

and get :

MHhGRkQ4RkZFMTE1MDY0NTc4Njk2NjAwMDA0RDREMDAyQTAwMDAwMDA4MDAwN..........

But according to this site I should get:

/9j/4RUGRXhpZgAATU0AKgAAAAgABwESAAMAAAABAAEAAAEaAAUAAAABAAAAYgEbAA........

So how to convert varbinary(max) to base64?

CodePudding user response：

Why are you attempting to CAST() the varbinary data? You just need to select it as an element or an attribute for the varbinary value to get base64 encoded...

/*
 * Data setup...
 */
if object_id('tempdb..#demo') is not null
  drop table #demo;
create table #demo (
  fancyImage varbinary(max)
);
insert #demo (fancyImage) values (0xFFD8FFE115064578696600004D4D002A000000080007);

/*
 * Select as an element containing base64 data
 */
select fancyImage as [base64DemoElement]
from #demo
for xml path(''), binary base64;

/*
 * Select as an attribute containing base64 data
 */
select fancyImage as [@base64Attribute]
from #demo
for xml path('demoElement'), binary base64;

The first select outputs the base data in an element:

<base64DemoElement>/9j/4RUGRXhpZgAATU0AKgAAAAgABw==</base64DemoElement>

The second select outputs the base64 data in an attribute:

<demoElement base64Attribute="/9j/4RUGRXhpZgAATU0AKgAAAAgABw==" />

Following comments discussion with @DaleK, a third alternative to return the bare base64 characters without any XML tags:

select (
  select top 1 cast(fancyImage as varbinary(max)) as [base64DemoElement]
  from #demo
  for xml path(''), type, binary base64
  ).value('.', 'varchar(max)') as [Base64 characters];

Which outputs: