How to pass multiple records to stored procedure in

There are three main ways to pass multiple records to the stored procedure.

  • Comma-delimited string:  It is suitable for the scenario where you need to pass single column value
  • XML document: You need to support SQL Server 2005, and you’ve multiple column values.
  • Table-valued parameter: You’ve SQL Server 2008 or higher, and you don’t want to get into parsing stuff at the backend and want the best performance.

To know what is a table-valued parameter and how to pass a data table to stored procedure and implement upsert (update + insert) logic using Merge Statement, check out an old post here.

We’ll see in this post how you can pass an XML document to a stored procedure, parse it in a stored procedure, and insert it into the table.

First, check out how you can generate XML Documents in using XmlSerializer.

Let’s say we have a list of Person class, and we want to serialize it to XML, then do as below:

List people = new List {
new Person { FirstName = "Scott", LastName = "Guthrie", Age = 32 },
new Person { FirstName = "Bill", LastName = "Gates", Age = 50 },
new Person { FirstName = "Susanne", LastName = "Guthrie", Age = 32 }

string strXML = SerializeObject(people);

And the function that serializes the object to XML is as below :

public string SerializeObject(T Obj)
    string strxml = string.Empty;
    using (StringWriter sw = new StringWriter())
        XmlSerializer xs = new XmlSerializer(typeof(T));
        xs.Serialize(sw, Obj);
        strxml = sw.ToString();
    return strxml;

And the resultant XML would be as below :

<?xml version="1.0" encoding="utf-16"?>
<ArrayOfPerson xmlns:xsi="" xmlns:xsd="">

Generated XML has encoding UTF-16. Now pass the above XML to your stored procedure as shown below:

string connectionString = Convert.ToString(ConfigurationManager.ConnectionStrings["XMLDBConnString"]);

using (SqlConnection cn = new SqlConnection(connectionString))
  using (SqlCommand cm = new SqlCommand("SaveUser", cn))
     cm.CommandType = CommandType.StoredProcedure;

     cm.Parameters.Add("@UsersXml", SqlDbType.NVarChar, -1).Value = strXML;



If you see the parameter datatype, it is Nvarchar as generated XML has encoding UTF-16. If you need to support Unicode characters, then you don’t need to change anything, or if you need to encode to UTF-8, then you can use UTF8StringWriter class  shown below:

public class Utf8StringWriter : StringWriter
   public override Encoding Encoding
      get { return Encoding.UTF8; }

Note: If you pass XML having encoding UTF-16 with varchar datatype to the stored procedure, it will throw an error: unable to switch the encoding

To avoid error, use Utf8StringWriter class instead of StringWriter, use varchar datatype, or use nvarchar datatype and pass XML with encoding UTF-16.

Now to parse generated element-centric XML, we need to use Xquery. If you’re new to Xquery, I suggest you go through the links below, or you can skip them if you wish.

Xpath Syntax
Get Table From XML

Check out the sample stored procedure :

ALTER PROCEDURE [dbo].[SaveUser]

SELECT @XML = @UsersXml

INSERT INTO Person (FirstName, LastName, Age)
select M.Item.value('FirstName[1]','VARCHAR(50)'),
FROM @xml.nodes('/ArrayOfPerson/Person') AS M(Item)

Note: XQuery is case-sensitive; hence check your tsql statements for typos if you don’t get the expected output.

That’s all you need; I hope it helps! Your comments are most welcome. 🙂






2 responses to “How to pass multiple records to stored procedure in”

  1. Khurram Avatar

    Thanks for the detailed post. Very handy.

  2. Nilesh Thakkar Avatar

    Welcome, I'm glad that it helped you 🙂

Leave a Reply

Your email address will not be published. Required fields are marked *

%d bloggers like this: