I recieve basics 64 string from the XML file which I wish to place into my SQL Server database. Which area type possess the area during my database to become? varbinary(MAX)? Must i convert the bottom 64 string to a different format before placing into my database?


If you plan to keep your Base64 string out of the box, you should use the VARCHAR data type. The Base64 encoding continues to be devised to make use of only 7-bit ASCII figures.

However, if you'd rather store your computer data in binary format, you should make use of the VARBINARY data type and convert your Base64 string to binary. You should use the XQuery functionality (SQL Server 2005 let's start) to simply convert values to VARBINARY and vice-versa.

Convert Base64 value inside a variable to VARBINARY:

declare @str varchar(20);

set @str = '3qAAAA==';

select cast(N'' as xml).value('xs:base64Binary(sql:variable("@str"))', 'varbinary(20)');

Convert binary value inside a variable to Base64:

declare @bin varbinary(20);

set @bin = 0xDEA00000;

select cast(N'' as xml).value('xs:base64Binary(xs:hexBinary(sql:variable("@bin")))', 'varchar(20)');

Source (and much more good examples): Converting from Base64 to varbinary and vice versa.

Becasue it is a string along with a string is really a string is really a string, you need to have the ability to place it right into a VARCHAR(x) or VARCHAR(MAX) area with no problems.

The "sized" VARCHAR(x) includes a max. period of 8000 figures, while VARCHAR(MAX) tops out at 2 GB (2**31-1 byte) of size.

varbinary(MAX) will be the most effective storage medium (raw bytes are more compact than b64 encoded), but you'd need to convert from b64 to raw bytes. If you wish to keep b64 as you become it, only use varchar(max). Really is dependent- if you are likely to splat it back into XML, it would be less processing to depart the b64 alone.

You may either place the Base64 string itself right into a VARCHAR column, or you can convert the Base64 string to a byte[] array and store that inside a VARBINARY column.

Determining which option is easily the most appropriate will rely on that which you subsequently need related to the information. Should you prefer a Base64 string then ensure that it stays this way (inside a VARCHAR column) Should you prefer a byte[] array then convert it (and store it inside a VARBINARY column).

It's worth mentioning that if you are using varchar(x) or varchar(max) with base64 strings and you apply the base64 string in almost any WHERE clauses for searches you need to use a situation sensitive collation around the column because situation is important with base64 encoded data.