Query XML using a cursor style approach

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)

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.

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.