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:

  ABC123
  true

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 ABC123 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: ABC123 vs a single side

• Are there other/better methods of inserting nodes into XML using SQL?

Cheers

Pete

Answers:

• Yes update [dbo].[Sites] SET [SiteInfo] = ''

• Shorthand vs Long

• No

You just need to specify the first SiteInfo node using [1]

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

StackExchange Database Administrators Q#107827, answer score: 9