SQL Server querying xml using a cursor

Question

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 = ' < DataDTO> < UserId>123456789 < ItemID>0 < /DataDTO> < DataDTO> < UserId>112456789 < ItemID>10 < DataDTO> < UserId>123456129 < ItemID>20 < DataDTO> < UserId>120056789 < ItemID>444 < /DataDTO> ' 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

Accepted Answer

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.

SQL Server querying xml using a cursor

Solution

Problem