Update the values in a SQL Server Databse coulmn of type XML.

[querry]

Hi all,

I am developing a web application where I save a XML file into a SQL Server 2000 database. I do not face any problems in storing to xml file to the database. However, when a updated version of the same xml file is to be stored into the database I cannot do so.

Basically what I am trying to do is check the database for a perticular record. Then if the record is not present I use the INSERT statement to create the new record. But in case a record is present and needs to be updated I cannot use the UPDATE command.

XmlReader xr = XmlReader.Create(Server.MapPath(@"~\XmlFiles\abc.xml"));
SqlConnection connection = new SqlConnection();
connection = "//Some connection string//"
SqlCommand cmd = new SqlCommand();
cmd.Connection = connection;
cmd.CommandText = "INSERT INTO tblXML(ID, Name, xmlFile) VALUES(@ID, @Name, @x)";
cmd.Parameters.Add("@ID", SqlDbType.UniqueIdentifier);
cmd.Parameters.Add("@Name", SqlDbType.VarChar);
cmd.Parameters.Add("@x", SqlDbType.Xml);
cmd.Parameters[0].Value = Guid.NewGuid();
cmd.Parameters[1].Value = txtFormName.Text;
cmd.Parameters[2].Value = new SqlXml(xr);
int i = cmd.ExecuteNonQuery();
 

this works fine. but the below code does not.

cmd.CommandText = "UPDATE tblXML SET xmlFile=@x WHERE //some condition//
cmd.Parameters.Add("@x", SqlDbType.Xml);
cmd.Parameters[0].Value = new SqlXml(xr);
i = cmd.ExecuteNonQuery();

I am using C#.

can any body suggest something?

Many Thanks.

[kunal pawar]

try this one

cmd.Parameters.Add("@x", SqlDbType.Xml);
cmd.Parameters.Value = new SqlXml(xr);
i = cmd.ExecuteNonQuery();

its work