Sql Server Error: Msg 2207, Level 16, State 1, Line 89 XQuery : Only non-document nodes can be inserted. Found “xs:string ?”.

Interesting Error today:

While playing with XML DML, ran into this cryptic error.

Msg 2207, Level 16, State 1, Line 89
XQuery [dbo.Table.XMLColumn.modify()]: Only non-document nodes 
can be inserted. Found "xs:string ?".

 

While INSERTing a new XML element into an existing XML column, using the below code, this error pops up:

 

DECLARE @NewXMLElement VARCHAR(50) = 'Test'

UPDATE dbo.TableName
SET XMLColumn.modify('
		insert sql:variable("@NewXMLElement")
		into (/Parent/Child)[1]
	')
WHERE XMLColumn.exist('(/Parent/Child/NewXMLElement/text())') = 0
GO

Resolution:

Here, the goal is to INSERT a XML snippet into an existing XML column value. So, it is important that in the modify() function, the new XML element variable is defined as XML datatype

DECLARE @NewXMLElement XML = 'Test'

UPDATE dbo.TableName
SET XMLColumn.modify('
		insert sql:variable("@NewXMLElement")
		into (/Parent/Child)[1]
	')
WHERE XMLColumn.exist('(/Parent/Child/NewXMLElement/text())') = 0
GO

 

Hope this helps,
_Sqltimes

 

Read Full Post »