How to populate an empty XML node using replace for replace in an XML column?

Question

This is my xml stored in an XML type column called UserDef

<Orders> <Document> <DocumentID>abc123</DocumentID> <ActualShipDate /> <Comments/> </Document> <Document> .... ... </Document> </Orders> 

I am trying to populate the Comments element with the following:

 declare @t1 table (id bigint, userdef xml) insert into @t1 select id, userdef from MyMainTable update @t1 set userdef.modify('replace value of(/Orders/Document/Comments[1]/text())[1] with "NO COMMENTS"') select * from @t1 

However, I do not see populated Comments . What should I do differently for this to work?

Answer 1

The <Comments/> element does not have a text() node, you must do insert , not replace :

  update @t1 set userdef.modify(' insert text{"NO COMMENTS"} into (/Orders/Document/Comments[1])[1] ') where id = @id; 

If you want to insert text from an sql variable, you can use the following construction:

 declare @comments varchar(1000); set @comments = 'NO COMMENTS'; update @t1 set userdef.modify(' insert text {sql:variable("@comments")} into (/Orders/Document/Comments[1])[1] ') where id = @id;