How to update xml using value from a sql query I have a table with an XML column. I need to insert various nodes and then set those nodes using values from a sub-query. 

Setup: 

use tempdb

CREATE TABLE [dbo].[Sites](
    [SiteID] [int] IDENTITY(1,1) NOT NULL,
    [SiteInfo] [xml] NULL,
    [InVal] Varchar(50) NULL)

insert into [dbo].[Sites] ([InVal]) select 'ABC' union select 'DEF' union  select 'GHI'

update [dbo].[Sites] SET [SiteInfo] = '&lt;SiteInfo /&gt;'

update [dbo].[Sites] SET [SiteInfo].modify('insert &lt;SiteID/&gt; into (/SiteInfo[1])') 


Here is my update in it's most simple form. 

update Sites SET [SiteInfo].modify('replace value of (/SiteInfo/@SiteID)[1] with sql:column("InVal")') 


I have tried with both singleton and non singleton nodes 

The end result should preferably not be a singleton. i.e something like: 

&lt;SiteInfo&gt;
  &lt;SiteID&gt;ABC&lt;/SiteID&gt;
&lt;/SiteInfo&gt;
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]');