x

Query XML using a cursor style approach

Hello,

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 @open_xml XML
SET @open_xml = '<DataDTOs>
 <DataDTO>
  <UserId>123456789</UserId>
  <ItemID>0</ItemID>
 </DataDTO>
 <DataDTO>
  <UserId>112456789</UserId>
  <ItemID>10</ItemID>
 </DataDTO>
 <DataDTO>
  <UserId>123456129</UserId>
  <ItemID>20</ItemID>
 </DataDTO>
 <DataDTO>
  <UserId>120056789</UserId>
  <ItemID>444</ItemID>
 </DataDTO>
</DataDTOs>'
DECLARE @userid nvarchar(255) 
SELECT @userid = 
  tab.col.value('UserId[1]','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!

Thanks

Ben

more ▼

asked Oct 20, 2009 at 12:20 PM in Default

Ben gravatar image

Ben
41 1 1 1

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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.

DECLARE @UserIdTable TABLE (UserId int)INSERT INTO @UserIdTable (UserId) SELECT tab.col.value('UserId[1]','VARCHAR(20)') FROM @open_xml.nodes('//DataDTO') tab(col) 
more ▼

answered Oct 20, 2009 at 01:26 PM

Tom Staab gravatar image

Tom Staab
5.8k 6 8 10

(comments are locked)
10|1200 characters needed characters left

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:

DECLARE @userid nvarchar(255)
DECLARE CurName CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
SELECT tab.col.value('UserId[1]','VARCHAR(20)')
FROM @open_xml.nodes('//DataDTO') tab(col)

OPEN CurName

FETCH NEXT FROM CurName INTO @userid

WHILE @@FETCH_STATUS = 0
BEGIN

    -- Do some stuff

    FETCH NEXT FROM CurName INTO @userid
END

CLOSE CurName
DEALLOCATE CurName

This cursor is the fastest one.
I don't have enough info for a loop, but the structure is:

WHILE (Some Limiting Factor)
BEGIN
    -- Do Some Stuff
END

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.

more ▼

answered Oct 20, 2009 at 12:58 PM

dvroman gravatar image

dvroman
1.1k 2 2

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Oct 20, 2009 at 03:00 PM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
101k 19 21 74

Exactly. And if you're not sure how to convert your idea or code from cursor-based to set-based, just post another question. :) I'm a SQL geek who loves figuring out those sorts of problems, and I know I'm not the only one here who feels that way.
Oct 20, 2009 at 03:23 PM Tom Staab
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x344
x150

asked: Oct 20, 2009 at 12:20 PM

Seen: 2879 times

Last Updated: Oct 20, 2009 at 12:30 PM