Have Geek Brain

Store the [] byte stored in the SQL XML parameter in a varbinary (MAX) field in SQL Server 2005. Can I do this?

Store the [] byte stored in the SQL XML parameter in the varbinary (MAX) field in SQL Server 2005. Can I do this?

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 of type [] byte. Should I be using the String type for ImageBinary? Should I then specifically encode this string?

+2


a source to share


2 answers


Assuming you are using Base64 for the [] byte in XML, the approach that XQuery uses 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

+1


a source


Probably no.

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



In this case, you have 2 datasets: name and 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
0


a source






More articles:


All Articles