Retrieve varbinary value as BASE64 in MSSQL

convert varbinary to base64 online
sql server varbinary to string
varbinary to base64 online converter
convert varbinary to image sql server
base64 to varbinary online
sql server insert base64 into varbinary
sql server convert text to varbinary
sql server convert hex to base64

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 the same).

I want to have a valid BASE64 string value.

I don't want to fully decompress it to EDMX.

I don't want to get it from *.resx migration file.

When I select whole __MigrationHistory in SSMS, Model column value is like following

0x1F8B0800000000000400CD57DB6EDB38107D5F60FF81E0D31648C5247DD906528BD4898BA075525469DF6969EC10CB8BCA4B607F5B1FFA49FB0B3BBACB521CB7E9A228020422357366E6F0CC50FEF7EBB7F8F54649720FD609A3137A121D53023A33B9D0EB8406BF7AFE377DFDEACF3FE2CB5C6DC8E7D6EE4569879EDA25F4CEFBE28C3197DD81E22E5222B3C699958F32A318CF0D3B3D3E7EC94E4E182004452C42E28F417BA1A05AE072667406850F5C2E4C0ED235FBF826AD50C93557E00A9E414253AE0A0917DCF325771035CB254278D8784ACEA5E098550A724509D7DA78EE31E7B34F0E526F8D5EA7056E7079BB2D00ED565C3A686A39EBCDBFB7ACE3D3B22CD63BB6505970DEA81F043C79D1F0C4C6EE4F629B763C229397C8B8DF9655576C26746E0C25E3386733694B9B433C47159C001721CC11D9353EEA84827A2AFF8EC82C481F2C241A82B75C1E910F612945F60EB6B7E61FD0890E520ED3C584F1DDCE066E7DB0A600EBB71F61D514719553C276FDD8D8B1731BF8D435BE0D029FAF31365F4AE8C4C01E752FFFB700A8286C144A167CF31EF4DADF25141F29998B0DE4ED4E83FA490BEC2B74F2368C82C4AC3F9DE999959473A1C136094C243F3944D47A738EAE09B75B518D9B82EFA5E028E973A8DB27AA14F2509E5D467DA3B2BA53DB8E667B5A3A5EF0A240CA062DDEEC90B4EEEFD9F3F4C7C5AE6A0C96B90734DF65DB45F2C6F2358CDE6268CC742EACF3AD902999E56A6236E67F0FB76DB401C5633DF78CB7C6E5F3CE29EFEBBFF26C46703D97732C4F81F655A5D06554F7FCC4AB1AB35C72FB409FCC8C0C4AEFEBB5C7BCEB3619FAD73B5384988DD21ED3C4263C8D3A7ECCF963921D9B74D13BE98E241A3772397C354DF4539B5082D4DC8BBCD2CED67950516910A55FE44C0AACB73758702D56E07C3D1429DE08A7A31BEDF7B95D9873B93C7CC5FCF2A11EB4F81200D9C46C5602EC4F0D787DCF6D76C7ED5F8A6F9E0D917E76884F67CDC111BD6F42D72A4B68BEC4C56D9D5E3DD19F38BBA7828FD9F08B2DBE0027D63D44F9FDA6212B95D483B636577A655A82B19C6146ADC988FF05789E232BE7160F90671E5F67E05C75D17EE632A0C9A55A427EA56F822F823F770ED4526E87F5C6ECF1F8D505B59B737C53942BF77F9480690A2C016EF49B2064DEE53D9FEA6F1F442990B780FB5587E28706C2ADB71DD2B5D1DF09D4D0770105E8B2296E016F120473373AE5F7F094DCF0FBE23DAC79B66DE7D67E90C307B14B7B7C21F8DA72E51A8CDEBFFC15C2CA9F21AFFE038574340FB80C0000

This is my sample database, so I don't mind sharing it.

The column is of type varbinary(max), I googled how can convert it to UTF8*-ish* text.

varbinary to string on SQL Server

SQL Server: Convert a string into a hex string and back #sql #sqlserver

For

SELECT CONVERT(VARCHAR(max), 0x1F8B0800000000000400CD57DB6EDB3810..... , 0)

or even more straightforward

SELECT CONVERT(VARCHAR(max), (SELECT TOP (1) [t0].[Model]FROM [__MigrationHistory] AS [t0]), 0);

I get the following result

literally this:

I checked my Model if it's not broken or incomplete in both LinqPad5 and using this tool found here, but it looks ok.

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

Converting Column values from VARBINARY to Base64 You can get the binary value of your image using simple select from our table (we  APPLIES TO: SQL Server Azure SQL Database Azure Synapse Analytics (SQL DW) Parallel Data Warehouse . If the BINARY BASE64 option is specified in the query, the binary data is returned in base64 encoding format. If the BINARY BASE64 option is not specified in the query, then by default, AUTO mode supports URL encoding of binary data.

It's apparently gzip'd xml. eg

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

Convert VARBINARY (BLOB) to Base64 String using T-SQL. Question asked by TLongSUGF on Jul 12, 2017. Latest reply on Jan 5, 2018 by TLongSUGF. In this question I asked how can I convert varbinary to valid BASE64 string value in MSSQL. Now I'd like to know how can it be done without MSSQL (in .NET f.e.). Let's think of a method which accepts the following string (or byte array) and returns valid BASE64

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 

set @source = convert(varbinary(max), 'Hello Base64') set @encoded = cast('' as xml).value('xs:base64Binary(sql:variable(“@source”))',  The following query returns the product photo stored in a varbinary(max) type column. The BINARY BASE64 option is specified in the query to return the binary data in base64-encoded format. Example USE AdventureWorks2012; GO SELECT ProductPhotoID, ThumbNailPhoto FROM Production.ProductPhoto WHERE ProductPhotoID=1 FOR XML RAW, BINARY BASE64 ; GO

In this tip we look at how to decode Base64 encoded text stored in a SQL Server table and SQL Server CONVERT() and CAST() function do not recognize Base64 textual 'VARBINARY(MAX)') AS img FROM ConvertBase64 Specify column [img] in the Extract Column since this contains the binary  Function for encode string to Base64 in sql server/ Function for edecode Base64string to String in sql server. Previous Updates In previous articles we have learnt Best basic C# interview questions. Transform multiple rows into one comma separated string column. .AngularJS basics, What is Cursor in sql and use of cursor.

SQL Server uses UTF-16 Little Endian only for NVARCHAR data, and even for XML. The trick is to convert the Base64 decoded bytes, in their text representation (even VARBINARY(500) = CONVERT(XML, @Base64Value).​value('.','varbinary(max)'); DECLARE @IntermediaryValue VARCHAR(500)  Stack Overflow for Teams is a private, secure spot for you and your coworkers to find and share information. Learn more How to Store and Retrieve a varbinary(max) Column in SQL Server

Figured it out: SELECT CONVERT ( VARCHAR(MAX), CAST('' AS XML).value('xs​:base64Binary(sql:column("BASE64_COLUMN"))',  Did you know that SQL Server has built-in support for base64 encoding and decoding and has since 2005? I sure didn’t, because it’s hidden in the depths of XQuery. Here is a complete sample

Comments
  • This is ever simpler solution. I wonder though why I get different results in SMSS and LinqPad5. Any ideas?