Nicola Celiento

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).