Insert object serialized as xml into sql server 2005 xml column

[Andy B]

I got an object to serialize into an xml file. Now, instead of putting it
into an xml file, I need it to go to and from an xml database column. How do
you do this?

 

[Marc Gravell]

At the simplest level, by passing the string of the xml into the
database, through whatever data-access approach you are using - making
sure to use a parameterised command (not string concatenation for the
command). If you let us know what database and data-access method you
are using, we might be able to give more context.

Marc

 

[Andy B]

I would be using a sql server 2005 database. Since I haven't messed around
with sql server 2005 projects in vs2008, I will probably attempt to do
something with that. I kind of like the idea of having the stored procs and
stuff in dll files on the database, so I will probably want to access
something like that from a design time created dataSet. As far as the
contract itself goes, It will probably not need to be validated at the
database level other than if it has been inserted the right way or not. Is
this the stuff you were wanting me to give?

 

[Marc Gravell]

Kinda; so if you are a DataSet fan, you should just be able to treat is
as a string column on a table; at that point it makes very little
difference that is is xml.

Marc

 

[Andy B]

But what about the xml dataType? That is what one of the columns is set up
for. So, I should be able to serialize the object to a string variable,
insert that string into the database as is? Then when it comes time to
reload the object, I should be able to select the xml into a string variable
and then deserialize the string variable back into the object in question?

Or would a better way be to serialize the object to an XmlDocument type and
insert the XmlDocument variable into the database? Guess both methods would
be good to look at.

 

[Marc Gravell]

The SQL server will usually cast between xml and varchar / string quite
happily; this is usually the simplest way to talk xml to the database.

Whether you *use* the xml type depends on a few factors; if it is *just*
storage, then the only benefit is that it verifies that the xml is
well-formed. However, using the xml data-type has the overhead of
shredding the data, so will be more expensive if you have lots of
updates. In this case, varbinary(max) might be a cheaper option, as it
allows you to store the data without the cost of parsing it.

However; on the other side, the xml type allows you to enforce schema
(if you fancy), to query inside the entity directly with TSQL (including
indexing), and allows you to promote key expressions (/order/@orderNo
for example) to persisted, index columns for blazing performance.

So only your project can determine whether the xml type is suitable!

Marc