OPENXML, Convert Base64 to Binary

Question

For an import/export process, we put binary data into XML as Base64 encoded strings. The issue came up when getting the values back out...

We're using OPENXML because performance on 2005/2008 is horrid using nodes() - it doesn't scale well at all. They fixed the performance issue in SQL Server 2012, but for the sake of legacy support (2005+) it's not a realistic option, and MS doesn't appear to want to backport things (assuming even possible).

Here's some relevant info on the subject.

Ideally, I'm looking for a single SQL statement using OPENXML to shred an XML document that contains binary data encoded to Base64, and provide a result set there that data is correctly rendered as binary data. I have one solution that doesn't use nodes, hoping someone has something better.

Answer 1

You can specify your column as XML and use .value to get the data as varbinary.

Something like this.

declare @XML xml 

set @XML = 
'<root>
  <item>
    <ID>1</ID>
    <Col>Um93MQ==</Col>
  </item>
  <item>
    <ID>2</ID>
    <Col>Um93Mg==</Col>
  </item>
</root>'

declare @idoc int
exec sp_xml_preparedocument @idoc out, @xml

select T.ID,
       T.Col.value('.', 'varbinary(max)')
from openxml(@idoc, '/root/item', 2)
with (ID int,
      Col xml) as T

exec sp_xml_removedocument @idoc

Or you could make use of CLR (if that is an option for you) something like this:

using System;
using System.Text;
    public class CLRTest
    {
        public static byte[] ConvertBase64ToBinary(string str)
        {
            if (str == null)
            {
                return null;
            }
            return Convert.FromBase64String(str);
        }
    }