converting then outputting varbinary(max) FOR XML PATH

Question

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!

• Pieter Geerkens: If the text data comprises distinct lines of reasonable length, simply break on those lines and output as distinct XML pieces (either attributes or entities as is consistent with the remainder of your XML). If distinct lines do not already exist, I would simply break the text data into artificial lines of 65 characters (for easy reading) and do the same.
• JShean: I should also say its sql server 2005
• Aaron Bertrand: Are you trying to put the varbinary values (e.g. 0x486921) into XML as strings (so you would have <tag>0x486921</tag>, or are you trying to convert them to their string representation first (e.g. 0x486921 => <tag>Hi!</tag>)?
• JShean: I'm sorry to clarify: I would love to export from column value: 0x486921 to <tag>Hi!</tag>.

Answer 1

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.

• JShean: Thanks! In this case its utf-8 encoded strings. I think I may have to try to create a UDF function...
• Aaron Bertrand: SQL Server doesn't have direct UTF-8 support, so you may have to deal with this on the other end anyway...
• JShean: So close...I installed a C# UDF to do the conversion pre-markup, but I'm getting a strange trailing null character in SQL Server's XML output (not in the data). <row> <factoryid>9FAC6D5B-02D8-458D-8843-507288593B9D</factoryid> <name>Object_Credit Line</name> <datatype>10</datatype> <datautf>Gift of Mrs. J. Pierpont Morgan, 1921&#x0;</datautf> <datahex>0x47696674206f66204d72732e204a2e2050696572706f6e74204d6f7267616e2c203139323100</datahex> <datavalue>R2lmdCBvZiBNcnMuIEouIFBpZXJwb250IE1vcmdhbiwgMTkyMQA=</datavalue> </row>
• Aaron Bertrand: @JShean How are you so sure it's not in the data? SQL Server doesn't just add random data "for you"...
• JShean: Good point. I'll dig deeper.