How to add an XML parameter to a stored procedure in C#?

Question

I am developing a C# web application in VS 2008 which interacts with my Adventureworks database in my SQL Server 2008. Now I am trying to add new records to one of the tables which has an XML column in it. How do I do this? This is the error I'm getting:

System.Data.SqlClient.SqlException was caught
  Message="XML Validation: Text node is not allowed at this location, the type was defined with element only content or with simple content. Location: /"
  Source=".Net SqlClient Data Provider"
  ErrorCode=-2146232060
  Class=16
  LineNumber=22
  Number=6909
  Procedure="AppendDataC"
  Server="."
  State=1
  StackTrace:
       at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
       at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
       at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
       at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
       at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
       at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
       at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
       at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
       at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
       at ADONET_namespace.ADONET_methods.AppendDataC(DataRow d, Hashtable ht) in C:\Documents and Settings\Admin\My Documents\Visual Studio 2008\Projects\AddFileToSQL\AddFileToSQL\ADONET methods.cs:line 212
  InnerException: 

And this is a portion of my code in C#:

    try
            {
                SqlConnection conn2 = new SqlConnection(connString);
                SqlCommand cmd = conn2.CreateCommand();
                cmd.CommandText = "dbo.AppendDataC";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = conn2;
...
                sqlParam10.SqlDbType = SqlDbType.VarChar;
                SqlParameter sqlParam11 = cmd.Parameters.AddWithValue("@" + ht["@col11"], d[10]);
                sqlParam11.SqlDbType = SqlDbType.VarChar;
                SqlParameter sqlParam12 = cmd.Parameters.AddWithValue("@" + ht["@col12"], d[11]);
                sqlParam12.SqlDbType = SqlDbType.Xml;
...
                conn2.Open();
                cmd.ExecuteNonQuery(); //This is the line it fails on and then jumps
                                       //to the Catch statement
                conn2.Close();
                errorMsg = "The Person.Contact table was successfully updated!";
            }
            catch (Exception ex)
            {

Right now in my text input MDF file I have the XML parameter as:

'<Products><id>3</id><id>6</id><id>15</id></Products>'

Is this valid format for XML?

Answer 1

When you specify SqlDbType.Xml, the value of the parameter must be an instance of the SqlXml class:

sqlParam12.Value = new SqlXml(...);

If you want to set an XML string into an XML column (without instantiating SqlXml), you can set the type of the parameter to SqlDbType.VarChar -- which isn't actually necessary when you call AddWithValue -- and it will be implicitly converted, assuming the XML is well-formatted (yours is).