CREATE TABLE MyTable(PK INT NOT NULL PRIMARY KEY, Val INT)
INSERT INTO MyTable(PK,Val)
SELECT 1,10 UNION ALL SELECT 2,10
--
--
DECLARE @x XML
SET @x='<ROOT><ROW PK="1" VAL="50"/><ROW PK="2" VAL="100"/></ROOT>'
--
--
-- Joining directly to XML caused a clustered index scan on MyTable.PK
UPDATE MyTable
SET Val=r.value('@VAL','INT')
FROM @x.nodes('/ROOT/ROW') AS x(r)
WHERE MyTable.PK=r.value('@PK','INT')
--
-- 
DECLARE @T TABLE(PK INT NOT NULL PRIMARY KEY, Val INT)
INSERT INTO @T(PK,Val)
SELECT r.value('@PK','INT'),
       r.value('@VAL','INT')
FROM @x.nodes('/ROOT/ROW') AS x(r)
--
--
-- Joining to a temporary table, this turns into a clustered index seek on MyTable.PK
UPDATE MyTable
SET Val=r.Val
FROM @T r
WHERE MyTable.PK=r.PK
--
--
DROP TABLE MyTable