How to update xml using value from a sql query
You've got nothing to replace in &lt;SiteID/&gt;. Try and use an insert instead:

update Sites SET [SiteInfo].modify('insert text{sql:column("InVal")} as first into (/SiteInfo/SiteID)[1]');