Store a byte[] stored in a SQL XML parameter to a varbinary(MAX) field in SQL Server 2005. Can it be done?
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开发者_JAVA技巧[]. Should I use the String type for ImageBinary too ? Would I then need to specially encode that string somehow ?
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
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
精彩评论