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:


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) *'


  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?




  1. Yes update [dbo].[Sites] SET [SiteInfo] = '<SiteInfo />'
  2. Shorthand vs Long
  3. No
  • Sorry I may have the terminology wrong. What is it called when a Node has an opening and closing pair with a value eg. <BoreID>ABC123</BoreID> vs a single side <BoreID=ABC123/> Commented Jul 22, 2015 at 23:01
  • Same, both are singleton nodes. One is just shorthand, one is long. Commented Jul 22, 2015 at 23:12

1 Answer 1


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

update [dbo].[Sites] 
SET [SiteInfo].modify('insert <Anothernode>ABC123</Anothernode> into (/SiteInfo[1])') 
where Siteid = 1 
  • Cool, thanks thanks seems to work where an XML exists. What about if the field is Null? can that be done in a single operation? or do I need to set the root node first? Commented Jul 22, 2015 at 22:59
  • 1
    Nulls cannot be modified, so you would need to set it first, which is a pain because that means you have to check if it is null before deciding on an update strategy. SQL and XML are not best friends. Commented Jul 22, 2015 at 23:20
  • I edited my question for clarity in case others ask the same thing in the future. My Q1 may not have been very clear, Your first answer says no but later says yes. I will edit to clarify if field is NULL. Commented Jul 22, 2015 at 23:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.