Search code examples
sql-serverxmldatabasedatabase-administrationdml

SQL DML - unable to use the after function in modify

I need to update around 1000 xml records with a new xml node, the xml's are stored in a sql table column, with xml datatype, So I've been trying to utilize the modify function documented HERE (specifically example A.4)

However, When I run this against my staging environment (SqlServer 2016 SP2-CU13) I receive the following error message:

Msg 2395, Level 16, State 1, Line 7 XQuery [production.dbo.organisations.Configuration.modify()]: There is no function '{http://www.w3.org/2004/07/xpath-functions}:AFTER()'

The code is rudimentary, I'm just stumped by the error message. Any help or insight is greatly appreciated

Code:

update [production].[dbo].[organisations] Set configuration.modify('INSERT <LegalEntityName>TEST</LegalEntityName> AFTER (/OrganisationMetaData/ExamPrepAssessModeFromAddress)[1]') where ID = 1
Solution

  • Remember XML is always case-sensitive, eg:

    use tempdb
go
drop table if exists organisations 
go
create table organisations(id int, configuration xml)
insert into organisations(id, configuration) values (1,'<OrganisationMetaData><ExamPrepAssessModeFromAddress></ExamPrepAssessModeFromAddress></OrganisationMetaData>')

update organisations Set configuration.modify('insert <LegalEntityName>TEST</LegalEntityName> after (/OrganisationMetaData/ExamPrepAssessModeFromAddress)[1]') where ID = 1