#sql2012 xml base64

That code looks like this:

FUNCTION Base64ToGuidString(pBase64 in Varchar2) return Varchar2 as     vRaw  VARCHAR2(32);     vGuid VARCHAR2(36); BEGIN    vGuid := '';    if (pBase64 is not null and (length(pBase64) > 23)) then     vRaw  := UTL_ENCODE.base64_decode(utl_raw.cast_to_raw(pBase64));

      vGuid := substr(vRaw, 7, 2) || substr(vRaw, 5, 2) ||                substr(vRaw, 3, 2) || substr(vRaw, 1, 2) || '-' ||                substr(vRaw, 11, 2) || substr(vRaw, 9, 2) || '-' ||                substr(vRaw, 15, 2) || substr(vRaw, 13, 2) || '-' ||                substr(vRaw, 17, 4) || '-' ||                substr(vRaw, 21, length(vRaw) - 20);

    end if;

    return lower(vGuid);   END;

Well, that's nice enough if you have a UTL_ENCODE package to lean on.  In order to do this on SQL Server 2012, I decided to leverage the excellent XML extensions to T-SQL.  Using the XML datatype, one gains access to XML Schemas, wherein we find the ability to convert a base 64 string to a binary.  From there, its a simple CONVERT to uniqueidentifier, and then CONVERT to nvarchar.

The SQL Server version looks like this:

CREATE FUNCTION Base64ToGuidString (@pBase64 varchar(max)) RETURNS varchar(max) AS  BEGIN

  DECLARE @vGuid varchar(36);   SELECT @vGuid =            convert(nvarchar(36),            convert(uniqueidentifier,            CAST(N'' AS              XML).value('xs:base64Binary(sql:variable("@pBase64"))' ,'VARBINARY(MAX)')   ));

  RETURN lower(@vGuid);

END