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!

Improve this question
4
  • 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. Commented Aug 24, 2014 at 13:22
  • I should also say its sql server 2005 Commented Aug 24, 2014 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>)? Commented Aug 24, 2014 at 19:44
  • I'm sorry to clarify: I would love to export from column value: 0x486921 to <tag>Hi!</tag>. Commented Aug 25, 2014 at 0:46

1 Answer 1

Reset to default
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.

Improve this answer
6
  • Thanks! In this case its utf-8 encoded strings. I think I may have to try to create a UDF function... Commented Aug 25, 2014 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... Commented Aug 25, 2014 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> Commented Aug 26, 2014 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"... Commented Aug 26, 2014 at 23:06
  • Good point. I'll dig deeper. Commented Aug 26, 2014 at 23:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.