I currently have the following block of SQL 2005 code. What I need to do is import the XML as a bulk operation, however for each record I might need to do some additional processing (maybe insert into a separate table). At the moment I can only query the first item, how can I query the complete data in a cursor style where I look around each DTO?
DECLARE @userid nvarchar(255) SELECT @userid = tab.col.value('UserId','VARCHAR(20)') FROM @open_xml.nodes('//DataDTO') tab(col) select @userid -- Do some stuff -- Get next UserID -- Do some stuff
Any help on this would be great!
I think dvroman's answer is great (and I've already voted for it), but here's something else that might help you. You can create a table variable to store all of the user IDs and then do whatever you want (cursor, joins, etc.) with that simple table.
answered Oct 20, 2009 at 01:26 PM
Since you need to do "stuff" in your insert, it appears that you either want to do a cursor or a loop. The easiest to do is a cursor:
This cursor is the fastest one.
Personally, in this case, I would write a DTS/SSIS package or preferably a .NET app to handle it. That way you can get the best of both worlds. Excelent looping and data filtration without using too much of the SQL memory and cycles to handle the data. It takes the work away from the central server and puts it on a workstation.
answered Oct 20, 2009 at 12:58 PM
Adding on to Tom Staab's answer (which I voted for), instead of trying to loop through the data, why not take the table that you can create and run set based operations against it. Join or insert or what have you based on the criteria you need to check, as a set instead of row-by-agonizing-row.
answered Oct 20, 2009 at 03:00 PM
Grant Fritchey ♦♦