Store a byte[] stored in a SQL XML parameter to a varbinary(MAX) field in SQL Server 2005. Can it be done ?

Question

Store a byte[] stored in a SQL XML parameter to a varbinary(MAX) field in SQL Server 2005. Can it be done ?

Here's my stored procedure:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER   PROCEDURE [dbo].[AddPerson]
 @Data AS XML
AS  
 INSERT INTO Persons (name,image_binary)
 SELECT 
  rowWals.value('./@Name', 'varchar(64)') AS [Name],
  rowWals.value('./@ImageBinary', 'varbinary(MAX)') AS [ImageBinary]
 FROM 
  @Data.nodes ('/Data/Names') as b(rowVals)

 SELECT SCOPE_IDENTITY() AS Id

In my schema Name is of type String and ImageBinary is o type byte[]. Should I use the String type for ImageBinary too ? Would I then need to specially encode that string somehow ?

Answer 1

Probably not.

XML is alphanumeric (eg codes > 32 basically) where byte[] would be 0->255.

In this case, you have 2 sets of data: a name and a BLOB. So treat them as such, no?

ALTER  PROCEDURE [dbo].[AddPerson]
 @Name AS varchar(64)
 @Data AS varbinary(max)
AS  
 INSERT INTO Persons (name, image_binary)
 VALUES (@Name, @Data)
 SELECT SCOPE_IDENTITY() AS Id
GO

Answer 2

Assuming that you use Base64 for the byte[] in XML, the approach which uses XQuery as described in the following article should work:

http://blogs.msdn.com/sqltips/archive/2008/06/30/converting-from-base64-to-varbinary-and-vice-versa.aspx