Create XML in ASP.NET 2.0 then use for joined table in SQL Server 2005 Stored Procedure

[news.sbcglobal.net]

Here's my problem:

I'm developing an ASP.NET 2.0 application that has a user select one or more
auto manufacturers from a listbox ("lstMakes"). Once they do this, another
listbox ("lstModels") should be filled with all matching models made by the
selected manufacturers. If lstMakes was not multi-select, I'd have no
problem. But in this case it has to be multi-select. The database is SQL
Server 2005 which does not accept arrays as parameters. I've been told that
I have to create an XML document that will act as a filtered Manufacturers
table that I can join to my Models table in my stored procedure. Problem is
I don't have the foggiest idea how to do this. I've seen some examples that
just leave me scratching my head so I was hoping someone could look at what
I'm trying to do and show me how to do this. Thanks!

 

[Uri Dimant]

Hi
Another way is to write an UDF that does split on your parameters
Take a look at Erland's example

CREATE PROCEDURE get_company_names_inline
@customers nvarchar(2000)
AS
SELECT C.CustomerID, C.CompanyName
FROM Northwind..Customers C
JOIN inline_split_me(@customers) s ON C.CustomerID = s.Value
go
---Usage
EXEC get_company_names_inline 'ALFKI,BONAP,CACTU,FRANK'

---The function does split

CREATE FUNCTION inline_split_me (@param varchar(7998)) RETURNS TABLE AS
RETURN(SELECT substring(',' + @param + ',', Number + 1,
charindex(',', ',' + @param + ',', Number + 1) -
Number - 1)
AS Value
FROM Numbers
WHERE Number <= len(',' + @param + ',') - 1
AND substring(',' + @param + ',', Number, 1) = ',')

SELECT TOP 8000 Number = IDENTITY(int, 1, 1)
INTO Numbers
FROM pubs..authors t1, pubs..authors t2, pubs..authors t3

drop table numbers
drop function inline_split_me
drop proc get_company_names_inline

 

[Erland Sommarskog]

I'm developing an ASP.NET 2.0 application that has a user select one or
more auto manufacturers from a listbox ("lstMakes"). Once they do this,
another listbox ("lstModels") should be filled with all matching models
made by the selected manufacturers. If lstMakes was not multi-select,
I'd have no problem. But in this case it has to be multi-select. The
database is SQL Server 2005 which does not accept arrays as parameters.
I've been told that I have to create an XML document that will act as a
filtered Manufacturers table that I can join to my Models table in my
stored procedure. Problem is I don't have the foggiest idea how to do
this. I've seen some examples that just leave me scratching my head so
I was hoping someone could look at what I'm trying to do and show me how
to do this. Thanks!

The good news is that XML is just one way to skin the cat, so if you
don't know how to create XML documents, you can sleep over that part
for now. Look at
http://www.sommarskog.se/arrays-in-sql.html#iterative for what is the
simplest method.

However, for more comlpex scenarios where you need to send down an
"array" of structure data, XML is the best apparoch. So you may want
learn how to form XML documents for future use anyway.


--
Erland Sommarskog, SQL Server MVP, (e-mail address removed)

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx