Storing XML dataset in SQL Server 2000

Question

I need to save the DataSet, results of a reporting app, to a table in SQL Server 2000. Do I save the datasets the same way I would store files in the DB, via Byte Array()?

Answer 1

SQL Server 2000 is really not well equipped to handle XML blobs. It neither has the dedicated XML type, nor does it easily support large text in the form of a VARCHAR(MAX) data type. Both are available in SQL Server 2005 and up.

If you're absolutely and definitely stuck on 2000, your best option is to store your XML into a TEXT column (or NTEXT - 2 byte per character - if you need to support non-ASCII type character sets like Asian languages, Hebrew, Arabic etc).

Mind you though : the TEXT column data type is not very useful in that you can't really do much on it without a lot of hassle - you can't even really search or replace something on it. It's a mess. None of the usual string method work on it - it's really a "store-and-forget" kind of blob.....

UPDATE: if you will never need to inspect the contents of the file while it's stored in the database (really?), you could also use an IMAGE data type.

To store the file in SQL Server, do something like this:

private void AddFileToDatabase(string filename)
{
   using(SqlConnection con = new SqlConnection("server=***;database=***;integrated security=SSPI;"))
   {
      string insertStmt = "INSERT INTO dbo.YourTableName(DocFilename, DocContent) VALUES(@FileName, @Content)";

      using(SqlCommand cmd = new SqlCommand(insertStmt, con))
      {
          cmd.Parameters.AddWithValue("@FileName", filename);
          cmd.Parameters.Add("@Content", SqlDbType.Image);

          cmd.Parameters["@Content"].Value = File.ReadAllBytes(filename);

          con.Open();
          int result = cmd.ExecuteNonQuery();
          con.Close();
      }
   }
}

And basically the same thing for the reading out of the blob of bytes from the database. Or you can package this up in a nice stored procedure - totally up to you.

If you use TEXT/NTEXT, you need to store an actual string, e.g. you could not really use a binary file (like Word) - but if you have output from a report as XML or HTML or something, then TEXT/NTEXT would work, too (just use cmd.Parameters.Add("@Content", SqlDbType.Text); in that case).

Answer 2

you can also serialize (binary not xml) your dataset and save in database.