0

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!

  • 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. – Pieter Geerkens Aug 24 '14 at 13:22
  • I should also say its sql server 2005 – JShean Aug 24 '14 at 13:47
  • 1
    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>)? – Aaron Bertrand Aug 24 '14 at 19:44
  • I'm sorry to clarify: I would love to export from column value: 0x486921 to <tag>Hi!</tag>. – JShean Aug 25 '14 at 0:46
3

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.

  • Thanks! In this case its utf-8 encoded strings. I think I may have to try to create a UDF function... – JShean Aug 25 '14 at 13:25
  • SQL Server doesn't have direct UTF-8 support, so you may have to deal with this on the other end anyway... – Aaron Bertrand Aug 25 '14 at 13:32
  • 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> – JShean Aug 26 '14 at 22:35
  • @JShean How are you so sure it's not in the data? SQL Server doesn't just add random data "for you"... – Aaron Bertrand Aug 26 '14 at 23:06
  • Good point. I'll dig deeper. – JShean Aug 26 '14 at 23:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.