declare @xml xml
set @xml = '<items><item id="1" ISDELETED="D"></item><item id="2" ISDELETED="D"></item><item id="3" ISDELETED="D"></item></items>'

;with MyCTE as (
	select xmlcol.value('@id','int') as ID,
	xmlcol.value ('@ISDELETED', 'char(1)') as IsDeleted
	from @xml.nodes ('/items/item') tbl (xmlcol))
select * from MyCTE

declare @xml xml
set @xml = '<items><item id="1" ISDELETED="D"></item><item id="2" ISDELETED="D"></item><item id="3" ISDELETED="D"></item></items>'

Update MyTable
Set ISDELETED = B.ISDELETED
From MyTable A
Inner Join 
	(
	select xmlcol.value('@id','int') as ID,
	xmlcol.value ('@ISDELETED', 'char(1)') as IsDeleted
	from @xml.nodes ('/items/item') tbl (xmlcol)
	) B on B.ID = A.ITEMID