Converting from base64 string to varbinary(max) in SQL Server

Question

I have PDF documents stored in my table as binary, the column that stores the bytes for the PDFs is type varbinary(max). I want to update one record with an updated document in SQL Studio, the way I am attempting to accomplish this is like below

UPDATE table
SET file_bytes=CONVERT(varbinary(max),'JVBERi0xLjYNCiW2JqDQo8PC9UeX...0YNCg==') --this is a base64 string
WHERE id='73c75254-ad86-466e-a881-969e2c6e7a04';

The query runs, but when I try to download the document (via the website), it throws an error message that reads PDF header signature not found.

Is this conversion even possible?

Solution

It is possible by using the approach described here : https://blogs.msdn.microsoft.com/sqltips/2008/06/30/converting-from-base64-to-varbinary-and-vice-versa/

It's a two-step process, first you declare a variable :

declare @str varchar(max) = '/9j/4AAQSkZJRgABAQEAAAAAAAD/==';

Then you can use the variable in your SQL statement as follow :

INSERT INTO Documents (Name, Body, MIMEType)
VALUES('12446_photo.jpg', cast(N'' as xml).value('xs:base64Binary(sql:variable("@str"))', 'varbinary(max)'), 'image/jpeg');

Comments

[sedavidw]

You don't need the variable. cast('/9j/4AAQSkZJRgABAQEAAAAAAAD/==' as xml).value('xs:base64Binary(.)', 'varbinary(max)') Since every character in base64 is considered text in xml, the whole string becomes a single text node.