Retrieve Varbinary Value As Base64 In Mssql

I'm looking for a way for retrieving Entity Data Model (EDM) from __MigrationHistory table using only T-SQL (so anyone, using Microsoft SQL Server Management Studio only, could do

Solution 1:

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

Solution 2:

It's apparently gzip'd xml. eg

SELECT MigrationId
      ,ContextKey
      ,cast(decompress(model) as xml) model
  FROM __MigrationHistory

Solution 3:

You can Convert binary to Base64 using XML and the hint "for xml path"

select Column1,ColumnWithBinary,ColumnToSwFinalResult
from TableWithBinary
cross apply (select ColumnWithBinaryas '*'for xml path('')) T (ColumnToSwFinalResult)
GO

Solution 4:

Based on your solution I'm sharing the code for 2 scalar function for conversion in both direction:

Base64Decode

CREATEFUNCTION[dbo].[fnBase64ToBinary]
(
    @Str AS NVARCHAR(MAX)
)
RETURNSVARBINARY(MAX)
ASBEGINRETURN (
        SELECT
                CONVERT(
                    VARBINARY(MAX), CAST('' AS XML).value('xs:base64Binary(sql:column("BASE64_COLUMN"))', 'VARBINARY(MAX)')
                )
        FROM    (SELECT @Str AS BASE64_COLUMN) A
    );
END;

Base64 encode

CREATEFUNCTION[dbo].[fnBinaryToBase64]
(
    @Var AS VARBINARY(MAX)
)
RETURNSNVARCHAR(MAX)
ASBEGINRETURN (
        SELECT  @Var AS '*' FOR XML PATH('')
    );
END;

Solution 5:

Necromancing. You can also use for JSON PATH similar to how for XML PATH is used:

SELECT 
     T_AP_Dokumente.DK_UID 
    ,T_AP_Dokumente.DK_Thumbnail 
    ,tBase64.JSON_Thumbnail 
    ,NULLIF(T.XML_Thumbnail, '') AS base64  


    ,COMPRESS(T_AP_Dokumente.DK_Thumbnail) AS gzipped 
    ,DATALENGTH(COMPRESS(T_AP_Dokumente.DK_Thumbnail)) AS dlt_datalength_gzipped 
    ,DATALENGTH(T_AP_Dokumente.DK_Thumbnail) AS dlt_datalength 
    ,DATALENGTH(DECOMPRESS(COMPRESS(T_AP_Dokumente.DK_Thumbnail))) AS should_equal_dlt_datalength 
FROM T_AP_Dokumente 

CROSS APPLY 
(
    SELECT T_AP_Dokumente.DK_Thumbnail AS '*' FOR XML PATH('')
) AS T(XML_Thumbnail)

CROSS APPLY 
(
    SELECT * FROM 
    OPENJSON 
    (
        (
            SELECT T_AP_Dokumente.DK_Thumbnail AS JSON_Thumbnail 
            FOR JSON PATH 
        )
    ) WITH(JSON_Thumbnail varchar(MAX)) AS t 
) AS tBase64