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
– Joel, Commented Jul 8, 2011 at 12:41

Marc Gravell · Accepted Answer · 2011-07-08 12:42:49Z

1

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();
}

answered Jul 8, 2011 at 12:42

Marc Gravell

1.1m273 gold badges2.6k silver badges3k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Joel Over a year ago

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.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related