Retrieve Varbinary Value as Base64 in Mssql

Retrieve varbinary value as BASE64 in MSSQL

Finally found this article:

https://social.technet.microsoft.com/wiki/contents/articles/36388.transact-sql-convert-varbinary-to-base64-string-and-vice-versa.aspx#Convert_VARBINARY_to_Base64_String

So, running the query gets what I wanted, valid Base64.

Using XML and the hint "for xml path"

select Model, baze64
from __MigrationHistory
cross apply (select Model as '*' for xml path('')) T (baze64)

Other presented queries in article will also work

Using XML XQuery

Using JSON

Convert varbinary to base64 in .NET

Look at the Convert.ToBase64String() method.

How to convert Varbinary Column to Base64 String to be saved as Json?

SELECT Id, Name, CAST('' as XML).value('xs:base64Binary(sql:column("Content"))', 'NVARCHAR(MAX)') as [Content]
FROM Files



declare @binary varbinary(max) = cast(N'{"message":"OK"}' as varbinary(max))
select CAST('' as XML).value('xs:base64Binary(sql:variable("@binary"))', 'NVARCHAR(MAX)') -- ewAiAG0AZQBzAHMAYQBnAGUAIgA6ACIATwBLACIAfQA=

Console.WriteLine(System.Text.Encoding.Unicode.GetString(Convert.FromBase64String("ewAiAG0AZQBzAHMAYQBnAGUAIgA6ACIATwBLACIAfQA="))); --{"message":"OK"}

SQL Server stored procedure convert varbinary to Base64String

You can use XML to get Base64:

Getting records that decoded(objectGUID) = extensionAttribute6

SELECT *
FROM your_tab
WHERE extensionAttribute6 IS NOT NULL
  AND CAST(CAST('' as XML).value('xs:base64Binary(sql:column("objectGUID"))', 'VARBINARY(MAX)') AS NVARCHAR(MAX)) = extensionAttribute6

And update:

UPDATE your_tab
SET Compliance = IIF(CAST(CAST('' as XML).value('xs:base64Binary(sql:column("objectGUID"))', 'VARBINARY(MAX)') AS NVARCHAR(MAX)) = extensionAttribute6, 'True', 'False')

For storing True/False you should use BIT datatype instead of string.

EDIT:

SELECT *
   [Compliance] = IIF(CAST(CAST('' as XML).value('xs:base64Binary(sql:column("objectGUID"))', 'VARBINARY(MAX)') AS NVARCHAR(MAX)) = extensionAttribute6, 'True', 'False')
FROM your_tab

Base64 encoding in SQL Server 2005 T-SQL

I know this has already been answered, but I just spent more time than I care to admit coming up with single-line SQL statements to accomplish this, so I'll share them here in case anyone else needs to do the same:

-- Encode the string "TestData" in Base64 to get "VGVzdERhdGE="
SELECT
    CAST(N'' AS XML).value(
          'xs:base64Binary(xs:hexBinary(sql:column("bin")))'
        , 'VARCHAR(MAX)'
    )   Base64Encoding
FROM (
    SELECT CAST('TestData' AS VARBINARY(MAX)) AS bin
) AS bin_sql_server_temp;

-- Decode the Base64-encoded string "VGVzdERhdGE=" to get back "TestData"
SELECT 
    CAST(
        CAST(N'' AS XML).value(
            'xs:base64Binary("VGVzdERhdGE=")'
          , 'VARBINARY(MAX)'
        ) 
        AS VARCHAR(MAX)
    )   ASCIIEncoding
;

I had to use a subquery-generated table in the first (encoding) query because I couldn't find any way to convert the original value ("TestData") to its hex string representation ("5465737444617461") to include as the argument to xs:hexBinary() in the XQuery statement.

I hope this helps someone!

SQL Server base64 encoding stored function

Well, I defenitly am pretty dumb. This code works:

ALTER FUNCTION [dbo].[usf_base64_encode]
(
    @value varchar(max)
)
RETURNS varchar(max)
AS
BEGIN
    DECLARE @source varbinary(max) = convert(varbinary(max), @value)
    RETURN cast('' as xml).value('xs:base64Binary(sql:variable("@source"))', 'varchar(max)')
END

---

Related Topics