How to update xml using value from a sql query

Question

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] = '<SiteInfo />'
update [dbo].[Sites] SET [SiteInfo].modify('insert <SiteID/> 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:

<SiteInfo>
 <SiteID>ABC</SiteID>
</SiteInfo>

Answer 1

You've got nothing to replace in <SiteID/>. Try and use an insert instead:

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

• Sir Swears-a-lot: Can you insert the node and text at the same time?
• sticky bit: @Peter: Yes. update [dbo].[Sites] SET [SiteInfo].modify('insert <SiteID>123</SiteID> into (/SiteInfo[1])')
• Sir Swears-a-lot: Eureka! update [dbo].[Sites] SET [SiteInfo].modify('insert <SiteID>text{sql:column("InVal")}</SiteID> into (/SiteInfo[1])')