Sql-server – converting then outputting varbinary(max) FOR XML PATH

sql serversql-server-2005varbinaryxml

I have a vendor application that has stored some of its text data in a varbinary(max) column. I am exporting this data (from several tables with different data types), ideally using FOR XML PATH. What is the best strategy for converting this column's data prior/during (function? convert?) export to XML.

Thanks!

Best Answer

Don't know that you're going to get anything much simpler than this:

DECLARE @x TABLE(a VARBINARY(64));

INSERT @x(a) 
SELECT 0x480069002100 
UNION ALL 
SELECT CONVERT(VARBINARY(64),(REPLICATE(N'x',32)));

SELECT CONVERT(NVARCHAR(32), a) FROM @x FOR XML PATH('tag');

Results:

<tag>Hi!</tag>
<tag>xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx</tag>

I'll leave all the other XML arrangement you may want to do with the output, but essentially IMHO you should convert to (the right!) string output before putting the data anywhere near XML.

Related Question