How to update/insert/inject nodes into XML I have an application which stores optional Site information in an XML field. 

Sample table:

CREATE TABLE [dbo].[Sites](
    [SiteID] [int] IDENTITY(1,1) NOT NULL,
    [SiteName] [nvarchar](80) NULL,
    [SiteInfo] [xml] NULL );

INSERT INTO [dbo].[Sites] ([SiteName]) VALUES  ('TestSite1') ; 


If no attributes apply the XML field is NULL. If any of those attributes do apply then the application inserts an XML document and relevant nodes.

A valid example looks like this: 

&lt;SiteInfo&gt;
  &lt;BoreID&gt;ABC123&lt;/BoreID&gt;
  &lt;ConsentCompliance&gt;true&lt;/ConsentCompliance&gt;
&lt;/SiteInfo&gt;


I need to migrate some data from another system, and so I'm trying to insert data into this field/XML. So far my attempts have resulted in singleton nodes which the app doesn't like. 

Using some examples i found in the web I've tried a the following: 

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


Which throws the following error: 


  Msg 2226, Level 16, State 1, Line 1 XQuery
  [dbo.Sites.SiteInfo.modify()]: The target of 'insert' must be a single
  node, found 'element(SiteInfo,xdt:untyped) *'


Questions: 


If the field is NULL, Do I need to update the field first to insert a base for the xml i.e  before I can insert to it? 
Whats the name of an XML node that doesn't use an opening and closing pair. For example: &lt;BoreID&gt;ABC123&lt;/BoreID&gt; vs a single side &lt;BoreID=ABC123/&gt; 
Are there other/better methods of inserting nodes into XML using SQL?


Cheers

Pete

Answers: 


Yes  update [dbo].[Sites] SET [SiteInfo] = '&lt;SiteInfo /&gt;' 
Shorthand vs Long
No
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