problem updating table with varbinary(max) field from xml

[quintonmartin]

Here's my problem.

I have a client application that uses a web service for data access. I can read/update many records in many tables, however, I am not getting what I expect for a varbinary(max) field I am trying to update.

Here's how I'm doing it.

I have a strongly typed dataset in c#. The column in question is defined as

System.Byte[] LogoImage,

for a simple test, I store a single byte with a zero value in this column. In the debugger, it looks correct.

I then call myDataSet.GetXm l() to convert to XML so I can send to my web service.

When I look at the XML string getting sent to the web service the column looks like this:

<LogoImage>AA== </LogoImage>

When I step into my web service code, I still see the xml looking the same:

<LogoImage>AA== </LogoImage>

I then eventually call a stored procedure to update the record. It passes the xml string as a parameter to the stored procedure:

--Here are the relevent parts of the stored procedure
--with other fields cut out so as not to confuse

ALTER PROCEDURE [dbo].[usp_myUpdateRou tine_u]
-- Add the parameters for the stored procedure here
@InstitutionRec ID int = 0,
@UserID varchar(20) = NULL,
@XML xml = NULL

-- then it later does this code to do the update

EXEC sp_xml_prepared ocument @idoc OUTPUT, @XML

Update [dbo].[mytable]
set [LogoImage] = xdoc.[LogoImage]

FROM OPENXML (@idoc, 'Data/CheckingAccount Def/CheckMain', 2 )
with (
[LogoImage] varbinary(max)
) xdoc;

EXEC sp_xml_removedo cument @idoc


after the update statement, I can do a select statement on the table and see in that column there is a value of:

0x410041003D003 D00

Any idea what is happening?

Then, when I call another stored procedure to read that record and send back to my client application, the field in XML looks like this:

<LogoImage>QQBB AD0APQA=</LogoImage>


I'm thinking it's some sort of encoding issue.

I'm pretty new to C# and MSSQL, only been using for 4 months.

I would appreciate any thoughts or suggestions.

thanks

Quinton

[quintonmartin]

Ok, I figured out what's going on.

byte[] testing = Convert.FromBas e64String("AA== ");
//testing is now a 1 byte array with a zero.

So, GetXml() is converting the binary data to base64 automatically.

However, in SQL Server, this base64 string is getting encoded AGAIN.

At the bottom of the page at this link, it describes how to convert the base64 string in XML back to binary data to be stored in the database.

http://msdn.microsoft.com/en-us/libr...7(SQL.90).aspx

Quinton