Q:

MSSQL XML long update query without locking

I have an MSSQL server with around 150 000 rows with huge XML documents in each row. I'm running a query to update all of them and it looks something like this:
UPDATE [dbo].[Table]
SET 
[BIGXML].modify
(' replace value of 
    (/xpath)[1]
     with     "newValue"
')

Currently it takes around 6 hours for the query to run, the bad thing is that during that time the database seems unresponsive to any other queries. How can I run the query and still keep the database functioning?

A:

Is the table responsive if you update the records one at a time.
DECLARE @TableID INT

DECLARE X CURSOR FOR 
    SELECT Table.ID FROM Table

OPEN X
FETCH NEXT FROM X INTO @TableID
WHILE(@@FETCH_STATUS=0) BEGIN

    UPDATE [dbo].[Table]
    SET 
    [BIGXML].modify
    (' replace value of 
        (/xpath)[1]
         with     "newValue"
    ')
    WHERE
        TableID=@TableID

    FETCH NEXT FROM X INTO @TableID
END
CLOSE X
DEALLOCATE X

