Search code examples
c#encodingxml-serializationvarbinary

XML in C# to varbinary column in SQL

I have an XmlDocument object that I load from a file.

XmlDocument xmlDoc = new XmlDocument();
xmlDoc.Load("C:\\myxml.txt");

I need to convert this XML Document to a format that is compatible with varbinary in a SQL table. How can I achieve this?

Solution

  • Skip the overhead of loading an XML document and then encoding by just reading the file as bytes directly:

    byte[] data = File.ReadAllbytes("C:\\myxml.txt");

    The above uses System.IO

    Then instert into SQL like so:

    SqlCommand cmd = new SqlCommand("INSERT INTO myTable(myCol) VALUES(@file)", myDbConn);
cmd.Parameters.Add("@file", SqlDbType.VarBinary).Value = data;

cmd.ExecuteNonQuery();