CODEWITHSUNDEEP
t-sqlsql-server-2005sql-updatexquerysqlxml
FMFF
FMFF

Reputation: 1718

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

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?

Upvotes: 4

Views: 1918

Answers (1)

i-one
i-one

Reputation: 5120

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;

Upvotes: 4

Related Questions