SQL Server querying xml with cursor
I currently have the following SQL 2005 code. I need to import XML as a bulk operation, however I may need additional processing for each record (possibly inserting into a separate table). At the moment I can only query the first element, how can I query the full cursor-style data where I loop through 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!
thank
Ben
+2
source to share
2 answers
Nothing fancy, just declare a cursor over the selection 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;
Normal warnings apply, maybe you can do inserts as a given operation instead of a cursor, etc. etc.
+6
source to share