SQL Server querying xml using a cursor

Question

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>
</pre>
< 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

Answer 1

Nothing special, just declare a cursor over the select from @xml:

DECLARE @userid nvarchar(255);

DECLARE crsDTO cursor static forward_only read_only for 
SELECT  
  tab.col.value('UserId[1]','VARCHAR(20)')
FROM @open_xml.nodes('//DataDTO') tab(col)

open crsDTO;
fetch next from crsDTO into @userid;
while 0 = @@fetch_status
begin
 -- do you work
 fetch next from crsDTO into @userid;
end 
close crsDTO;
deallocate crsDTO;

The usual warnings apply, perhaps you can do the inserts as a set operation instead of a cursor one etc etc.

Answer 2

To avoid slow RBAR ("Row By Agonizing Row") processing, you could load the XML data into temp tables and follow up with a series of set-based operations on them.