SQL Server 2005 - Convert any data to Base64 and back

[CVMichael]

Even though the actual conversion is a one line code, it is still better to have it in a function (at least you don't have to remember all that XML stuff...)

The first function f_BinaryToBase64 takes binary data as input, but anything can be converted to binary.

The second function f_Base64ToBinary takes the Base64 data (the output of the first function), and outputs the original binary data (that can be converted back to your data type).

How to use with strings for example:

DECLARE @Base64Data VARCHAR(MAX)
SET @Base64Data = dbo.f_BinaryToBase64(CONVERT(VARBINARY(MAX),'testing...'))
 
SELECT @Base64Data AS Base64, CONVERT(VARCHAR(MAX), dbo.f_Base64ToBinary(@Base64Data)) AS My_Data

And the actual functions:

-- =============================================
-- Author:      <Michael Ciurescu>
-- Create date: <20090123>
-- Description: <Description, ,>
-- =============================================
CREATE FUNCTION dbo.f_BinaryToBase64
(
    @bin VARBINARY(MAX)
)
RETURNS VARCHAR(MAX)
AS
BEGIN
    DECLARE @Base64 VARCHAR(MAX)
    
    /*
        SELECT dbo.f_BinaryToBase64(CONVERT(VARBINARY(MAX), 'Converting this text to Base64...'))
    */
    
    SET @Base64 = CAST(N'' AS XML).value('xs:base64Binary(xs:hexBinary(sql:variable("@bin")))', 'VARCHAR(MAX)')
    
    RETURN @Base64
END
 
 
-- =============================================
-- Author:      <Michael Ciurescu>
-- Create date: <20090123>
-- Description: <Description, ,>
-- =============================================
CREATE FUNCTION dbo.f_Base64ToBinary
(
    @Base64 VARCHAR(MAX)
)
RETURNS VARBINARY(MAX)
AS
BEGIN
    DECLARE @Bin VARBINARY(MAX)
    
    /*
        SELECT CONVERT(VARCHAR(MAX), dbo.f_Base64ToBinary('Q29udmVydGluZyB0aGlzIHRleHQgdG8gQmFzZTY0Li4u'))
    */
    
    SET @Bin = CAST(N'' AS XML).value('xs:base64Binary(sql:variable("@Base64"))', 'VARBINARY(MAX)')
 
    RETURN @Bin
END

[Tobiasgar]

Your example works when the function performing the mapping returns the same type as the input parameter type (in this case a string). Can you please give an example when the mapping function returns a different type?

[CVMichael]

BIGINT:

DECLARE @BigIntData BIGINT
DECLARE @Base64Data VARCHAR(MAX)
 
SET @BigIntData = 1242353245346345643
 
SET @Base64Data = dbo.f_BinaryToBase64(CONVERT(VARBINARY(MAX), @BigIntData))
 
SELECT @Base64Data AS Base64, CONVERT(BIGINT, dbo.f_Base64ToBinary(@Base64Data)) AS [BigInt]

DATETIME:

DECLARE @myDate DATETIME
DECLARE @Base64Data VARCHAR(MAX)
 
SET @myDate = GETDATE()
 
SET @Base64Data = dbo.f_BinaryToBase64(CONVERT(VARBINARY(MAX), @myDate))
 
SELECT @Base64Data AS Base64, CONVERT(DATETIME, dbo.f_Base64ToBinary(@Base64Data)) AS myDate

XML:

DECLARE @XML XML
DECLARE @Base64Data VARCHAR(MAX)
 
SET @XML = (
    SELECT TABLE_NAME, ORDINAL_POSITION, COLUMN_NAME
    FROM INFORMATION_SCHEMA.COLUMNS AS row
    WHERE TABLE_NAME = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME NOT LIKE 'sys%')
    FOR XML AUTO, ELEMENTS, ROOT('root')
)
 
SET @Base64Data = dbo.f_BinaryToBase64(CONVERT(VARBINARY(MAX), @XML))
 
SELECT @Base64Data AS [Base64]
 
 
DECLARE @New_XML XML
SET @New_XML = CONVERT(XML, dbo.f_Base64ToBinary(@Base64Data))
 
SELECT row.col.value('./TABLE_NAME[1]','NVARCHAR(128)') AS TABLE_NAME
    , row.col.value('./ORDINAL_POSITION[1]','INT') AS ORDINAL_POSITION
    , row.col.value('./COLUMN_NAME[1]','NVARCHAR(128)') AS COLUMN_NAME
FROM @New_XML.nodes('//row') AS row(col)

[slamo786]

I have a table with PK,Biometric Image1,Image2 and need to update the Image1 and Image2 to base64 encode.

the function does work, but i need to create a stored procedure to copy the table contents between linked server and then update the image data to base64encode

please help?

[malibeg]

Great post. Here's how to dinamically create query to convert columns from XML. I used this in ServiceBroker app

DECLARE @XmlDocument XML;
		SET @XmlDocument = (select TOP 1 * FROM BRO.Dialogs FOR XML AUTO, BINARY BASE64, ROOT('root'));
		--SELECT @XmlDocument	;
		
		declare @SQL varchar(max), @SQL2 varchar(max), @SQL3 varchar(max);
	    select @SQL = coalesce(@SQL + ','+ space(1),' ') + quotename(COLUMN_NAME),
			@SQL2 = coalesce(@SQL2 + ',' + space(1),' ') + (CASE WHEN DATA_TYPE = 'binary'
				   THEN  'dbo.f_Base64ToBinary(' + COLUMN_NAME + ')'
				   ELSE COLUMN_NAME END),
		    @SQL3 = coalesce(@SQL3 + ',' + space(1),' ') + (CASE WHEN DATA_TYPE = 'binary'
				   THEN  COLUMN_NAME + ' ' + 'VARCHAR(max)'
				   WHEN CHARACTER_MAXIMUM_LENGTH IS NOT NULL THEN COLUMN_NAME + ' ' + DATA_TYPE +'(' + CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(20)) + ')'
				   ELSE COLUMN_NAME + ' ' + DATA_TYPE END)
		from INFORMATION_SCHEMA.COLUMNS
		where TABLE_SCHEMA = 'BRO' AND TABLE_NAME = 'Dialogs'
		and COLUMNPROPERTY(object_id(TABLE_SCHEMA + '.' +TABLE_NAME), COLUMN_NAME, 'IsIdentity') <> 1
		ORDER BY ORDINAL_POSITION;
		--SELECT @SQL, @SQL2, @SQL3;
		
		declare @var_query nvarchar(max)
		
		SET @var_query = '
		DECLARE @DocHandle int;
		DECLARE @XmlDocument XML;
		SET @XmlDocument = ''' +CAST(@XmlDocument AS NVARCHAR(max)) + ''';
		-- Create an internal representation of the XML document.
		EXEC sp_xml_preparedocument @DocHandle OUTPUT, @XmlDocument;
		SELECT  '+ @SQL2+ ' 
		FROM OPENXML (@DocHandle, ''/root/BRO.Dialogs'',1)
		WITH ( '+ @SQL3 +' );'
		
		--SELECT @var_query
		
		exec dbo.sp_executesql @statement = @var_query