CODEWITHSUNDEEP
sql-serverxmlxquery-sql
workvact
workvact

Reputation: 685

XQuery [modify()]: The namespace prefix 'dt' has not been defined

I am trying to update an existing XML column value in SQL Server. 

declare @xml xml;
select @xml = 
'<items xmlns:dt="urn:schemas-microsoft-com:datatypes">
<item dt:dt="string">Item1<item>
</items>';
set @xml.modify('insert <item dt:dt="string">Item2</item> into (/items)[1]');

This throws the error: XQuery [modify()]: The namespace prefix 'dt' has not been defined

How can I get the namespace in to the modify?

Upvotes: 0

Views: 515

Answers (1)

lptr
lptr

Reputation: 6798

declare @xml xml;
select @xml = 
'<items xmlns:dt="urn:schemas-microsoft-com:datatypes">
<item dt:dt="string">Item1</item>
</items>';
select @xml;

set @xml.modify('declare namespace dt="urn:schemas-microsoft-com:datatypes"; insert <item dt:dt="string">Item2</item> into (/items)[1]');

select @xml;

Upvotes: 2

Related Questions