reading image data

Question

reading image data

NJ-DBA

SSChampion

Points: 13834
More actions
January 31, 2011 at 11:05 am

#148326

I am tasked with "making data available" from an old application which is totally unrecoverable.
The data was in a mysql database which I was able to extract into SQL Server, just because I'm more familar there. Almost all of the data is available, except for some data which was BLOB data in mysql and is now "image" data in SQL Server.
when I select against the table, the image data looks like this:
0x3C3F786D6C20... about a million letters and numbers.... 2F786
I learned that this data is actually a stored .xml file. Using an out of the box web page builder (asprunner) I was able to download this "file" and read it as an actual .xml file.
My goal is to take this data and put it back into a column of .xml datatype so that by a simple "select", I would be able to see the actual xml information.
Is there some SQL statement that can do whatever AspRunner is doing and convert this hex-like string into readable xml information?
Any info, advice, tips, or witchcraft is greatly appreciated.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

I'm assuming "cast(column as XML)" has been tried and didn't work. If so, I'd assume that SQL Server won't do this for you, and the third-party tool will be needed.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

NJ-DBA SSChampion Points: 13834 More actions · Answer 2

yes- cast gives:

Msg 529, Level 16, State 2, Line 1

Explicit conversion from data type image to xml is not allowed.

I did find a method to output it to a file and then can import the file back in like this:

--output to file:

DECLARE @objStream INT

DECLARE @imageBinary VARBINARY(MAX)

SET @imageBinary = (SELECT top (1) data FROM dbo.type_sheet)

DECLARE @filePath VARCHAR(8000)

SET @filePath = 'd:\test.xml'

EXEC sp_OACreate 'ADODB.Stream', @objStream OUTPUT

EXEC sp_OASetProperty @objStream, 'Type', 1

EXEC sp_OAMethod @objStream, 'Open'

EXEC sp_OAMethod @objStream, 'Write', NULL, @imageBinary

EXEC sp_OAMethod @objStream, 'SaveToFile', NULL,@filePath, 2

EXEC sp_OAMethod @objStream, 'Close'

EXEC sp_OADestroy @objStream

--import to column:

CREATE TABLE XmlImportTest

(

xmlFileName VARCHAR(300),

xml_data xml

)

GO

DECLARE @xmlFileName VARCHAR(300)

SELECT @xmlFileName = 'd:\test.xml'

-- dynamic sql is just so we can use @xmlFileName variable in OPENROWSET

EXEC('

INSERT INTO XmlImportTest(xmlFileName, xml_data)

SELECT ''' + @xmlFileName + ''', xmlData

FROM

(

SELECT *

FROM OPENROWSET (BULK ''' + @xmlFileName + ''' , SINGLE_BLOB) AS XMLDATA

) AS FileImport (XMLDATA)

')

GO

SELECT * FROM XmlImportTest

DROP TABLE XmlImportTest

so I think I can figure out a way to loop this through all the records....

GSquared SSC Guru Points: 260824 More actions · Answer 3

I just tried this:

DECLARE @XML XML = '<row ID="1" /><row ID="2" />', @Bin VARBINARY(MAX);

SELECT @XML;

SELECT @Bin = CAST(@XML AS VARBINARY(MAX));

SELECT CAST(@Bin AS XML);

From that, I tried this:

CREATE TABLE dbo.DropMe (

ID INT IDENTITY PRIMARY KEY,

Img IMAGE);

INSERT INTO dbo.DropMe (Img)

VALUES (0xFFFE3C0072006F0077002000490044003D002200310022002F003E003C0072006F0077002000490044003D002200320022002F003E00);

SELECT CAST(CAST(Img AS VARBINARY(MAX)) AS XML), Img

FROM dbo.DropMe;

It worked for me.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

NJ-DBA SSChampion Points: 13834 More actions · Answer 4

Thanks a ton! that's exactly what I was looking for!

GSquared SSC Guru Points: 260824 More actions · Answer 5

You're welcome.

I actually learned about that from an SQL injection technique that was in heavy use a few years back. Was hiding the SQL commands inside a binary string, then converting to text and executing it. Bypassed a lot of the injection-proofing where people search for specific keywords in input strings.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon