How to populate an empty XML node using modify replace on an XML column?

sql-server-2005, sql-update, sqlxml, t-sql, xquery

Solution

Element `<Comments/>` has no `text()` node, you should do `insert` rather then `replace`:

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

If one wishes to insert text from sql variable, the following construct can be used:

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;

Problem

This is my xml stored in an XML type column called UserDef ``` <Orders> <Document> <DocumentID>abc123</DocumentID> <ActualShipDate /> <Comments/> </Document> <Document> .... ... </Document> </Orders> ``` I'm trying to populate the `Comments` element with this: ``` 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 don't see the `Comments` being populated at all. What should I do differently in order for this to work?

Original source

Related problems