question

Ben avatar image
Ben asked

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

sql-serverxml
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Tom Staab avatar image
Tom Staab answered

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)
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

dvroman avatar image
dvroman answered

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.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image
Grant Fritchey answered

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.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.