Saving XML Files to Database

eslater posted on Wednesday, August 23, 2006

How would I go about saving/retrieving files from a SQL Server database?  I have tried using .GetBytes but cant seem to get it working correctly.

Thanks for any help you can provide

Elliot

Michael Hildner replied on Wednesday, August 23, 2006

What version of Sql Server you using? There's a new xml data type in 2005. I've used that, and also just a varchar column. Either way you can treat it as a string in your BO.

Regards,

Mike

figuerres replied on Wednesday, August 23, 2006

eslater:

How would I go about saving/retrieving files from a SQL Server database?  I have tried using .GetBytes but cant seem to get it working correctly.

Thanks for any help you can provide

Elliot

 

here is a fragment of code:

               dbCommand.Connection = dbConnection;
               dbCommand.CommandText = "INSERT INTO OrderLog (EventClass,Data,Link) VALUES(@EventClass,@Data,@Link)";
               SqlParameter firstParameter = dbCommand.Parameters.Add(new SqlParameter("@EventClass", System.Data.SqlDbType.NVarChar, 50));
               SqlParameter secondParameter = dbCommand.Parameters.Add(new SqlParameter("@Data", System.Data.SqlDbType.Xml));
               SqlParameter thirdParameter = dbCommand.Parameters.Add(new SqlParameter("@Link", System.Data.SqlDbType.UniqueIdentifier));
               firstParameter.Value = "Request";
               thirdParameter.Value = Link;

               XmlSerializer s = new XmlSerializer(typeof(OrderLog));
               StringWriter Output = new StringWriter(new StringBuilder());
               s.Serialize(Output, OL);
               secondParameter.Value = new SqlXml(new XmlTextReader(Output.ToString(), XmlNodeType.Document, null));
               dbCommand.ExecuteNonQuery();

 

with 2005 you can query the xml data w/o a need to do the bytes stuff...

hmm I can't find my sample right now but as I recall a normal query gets the data and an x-path thing will pull out a selected bit of it.