varbinary and FOR XML clause

Question

How can I store a value returned by COLUMNS_UPDATED() in the xml message? I wanna later get it back to the varbinary(max) variable and perform some operations.

The following code works: SET @COLUMNS_UPDATED = @xmlmessage.value('xs:base64Binary((/ColumnsUpdated)[1])', 'varbinary(max)'). In case when I didn't specify the type (xs:base64Binary) it returned 0x0 — Marian Zagoruiko, May 24 '12 at 13:07

score 2 · Accepted Answer · answered May 23 '12 at 09:22

Adding a varbinary value into an XML type stores it as base64 encoded, and extracting the value already knows how to decode it:

declare @v varbinary(1000) = 0x0102030405;
declare @x xml;
set @x = (select @v as value for xml path ('message'), type);
select @x, @x.value(N'(/message/value)[1]', N'varbinary(1000)');

varbinary and FOR XML clause

1 Answers1