SQL 2005 & 2008 - Passing complex data sets with the XML type

mbohn — Thu, 13 Nov 2008 22:02:00 GMT

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();
   }

Matt’s Blog : SQL 2005

SQL 2005 & 2008 - Passing complex data sets with the XML type