I have an application which stores optional Site information in an XML field.
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) *'
- 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: <BoreID>ABC123</BoreID> vs a single side <BoreID=ABC123/>
- Are there other/better methods of inserting nodes into XML using SQL?
update [dbo].[Sites] SET [SiteInfo] = '<SiteInfo />'
- Shorthand vs Long