MS SQL XQuery xs:base64Binary returns NULL

Question

I (have to) use base64Binary to convert my base64 encoded string into bytes. In most cases it works good enough, but from time to time it returns NULL . For example this works like a charm:

DECLARE @Base64String VARCHAR(MAX)
SET @Base64String = 'qwerqwerqwerqwer'

declare @Base64Binary VARBINARY(MAX)
set @Base64Binary = cast('' as xml).value('xs:base64Binary(sql:variable("@Base64String"))', 'VARBINARY(max)');

select @Base64Binary as 'base64'

Result is 0xAB07ABAB07ABAB07ABAB07AB and that's ok for me. But if I set SET @Base64String = 'qwerqwerqwerqwe=' then I get NULL as result. Why? I pass pretty valid base64 string and expect not null value. I've tried to find some workaround, but no luck. How can I made xs:base64Binary to return valid varbinary value for such input strings?

Answer 1

Having had a little look at this, I would suggest that qwerqwerqwerqwe= is not a valid base64 string.

Decoding qwerqwerqwerqwe= using a base64 conversion tool in C# renders the following:

0xAB07ABAB07ABAB07ABAB07

Encoding this in SQL server actually gives the output qwerqwerqwerqwc=:

DECLARE @Base64String VARCHAR(MAX)
DECLARE @Base64Binary VARBINARY(MAX)

SET @Base64Binary = 0xAB07ABAB07ABAB07ABAB07
PRINT @Base64Binary
SET @Base64String = CAST('' AS XML).value('xs:base64Binary(sql:variable("@Base64Binary"))', 'VARCHAR(max)');
PRINT @Base64String

I would suggest that the reason that SQL Server is returning NULL to you is that the base64 string you are working with is not actually valid.