How to update/insert/inject nodes into XML
You just need to specify the first SiteInfo node using [1]

update [dbo].[Sites] 
SET [SiteInfo].modify('insert &lt;Anothernode&gt;ABC123&lt;/Anothernode&gt; into (/SiteInfo[1])') 
where Siteid = 1