Post reply

Convert Binary Data to XML in a select

  • 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

  • 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!

  • 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. 😎

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply