OPENXML and image data type problem

Question

OPENXML and image data type problem

Alexander Chacon

Right there with Babe

Points: 797
More actions
September 5, 2005 at 11:25 am

#89603

Hello all
I've been trying to use OPENXML from a C# .net APP to insert rows in a more efficient way. However, one of the columns is of image data type.
The XML file generated contains the column, fully encoded in base64, but for some reason, openxml inserts an incorrect value for this column in the table.
When I test the result in query anazyler, I can see that the column (expressed in HEX) does not contain what it should. I know that the base64 string is correct because I've re-converted it using .NET, and also have had SQL server output a correct row in XML using FOR XML.
This is an example of what I am doing...
***************************************************
DECLARE @idoc int
DECLARE @doc varchar(8000)
SET @doc ='

1
9/2/2005
C
UEsDBBQAAAAIAIeDIjOB14NTJQAAACMAAAAAAAAAC8lILUpVyCxWyMtXSC4tKkrNK1FITC5JyslITExUyCzLLKkEAFBLAQIUABQAAAAIAIeDIjOB14NTJQAAACMAAAAAAAAAAAAAAAAAAAAAAAAAAABQSwUGAAAAAAEAAQAuAAAAQwAAAAAA

'
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
SELECT * FROM OPENXML (@idoc, '/ROOT/AERO_CurrentStatement',2)
WITH (AccountId int, StatementDate datetime, LocalSymbol char(4), CompressedText image)
EXEC sp_xml_removedocument @idoc
************************************************
The resulting CompressedText column in QA looks like this: 0x550045007300440042004200510041004100410041004900410049006500440049006A004F004200310034004E0054004A00510041004100410043004D00410041004100410041004100410041004100430038006C0049004C00550070005600790043007800570079004D007400580053004300340074004B006B0072004E
and it should like something like this
0x504B03041400000008002B60093358AF862C73030000840B000000000000AD965D6FDA481486EF91F80F476AAF566234DFE3C91D01B74B944285D9BD5D4D6194BA013BB28D56E4D7F718B361629BD2447BA24813E7F8F17BBE6606AEDA385E2E26E364767F3F4E202163D2E3F3D53D7860C301FC96252E83BBBCF4BFE39F54
I have no idea what it could be, I've tried different encondings... and many other things, any suggestiongs?
Thanks in advance
Alex

Viewing 4 posts - 1 through 3 (of 3 total)

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

Alexander Chacon Right there with Babe Points: 797 More actions · Answer 1

I just realized my message was stripped from all the XML element tags... anyway the base 64 string can still be seen.

I think SQL is simply replacing each character in the string with it's utf-8 equivalent, and not doing any conversion from the base64 format...

Any ideas why this is happening?

Alexander Chacon Right there with Babe Points: 797 More actions · Answer 2

Nevermind people, I just realized OPENXML is not capable to do a direct conversion from base64 to a binary type. That's really annoying, you know... if FORXML is capable of generating binary types as base64, they should let you know you can't do it the other way around with openxml, I just wasted 2 good days investigating.

Gift Peddie SSC Guru Points: 73570 More actions · Answer 3

Try the link below for sample code using OPENXML. Hope this helps.

http://msdn.microsoft.com/msdnmag/issues/05/06/DataPoints/default.aspx

Kind regards,

Gift Peddie

Kind regards,
Gift Peddie