How to use base64 encoding in SQL Server 2005

  • Article

Kirk Allen Evans gives an example how to generate a base64 encoded WordML binData element. Just for kicks, here is how the same code would look like in SQL Server 2005 using FOR XML (as in his case, it is not the complete Word document):

WITH

XMLNAMESPACES ('https://schemas.microsoft.com/office/word/2003/wordml' as w)
SELECT img as "w:binData"
FROM OpenRowset(BULK 'c:\temp\test.gif', SINGLE_BLOB) T(img)
FOR XML PATH('w:wordDocument'), TYPE

Oh the joy of declarative programming :-). Here is how you can extract the value (assuming the XML with the base64 encoded value was in a T-SQL variable @x):

SELECT @x.value('declare namespace w="https://schemas.microsoft.com/office/word/2003/wordml";
(/w:wordDocument/w:binData)[1]', 'varbinary(max)')

Note that the value() method will automatically base64 decode (or also hex decode, if the binary value was of type xs:hexBinary) if the target SQL type is a binary type.