declare @x xml;
set @x='<root>
  <items>
  <item ID="1" status="2"/>
  <item ID="5" status="6"/>
  <item ID="9" status="8"/>
  </items>
</root>';

select @x.query('<root>
                   <items>
                      {
                         for $i in //items/item
                            return <item ID="{$i/@ID}" status="1" />
                      }
                  </items>
                 </root>')

------解决方案--------------------
SQL code

-- 建测试表
create table t11(id int,x xml)

insert into t11
select 1,
'<root>
  <items>
  <item ID="1" status="2"/>
  <item ID="5" status="6"/>
  <item ID="9" status="8"/>
  </items>
</root>'

declare @x xml
select @x=x from t11 where id=1

select
 ID = T.c.value('@ID[1]', 'nvarchar(1)'),
 status= T.c.value('@status[1]', 'int')
into #t11
from @x.nodes('/root/items/item') T(c)

update #t11 set status=1

update t11 set x=
(select '<root><items>'+cast((select * from #t11 for xml raw('item')) as varchar(2000))+'</items></root>')
where id=1

select x from t11

x
-----------------------------------------------------------------------
<root><items><item ID="1" status="1" /><item ID="5" status="1" /><item ID="9" status="1" /></items></root>