Pass a list of Integers to a Stored Procedure in T-SQL

To pass a list of Integers from .NET C# to a Stored Procedure in your MS SQL Server, take a look on the following article:

  1. Serializer
  2. Create list of Integers
  3. Create Stored Procedure

Serializer

First of all, you need to implement a simple and reliable Object-to-XML Serializer:

 
        public string serializeObjectToXML<T>(T o)
        {
            return SerializeToXMLString<T>(o, Encoding.Unicode, true);
        }

        public string SerializeToXMLString<T>(T XMLObj, Encoding encoding, bool removeNamespace)
        {
            MemoryStream memStrm = new MemoryStream();
            SerializeToXMLString(XMLObj, memStrm, encoding, removeNamespace);
            memStrm.Position = 0;
            var sr = new StreamReader(memStrm);
            return sr.ReadToEnd();
        }

        public void SerializeToXMLString<T>(T XMLObj, Stream s, Encoding encoding, bool removeNamespace)
        {
            try
            {
                XmlSerializer xmlSerializer = new XmlSerializer(typeof(T));
                XmlTextWriter xmlSink = new XmlTextWriter(s, encoding);
                xmlSink.Formatting = Formatting.Indented;

                if (removeNamespace)
                {
                    XmlSerializerNamespaces xs = new XmlSerializerNamespaces();
                    xs.Add("", "");
                    xmlSerializer.Serialize(xmlSink, XMLObj, xs);
                }
                else
                    xmlSerializer.Serialize(xmlSink, XMLObj);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                Console.WriteLine(ex.StackTrace);

                if (ex.InnerException != null)
                    Console.WriteLine(ex.InnerException);
            }
        }

        private static string GetXMLIntList(List<int> JobList)
        {
            Serializer s = new Serializer();
            return s.serializeObjectToXML(JobList);
        }

Create list of Integers

Next step: create a list of Integers and serialize it to an XML-String.

List<int> ListOfInteger = new List<int>() { 1, 2, 3, 4, 5, 100, 101 };
string xml_ListOfInteger = GetXMLIntList(ListOfInteger);

The content of xml_ListOfInteger is now:



  1
  2
  3
  4
  5
  100
  101

Create Stored Procedure

Create your Stored Procedure and declare the XML document as a parameter. The following example returns the list directly.

CREATE PROCEDURE [dbo].[GetValues]
@xml nvarchar(max)
AS
BEGIN
SET NOCOUNT ON;

DECLARE @idoc int;
DECLARE @doc nvarchar(max)
SET @doc = CONVERT(nvarchar(max),@xml);

EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

SELECT Value FROM OPENXML(@idoc, 'ArrayOfInt/int')
WITH (
Value int 'text()'
)
END