Saving XML from a string to a varbinary column in Sql Server 2005 .NET

Question

Using .NET (in an SSIS package):

I've got an XML string in memory which I need to save to a varbinary column in Sql Server, as an XML file.

It seems like I should be able to avoid actually saving a .xml file to disk, and do all of this in memory, but I'm not sure how. My initial thought was to use the File class in System.IO, but this class seems to require a file on disk.

Is there a way in memory to convert the XML string to its bytes and save those to the DB?

Thanks.

(I'm coding in VB but naturally C# examples are fine too).

I may have asked this too soon. Five minutes later I find: byte[] data = Encoding.UTF8.GetBytes(xmlString); I'm guessing this might be the way to go. — Joel, Jul 08 '11 at 12:41

score 1 · Accepted Answer · answered Jul 08 '11 at 12:42

I would expect that to work fine as a byte[]; so all you need to do is serialize the string. If I make an assumption that the xml is tagged as UTF8 internally (or not tagged at all), then perhaps:

var data = Encoding.UTF8.GetBytes(yourXmlString);

However, note that if your xml is tagged internally with a different encoding to UTF8, the actual encoding should ideally match (otherwise: bad things).

Of course, if you are creating the xml, then you can write it directly to binary and select the encoding at the same time. You can do this by passing a Stream and an Encoding to XmlWriter:

byte[] bytes;
using (var ms = new MemoryStream())
{
    using(var xw = XmlWriter.Create(ms, new XmlWriterSettings { Encoding = Encoding.UTF8 }))
    {
        // write to xw, directly or indirectly
    }
    bytes = ms.ToArray();
}

Thanks Marc, I think this is what I'll end up doing (the first thing). I do need to use the string as a string, so I can't go straight to memory from the XmlWriter, but I do have control over the encoding. — Joel, Jul 08 '11 at 12:51

Saving XML from a string to a varbinary column in Sql Server 2005 .NET

1 Answers1