Decoding MIME Base64

Hi

How do you decode an image (found inside an xml file) which has been encoded in MIME Base 64. If you read in the string, how would you get the image?

I know other database systems (like ASA) that has a build in function to do it for you but I cant find a similar function in SQL Express?

Thanks

Hi

I found out that I can encode images to base64 by using the FOR XML functionality. As far as I can tell I am suppose to be able to use the OPENXML function to turn it back into an image. This does not work in SQL Express, but it does work in ASA?

The only deferens is that in SQL Express I have to get a handle on the xml doc before calling OPENXML. Why will this make a difference and how do I get the correct end result?

Here is the code for SQL EXPRESS (the image encoding is not the full version since it takes to much space):

begin

declare @.doc varchar(max);

DECLARE @.idoc int;

declare @.pic varbinary(max);

set @.doc = '<row><itm_pic>/9j/4FFFFAH//2Q==</itm_pic></row>';

EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc;

SELECT *

FROM OPENXML( @.idoc,'/row/itm_pic')

WITH ( picture image 'text()');

EXEC sp_xml_removedocument @.idoc;

end;

To get the string for the xml doc with the image run this select on a image in a table:

SELECT picture

FROM prod_images

where id_pic = 1'

FOR XML RAW, BINARY BASE64, ELEMENTS

Thanks

Decode Base64 to Image!

Hi,

I have a xml string which is consist of some images encoded in base64; I have to extract these images in a stored procedure and save them in some tables.

The problem is that I can't decode this base64s to images. Is there a way to do it? (I use SQL Server 2005 Enterprise)

Thanx

Convert.FromBase64String
You probably want to take a look at that. You could possibly create a memory stream from the byte array and then create a bitmap from the stream.

|||

Thank you for your help but as I've mentioned I have to extract images in a stored procedure, so I have to decode them in it too.

Sorry for my late reply.

|||hwat about usin a CLR procedure if you have SQL2k5 already. YOu could use the mentioned class in the last post. That should work for you.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de