Sometimes as a web developer you need to pass more complex data as input to a stored procedure. Take for example, a user profile with a question that has multiple check box answers and a database that stores each answer as a seperate row in a table.
Say our target answer table was created like this:
CREATE TABLE AnswerTable (
AnswerID int Primary Key,
QuestionID int,
AnswerValue varchar(10)
)
We can save multiple answers for a given QuestionID at one time by creating an xml document to represent the answer data and passing it to SQL.
We could create an XML string like this:
string xmlString = "<answers><answer>a</answer><answer>b</answer><answer >c</answer></answers>";
And a store procedure to receive it like this:
CREATE PROCECURE [dbo].[sproc_AddAnswers]
@AnswerXml Xml,
@QuestionID int
AS BEGIN
INSERT INTO AnswerTable SELECT @QuestionID, Item.i.value('.', 'varchar(30)') from @AnswerXml.nodes('answers/answer') as Item(i)
END
Note how Item.i.value selects the value of the node selected by x-path expression 'answers/answer' pass to @AnswerXml.nodes. The nodes() function of the XML type converts XML data to a relational result set.
To send the XML to the stored procedure we do something like this:
//send XML document to SQL server to add answers
using (SqlConnection conn = new SqlConnection(connectionString))
{
SqlCommand myCommand = new SqlCommand("sproc_AddAnswers", conn);
myCommand.CommandTimeout = 1200;
myCommand.CommandType = CommandType.StoredProcedure;
myCommand.Parameters.Add("@AnswerXml", xmlString);
myCommand.Parameters.Add("@QuestionID", 1);
conn.Open();
myCommand.ExecuteNonQuery();
conn.Close();
}