0

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?

Charlieface
  • 52,284
  • 6
  • 19
  • 43
BaselCS
  • 13
  • 3
  • 1
    `CAST('' as varbinary(max))` what are you casting? An empty string? – Dale K Aug 19 '22 at 02:13
  • I think it depends on the image format, I just tried converting some of my images, and some come with your first format and some with the second. If you test your output [here](https://www.base64decode.net/base64-image-decoder) does it return an image? – Dale K Aug 19 '22 at 02:19
  • What are you casting? If it's a varbinary(max) column as you say then why are you casting it? – AlwaysLearning Aug 19 '22 at 02:49
  • @AlwaysLearning I *think* its to avoid having the resultant XML inside a tag - because that results in the column being unnamed, so no tag is added. Just a guess but its the same principle as when you use xml to string agg. Either way the query as shown actually works as intended - so issue is unreproducible. – Dale K Aug 19 '22 at 03:12
  • Well then you `.value('.', 'varchar(max)')` the XML result to get the bare base64 characters. I think the OP is doing something other than they're telling us - otherwise why the `cast()`? – AlwaysLearning Aug 19 '22 at 03:24
  • @AlwaysLearning sure, but many of the tutorials show this (cast) method. – Dale K Aug 19 '22 at 03:25
  • Thanks for the help, it was Solved finally, I am not used to dealing with SQL or any APIs at all. and this project isn't mine I just want to finish it. So I copied first code I saw on the internet and try to work with it. – BaselCS Aug 19 '22 at 04:01

3 Answers3

1

To select a bare Base64 value in SQL Server, without any XML node around it, you just need an unnamed column in FOR XML

SELECT CAST(fancyImage AS varbinary(max))
FROM #demo
FOR XML PATH(''), BINARY BASE64;

Or as a correlated subquery

SELECT
  myBase64 = (
    SELECT CAST(fancyImage AS varbinary(max))
    FOR XML PATH(''), BINARY BASE64
  )
FROM #demo;

db<>fiddle

Charlieface
  • 52,284
  • 6
  • 19
  • 43
0

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:

Base64 characters
/9j/4RUGRXhpZgAATU0AKgAAAAgABw==
AlwaysLearning
  • 7,915
  • 5
  • 27
  • 35
  • `.value('text()[1]', 'varchar(max)')` is slightly faster. Any case, you don't need to go so complicated, you can just use an unnamed column – Charlieface Aug 19 '22 at 11:14
0

I think it was v2008 of SQL-Server, when base64 was made the default in XML for binaries (before it was a hex string). No need to specify this explicitly.

(The option BINARY BASE64 is needed with mode AUTO...)

Just to demonstrate the back and forth I declare some text (a chain of characters) and cast it to binary (the same chain of bytes, but not a string any more):

DECLARE @someText    VARCHAR(100)    = 'This is just some text...';
DECLARE @binary      VARBINARY(MAX)  = CAST(@someText AS VARBINARY(MAX));

--In this case it's okay to rely on implicit casting: easy approach

DECLARE @base64_easy VARCHAR(100) = (SELECT @binary FOR XML PATH(''));

--Just to demonstrate that the base64 we found (VGhpcyBpcyBqdXN0IHNvbWUgdGV4dC4uLg==) is correct we reconvert it simply by casting it to XML and using .value() to retrieve it as binary:

DECLARE @reConverted VARBINARY(MAX) = (SELECT CAST(@base64_easy AS XML).value('.','varbinary(max)'));

--Casting this chain of bytes into a varchar again will show its (unchanged) content:

SELECT CAST(@reConverted AS VARCHAR(100));

All of this can be used within ad-hoc queries.

Hint:

The more explicit statement SELECTs the value into XML and reads this into text via .value()
(The ,type is needed to allow for XML methods)

DECLARE @base64 VARCHAR(100) = (SELECT @binary FOR XML PATH(''), type).value('.','nvarchar(max)'); --VGhpcyBpcyBqdXN0IHNvbWUgdGV4dC4uLg==
Shnugo
  • 66,100
  • 9
  • 53
  • 114