Convert SQL Server Base64 Encoded Text to Images


By:   |   Updated: 2018-09-05   |   Comments   |   Related: More > T-SQL


Problem

A table in our SQL Server database stores images in Base64-encoded text format. It does not seem that SQL Server CAST() and CONVERT() can convert Base64-encoded text into images. Is there an easy way to decode and convert these BASE64-encoded data back into an image and save them on the file system?

Solution

Base64 encoding schemes are commonly used when there is a need to encode binary data that needs be stored and transferred over media that are designed to deal with textual data. From this definition, we would expect the Base64 content is stored in a VARCHAR(max) data type in SQL Server. SQL Server CONVERT() and CAST() function do not recognize Base64 textual content specifically, it treats Base64 content as like any normal text.

But fortunately, hidden in SQL Server XQuery, the support to encode and decode BASE64 format is readily available since SQL Server 2005. You can then leverage SQL Server Data Tools (SSDT) to loop and download all the decoded Base64 content to the file system in image format.

In this tip, we will run through the actual mechanics to the solution using XQuery and SSDT. The solution is built using SSDT for Visual Studio 2013 and the table is in a database on SQL Server 2016 Developer Edition.

At the end of this tip, there is a link to download the SSDT solution and the database used in this tip.

Demonstration

We have table [dbo].[Base64Images] with the table definition below. This table is populated with 2 sample rows. The type of the image can be determined by the first 4 letters in the Base64 content. Image type PNG would begin with “iVBO”, and image type JPG will begin with “/9j/” to name a couple of examples.

Column Name Data Type Description
FileID INT Auto-numbering row identifier
Base64Content VARCHAR(MAX) The Base64-encoded images in textual format
FileName VARCHAR(255) The image file name and extension

Here we can see our sample images use image type PNG since the Base64 content starts with “iVBO”.

SELECT *
FROM [ImageDB].[dbo].[Base64Images]
Column Base64Content contains only textual

Step 1 - Decode and Convert the Base64-encoded Text into Binary Type

The T-SQL below uses XQuery to decode and convert the Base64-encoded text into binary type. This query will be subsequently used as a source input in SSDT.

WITH ConvertBase64 AS (
SELECT 
   Base64Content 
   , 'C:\mssqltips\images\' + FileName as [PathAndFileName]
FROM [ImageDB].[dbo].[Base64Images]
)
SELECT 
   [PathAndFileName]
   , CAST('' AS XML).value('xs:base64Binary(sql:column("Base64Content"))'
     , 'VARBINARY(MAX)') AS img
FROM ConvertBase64
Output of decoded Base64-encoded text is binary

Step 2 - Create New SSIS Package

Using SSDT, create a blank SSIS package and configure a new Connection Manager using [Native OLE DB\SQL Server Native Client] to the source database containing the table [dbo].[Base64Images]. The connection manager name in this solution is ImageDB.

Create a new SSIS package and establish a connection to source database

Step 3 - Add a Data Flow Task in Integration Services

Drag and drop a [Data Flow Task] from the SSIS Toolbox into the [Control Flow].

ssis package

Step 4 - Setup the Data Source in SSIS

Double-click the [Data Flow Task] to go into the [Data Flow]. Inside the [Data Flow Task], drag and drop an [OLE DB Source] from SSIS Toolbox.

Create [OLD DB Source] in [Data Flow Task]

When you double-click the [OLE DB Source] here, it will launch the [OLE DB Source Editor] form. Set the OLE DB connection manager here to the connection manager created in Step 1 which is ImageDB. Paste the T-SQL query from Step 1 to the SQL command text. Essentially, this query returns 2 columns as described in the table below.

Column Name Description
PathAndFileName Returns the concatenated path on file system and the file name
img Decoded Base64-text in binary type using XQuery


Specify query to return output to [OLE DB Source]

Step 5 - Map the Columns in SQL Server Integration Services

Click on the Columns page. This will reflect the 2 columns output from the query, PathAndFileName and img column. This step is actually very important to map the columns from the query to the [OLE DB Source]. Click the [OK] button to go back to the SSIS package.

ssis ole db source editor

Step 6 - Export Column Transformation in Integration Services

In the [Other Transforms] section in the SSIS Toolbox, drag and drop the [Export Column].

ssis package

Connect the precedence to specify [OLE DB Source] as the source, and [Export Column] as the destination.

ssis package

Double-click the [Export Column] task, it will launch the [Export Column Transformation Editor]. Specify column [img] in the Extract Column since this contains the binary image, and the column [PathAndFileName] in the File Path Column.

ssis column transformation editor

Step 7 - Execute the SSIS Package

This is the final step to execute the package or the [Data Flow Task]. We will execute the [Data Flow Task] as below.

execute ssis package task

The execution of the [Data Flow Task] is successful.

ssis package

Step 8 - Review the Exported Files

Go to the path specified in the query. There are 2 image files in folder C:\mssqltips\images folder.

image folder

Conclusion

XQuery contains many hidden gems to complement T-SQL query in SQL Server to greatly simplify some challenging scenarios. In addition, SSDT is a very powerful free ETL tool; especially the [Export Column] task is very useful for downloading images stored in database table. It removes a lot of complexity for developers and requires only a few parameter configurations to get it to work.

Next Steps


Last Updated: 2018-09-05


get scripts

next tip button



About the author
MSSQLTips author Simon Liew Simon Liew is an independent SQL Server Consultant in Sydney, Australia. He is a Microsoft Certified Master for SQL Server 2008 and holds a Masters Degree in Distributed Computing.

View all my tips



Comments For This Article





download





Recommended Reading

SQL Server Cursor Example

Using MERGE in SQL Server to insert, update and delete at the same time

Rolling up multiple rows into a single row and column for SQL Server data

How to use @@ROWCOUNT in SQL Server

SQL Server Loop through Table Rows without Cursor














get free sql tips
agree to terms