2

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
  • 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/> – Sir Swears-a-lot Jul 22 '15 at 23:01
  • Same, both are singleton nodes. One is just shorthand, one is long. – Mister Magoo Jul 22 '15 at 23:12
4

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? – Sir Swears-a-lot Jul 22 '15 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. – Mister Magoo Jul 22 '15 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. – Sir Swears-a-lot Jul 22 '15 at 23:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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