Dynamics CRM‎ > ‎

SQL Functions for Binary to Base64 conversion

/***********************************************
--  Used to encode file attachments for CRM SQL tables
-- SQL to convert varbinary(max) data to Base64 data type
-- and vice-versa
-- Author:      <Michael Ciurescu>
-- Create date: <20090123>
--Copied from
--http://www.vbforums.com/showthread.php?554886-SQL-Server-2005-Convert-any-data-to-Base64-and-back
**************************************************/

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


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

Comments