Passing Array To Stored Procedures
There are some scenarios; where you want to pass array to stored procedure. In SQL 2000; it was not possible. Different work-around was presented; like passing values as string and then parsing them out; passing data as XML and then using MSXML OLE automation object in SQL Server (sp_OACreate/sp_OADestroy). Both have the pit falls; parsing strings is not very efficient, OLE Automation is not available when SQL Server Kernel is running using NT Fibers (my observation I might be wrong)
With SQL Server 2005; we now have native support of XML. Yesterday my colleague asked about passing array to stored procedure and I remarked that it should be possible now using XML. I myself had not yet explored XML support in SQL so I couldn’t give him examples or references. Later in night; I decided to try it out. It was my first introduction to Yukon's XML support. Here is the sample stored procedure for passing arrays as XML document.
alter
procedure XmlTest (@xml xml) as
select t2.d.value('.', 'varchar(50)')
from @xml.nodes('/root/a') t2(d)
go
declare @@xml xml;
set @@xml = '<root><a>111</a><a>222</a></root>';
exec XmlTest @@xml;
Some more TSQL for my reference
declare
@x xml;
set @x = '<root><a>111</a><a>222</a></root>';
select t2.d.value('.', 'varchar(50)'), --will show 111, 222 (string)
t2.d.query('.'), --will show <a>111</a>, <a>222</a> (xml)
t2.d.query('text()') --will show 111, 222 (xml)
from @x.nodes('/root/a') t2(d)
Please note, ADO.NET v2 has support of XML data type. I haven’t yet tried it; so cannt say much.