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

Decoding a binary base64 inside a XML using transact SQL

Yes i just can't find a place to help me out on something.

Everything went well to create the XML using transact SQL command

SELECT TOP 10 * FROM TblEvenement for xml auto, binary base64

But how do decode my image field in my XML so i can save the data into a new table ..

all this as to be done in a store procedure inside SQL-Server 2000 !!

thanx guys !!

***Any good sites or example i can use for !!What i did is a store proc in SQL-Server 2000 that build an XML document so i can transfert data between my two server.

But i have image fields and i would like to transfert that as well. I can encode it no problem ( for xml auto, binary base64 ), but how can i DECODE my TEXT value (int the XML document) using Transact SQL to store my image to the new server !!

thanx|||Did you find any solution to this. I'm really interested.|||¸Yes i build a function in my SQL DataBase that convert my image but works only with images that have less or equal to binary(8000)..

but im looking for something else.. its not working as i would like so.. im keep working on it.

still need help guys !!!

not a single site in the web that have an example..

there must be a way !! or its to easy so no buddy wants to help me out..|||Does this post help you?OPENXML and image binary base64.

The approach here is to create a UDF which will convert base64 to binary, and use this function with OPENXML.

Terri|||I try the function but the problem is that it return a varbinary(8000) .. its to short.. all my images are over 10K

so im trying something else.. must be a way

thanx

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