For different needs, often I had needs to send a list of values to a Stored Procedure.
To achieve this goal it is possible to use a workaround with the TSQL function nodes(XQuery) on the types of XML data.
We’ll see two examples to better understand the proposed solution.
Example 1: How to send to a Stored Procedure a list of ID.
1 – Build an XML that contains a list of ID, as follows:
<MyIDList>
<ID>1</ID>
<ID>2</ID>
</MyIDList>
In C # I can build XML as follows:
XmlDocument xmlDoc = new XmlDocument();
XmlElement xElem;
//add root node
XmlElement xRoot = xmlDoc.CreateElement("MyIDList");
xmlDoc.AppendChild(xRoot);
//Add ID 1
xElem = xmlDoc.CreateElement("ID");
xElem.InnerText = "1";
xRoot.AppendChild(xElem);
//Add ID 2
xElem = xmlDoc.CreateElement("ID");
xElem.InnerText = "2";
xRoot.AppendChild(xElem);
2 – Sent the XML built to the Stored Procedure.
In C# create DbParam as follows:
//@MyIDListXML
DbParameter dbParam = dbCommand.CreateParameter();
dbParam.ParameterName = "@MyIDListXML";
dbParam.DbType = DbType.Xml;
dbParam.Value = xmlDoc.InnerXml; //Note you need to pass the InnerXml Value
dbCommand.Parameters.Add(dbParam);
3 – Get the @MyIDListXML parameter value and obtain the list of ID in a relational format to do a lot of relational operations (JOIN, WHERE, etc.)
SELECT T.Item.value('.' , 'BIGINT') AS ID
FROM @MyIDListXML.nodes('//MyIDList/ID') AS T(Item)
The result will be a table with two value like in figure:

Example 2: Hot to send a table to a Stored Procedure.
1 – Build an XML which represents a table like the following:
<MyList>
<item attributo1="value11" attributo2="value12" />
<item attributo1="value21" attributo2="value22" />
</MyList>
In C# I can create the XML as follows
XmlDocument xmlDoc = new XmlDocument();
XmlElement xElem;
//add root node
XmlElement xRoot = xmlDoc.CreateElement("MyList");
xmlDoc.AppendChild(xRoot);
//add item 1
xElem = xmlDoc.CreateElement("item");
xElem.SetAttribute("attributo1", "value11");
xElem.SetAttribute("attributo2", "value12");
xRoot.AppendChild(xElem);
//add item 2
xElem = xmlDoc.CreateElement("item");
xElem.SetAttribute("attributo1", "value21");
xElem.SetAttribute("attributo2", "value22");
xRoot.AppendChild(xElem);
2 – Sent to the Stored Procedure the XML built
In C# create DbParameter as follows:
//@MyListXML
DbParameter dbParam = dbCommand.CreateParameter();
dbParam.ParameterName = "@MyListXML";
dbParam.DbType = DbType.Xml;
dbParam.Value = xmlDoc.InnerXml; //Note you need to pass the InnerXml Value
dbCommand.Parameters.Add(dbParam);
3 – Get @MyListXML list values and I can do a lot of relational operations (JOIN, WHERE, etc.)
SELECT T.Item.value('@attributo1' , 'varchar(10)') AS Attributo1,
T.Item.value('@attributo2' , 'varchar(10)') AS Attributo2
FROM @MyListXML.nodes('//MyList/item') AS T(Item)
The result will be like in the following table:

It’s possible to pass any XML structure and work on SQL Engine with the nodes(XQuery) function.