Sql-server – 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:

<SiteInfo>
  <BoreID>ABC123</BoreID>
  <ConsentCompliance>true</ConsentCompliance>
</SiteInfo>

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 <Anothernode>ABC123</Anothernode> 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:

1. 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?
2. Whats the name of an XML node that doesn't use an opening and closing pair. For example: <BoreID>ABC123</BoreID> vs a single side <BoreID=ABC123/>
3. Are there other/better methods of inserting nodes into XML using SQL?

Cheers

Pete

Answers:

1. Yes update [dbo].[Sites] SET [SiteInfo] = '<SiteInfo />'
2. Shorthand vs Long
3. No