Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
pucky
Starting Member
3 Posts |
 Posted - 2002-04-04 : 17:43:36
|
| Hey gang, I really need to figure out a way to solve a problem without using a cursor. can anyone help. here's the idea. I realize this code wont work but I think you'll understand what I'm trying to figure out./******************************************************/ WARNING.... FAKE CODE/*******************************************************/UPDATE tblXMLOrders SET bMerged = (exec usp_Merge xmlstring, bReturn)WHERE bMerged = 0--let's just say that the usp_Merge returns 1 when it's done/******************************************/ END OF FAKE CODE :)Ok so you look at that and say "geez you can't do that" What I'm trying to do is figure out a way that I can take a recordset of orders (approx 12000) which have an xmlstring in them that need to be merged into an ordering system. The recordset has pkID, xmlstring, bReturn, bMerged etc...I need to take the xmlstring throw it at the usp_Merge along with bReturn and then update bMerged = 1 when it's done.any dreamy ideas on how to deal with this?Thanks,Pucky Loucks |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-04-04 : 17:56:41
|
What version of SQL Server are you using? SQL 2000 supports XML very nicely, and has an Updategram feature which will probably do exactly what you're looking for. I personally haven't used it, but you can find information on it in Books Online.Now, if you're NOT using SQL Server 2000, let me ask this: are you indivisibly tied to XML format, or are you currently converting an ADO recordset (or some other kind) to XML, and then passing the whole thing?The reason I ask is that you can pass multiple parameters to a stored procedure as comma-separated values (or any delimiter for that matter), and then parse them out into individual pieces/columns/rows. Something like this:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=12538There are a few links within that thread that describe CSV parsing techniques, you should read them through before you get too involved with the rest of that thread or your head might explode  It's a lot to read in one sitting!If that sounds like a reasonable approach, can you give us some more information on your EXACT data structure, the values you want to pass, the table(s) involve (DDL is a HUGE plus), and we'll see if it can work for you. |
 |
|
|
scootermcfly
Yak Posting Veteran
66 Posts |
Posted - 2002-04-04 : 21:52:34
|
| Are you looking for a way to do the XML specific update, or just a way to step through the table?If the tblXMLOrders has a PK that is unique, couldn't you just use a while loop and step over those values one at a time?Scooter McFly |
|
|
|
pucky
Starting Member
3 Posts |
Posted - 2002-04-05 : 12:03:22
|
| Well the stored procedure is using the OPENXML and merging information into tables. The idea of updategrams is out of scope as the information is already in the database. I'm ok with the processing of the XML via OPENXML (it's working awesome) yet the cursor seems to be the problem. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-04-05 : 12:07:35
|
| What I was getting at was this: do you NEED TO USE XML, or is it simply an intermediate data format? If it's only for intermediate data transport, can you use another format instead for this update operation?We can't tell where the problem might be with a cursor UNLESS you post ALLLLLLLL of your code, no matter how huge or complicated it is.Edited by - robvolk on 04/05/2002 12:08:16 |
|
|
|
scootermcfly
Yak Posting Veteran
66 Posts |
Posted - 2002-04-05 : 13:54:42
|
| I agree with robvolk, could you post some code? Hard to help with out looking at the actual prob.Scooter McFly |
|
|
|
|
|
|
|
|
Cursor way too slow......