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
