Convert Binary Data to XML in a select

• sweetdeal42

  Old Hand

  Points: 341

  More actions

  November 10, 2008 at 11:54 am

  #383342

  Hi,

  I have what may be a very stupid question but here goes...

  We have an application that saves a fillable PDF to a SQL Server 2005 database. The PDF form data is saved in an xFDF format to an XML column in the database. The PDF developer would like to save the data as binary.

  He wants to do this because the PDF utility is refusing to save at all if there is a '&' in any of the form text fields and he feels saving as binary will solve this problem.

  I have already written a T-SQL procedure to select and parse the XML that is currently saved so I can map it to a different system.

  I need to know if I can easily convert the binary file he saves back into the original XML format so I can leverage my existing code.

  I couldn't find any straight forward syntax online to do this and convert didn't seem to work.

  Does anyone have experience with this or know where something is written on how to do it?

  Thanks!

  Maureen

• Lowell

  SSC Guru

  Points: 323522

  More actions

  November 10, 2008 at 12:35 pm

  #896511

  see if this will help;

  here i'm just taking a static value and casting it to binary, then casting it back again.

  i believe that is all you'll have to do; it works for me just fine.

  [font="Courier New"]

  DECLARE @cmds NVARCHAR(MAX)

  DECLARE @obfoo VARBINARY(MAX)

  --convert some text to binary?

  SET @cmds = N'<?xml version="1.0" encoding="utf-8"?><example></example>'

  SET @obfoo = CAST(@cmds AS VARBINARY(MAX))

  SELECT @obfoo

  --convert binary back to text??

  SELECT CAST(@obfoo AS NVARCHAR(MAX) )

  [/font]

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• sweetdeal42

  Old Hand

  Points: 341

  More actions

  November 10, 2008 at 1:55 pm

  #896561

  Thanks very much! I tweaked it a bit and was able to insert my XML to a table as a binary and then select it back as XML.

  This is exactly what i needed. Don't know why I couldn't figure it out for myself but it seems I was always off one step.

  Your knowledge and help are very much appreciated! We are moving forward once again.