TSQL – Passing a list to a Stored Procedure

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.

Per diverse esigenze, spesso mi è capitato di dover inviare una lista di valori ad una Stored Procedure.
Per raggiungere questo obiettivo è possibile escogitare un workaround mediante la funzione TSQL nodes(XQuery) sui tipi di dato XML.

Vediamo due esempi per comprendere al meglio la soluzione proposta.

Esempio 1: Come inviare ad una Stored Procedure una lista di ID.

1 – Costruisco un XML che contiene la lista degli ID, come di seguito:

<MyIDList>
<ID>1</ID>
<ID>2</ID>
</MyIDList>

In C# posso costruire l’XML nel modo seguente:

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 – Invio alla Stored Procedure l’XML costruito.

In C# creo un DbParam come di seguito:

//@MyIDListXML
DbParameter dbParam = dbCommand.CreateParameter();
dbParam.ParameterName = "@MyIDListXML";
dbParam.DbType = DbType.Xml;
dbParam.Value = xmlDoc.InnerXml;  //Nota passo l'InnerXml
dbCommand.Parameters.Add(dbParam);

3 – Recupero dal parametro @MyIDListXML la lista di ID in formato relazionale per poter effettuare qualsiasi tipo di operazione (JOIN, WHERE, etc.)

SELECT T.Item.value('.' , 'BIGINT') AS ID
FROM @MyIDListXML.nodes('//MyIDList/ID') AS T(Item)

Il risultato sarà una tabella con due valori, come mostrato in figura:

Esempio 2: Come inviare una tabella ad una Stored Procedure.

1 – Costruisco un XML che rappresenta la tabella da inviare:

<MyList>
<item attributo1="value11" attributo2="value12" />
<item attributo1="value21" attributo2="value22" />
</MyList>

In C# posso contruire l’XML nel modo seguente:

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 – Invio alla Stored Procedure l’XML costruito.

In C# creo un DbParameter come di seguito:

//@MyListXML
DbParameter dbParam = dbCommand.CreateParameter();
dbParam.ParameterName = "@MyListXML";
dbParam.DbType = DbType.Xml;
dbParam.Value = xmlDoc.InnerXml;  //Nota passo l'InnerXml
dbCommand.Parameters.Add(dbParam);

3 – Recupero dal parametro @MyListXML la lista di ID in formato relazionale per poter effettuare qualsiasi tipo di operazione (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)

Il risultato sarà una tabella con i valori come mostrato in figura:

E’ possibile inviare qualsiasi struttura XML e lavorare lato SQL Engine con la funzione nodes(XQuery).