How to populate an empty XML node using modify replace on 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'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?

i-one · Accepted Answer · 2015-09-02 04:44:39Z

4

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;

edited Sep 2, 2015 at 4:44

answered Jul 30, 2013 at 20:22

i-one

5,1201 gold badge33 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Si8 Over a year ago

What if I wanted to insert a variable in the INSERT statement? stackoverflow.com/questions/32253235/… Thank you.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related