ArrayList as parameter to SQL stored procedure

I actually have to send an XML as a parameter to a SQL stored procedure but I am using SQL 2000 and it does not have XML datatype. The XML is like below

<productlist>
<product>abc3</product>
<product>xyz4</product>
<product>pqr2</product>
</productlist>

abc is the product code and 3 is the quantity. I need to insert product code and quantity in different columns of a table. I think I have two options

(1) Send the XML as varchar(8000) and do the split in the stored procedure.
This seems risky if the XML is more than 8000 characters.

(2) Do the split in my VB.NET application tht calls the stored procedure. Insert product code and quantity in separate arrays and send the arrays to stored procedure as parameters.

Which is the better option? Or is there any other way of doing it? Please advise and let me know how I can do it. Thanks!
Angel02Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Priya SudharsanProgrammer AnalystCommented:
You can go with the 2nd option. You cannot pass an array as a parameter to Stored procedure. Instead, read the XML into a list, read through each element and populate the table directly without calling the stored procedure.

If your stored procedure does any other operations, you can call it once the table is populated.
0
Angel02Author Commented:
Won't it be slow if I insert one row at a time while looping? Is there any other way?
0
Angel02Author Commented:
Also, I have to check if that row already exists before inserting so I might need to have it in a stored procedure.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Randy Knight, MCMPrincipal ConsultantCommented:
You're pretty limited on your options in SQL 2000.  Is this an actual SQL 2000 instance or just a database that is in SQL 2000 compatibility mode?  

If the former, I think you are going to have to stage this data somehow as Priya suggests.  Mainly because you are concerned about it being more than 8000 characters.  If you were sure it would all fit in a varchar(8000) then you could pass it in as a comma-delimited list and then load it into a temp table inside the stored procedure, at which point you could do set operations against it.  If you need to do more with the products than just insert them to a table, load them into a staging table from VB.NET and then call the stored procedure and work with that table.

If its a SQL 2000 database on a 2005+ instance there are some other options we could explore.
0
Anthony PerkinsCommented:
Won't it be slow if I insert one row at a time while looping?
Asbsolutely, Definitely do not do this.

Is there any other way?
There is no reason you cannot pass Xml to a SQL Server 2000 box.  All you have to do is define the parameter in your Stored Procedure as text or ntext.  You can then use OPENXML() to shred the data.

Let me know if you need more help.
0
Priya SudharsanProgrammer AnalystCommented:
Slowness is based on the number of rows that you are inserting.

There is an other option you can go with:

Create a Datatable in the code and populate the data from XML to Datatable.

Use SqlBulkCopy option to BCP in your data directly to the database table. This will be faster.
0
Randy Knight, MCMPrincipal ConsultantCommented:
As you can see, there are lots of ways to skin this particular cat.  I like the OPENXML idea although it's pretty slow in SQL 2000.  So it may or may not be faster than having to loop to load a staging table.  It really depends on how much data you have.  

However you do it, the bottom line is you need to turn your list of products into a set.  Could be a temp table in your procedure via OPENXML, could be a staging table loaded via VB.NET or SQLBULKCOPY, etc.
0
Angel02Author Commented:
I will try the OPENXML and see if it works.
@Randy Knight
It is a SQL 2000 database on a 2005 instance.
0
Randy Knight, MCMPrincipal ConsultantCommented:
In that case what I would do is create a database on the instance to put your sp in (or put in a different database if you have one that makes sense).  Then you can use the xml data type for your parameter and use xquery to shred the xml.  Then just have plain old inserts into the SQL 2000 database.
0
Anthony PerkinsCommented:
>>I will try the OPENXML and see if it works. <<
It is not difficult at all.  If all you want to do is pass a long list to INSERT/UPDATE a permanent table, than pass it in as a text parameter, Use OPENXML and INSERT/UPDATE to do this.  It is at most 3 lines of code:
1. EXEC sp_xml_preparedocument
2. INSERT/UPDATE with OPENXML clause
3.  EXEC sp_xml_removedocument (whatever you do, do not forget this!)

OPENXML can in some cases be faster than the newer Xml Data Type Methods.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Angel02Author Commented:
When I use text or ntext as a parameter, I am getting the following error

The text, ntext, and image data types are invalid for local variables.

Is it not an option for me?
0
Anthony PerkinsCommented:
You can use it as a parameter.  You cannot use it as a local variable.

Let me know if you need an example.
0
Angel02Author Commented:
Yes please. How do I mention it in the stored procedure?
0
Anthony PerkinsCommented:
You create the Stored Procedure like this:
CREATE PROCEDURE usp_ShredXml
			@MyXml text

AS
DECLARE @iDoc integer
		
SET NOCOUNT ON

EXEC sp_xml_preparedocument @iDoc OUTPUT, @MyXml

SELECT	ProductID
FROM	OPENXML(@iDoc, 'productlist/product', 2) WITH
		(ProductID varchar(20) '.')

EXEC sp_xml_removedocument @iDoc

Open in new window

And you execute it like this:
EXEC usp_ShredXml 
	'<productlist>
		<product>abc3</product>
		<product>xyz4</product>
		<product>pqr2</product>
	</productlist>'

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.