January 31, 2011 at 11:05 am
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.
January 31, 2011 at 11:13 am
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
January 31, 2011 at 11:30 am
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....
January 31, 2011 at 11:47 am
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
January 31, 2011 at 12:13 pm
Thanks a ton! that's exactly what I was looking for!
February 1, 2011 at 6:59 am
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply