How to add an XML parameter to a stored procedure in C#?
- by salvationishere
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?
