Add to xml stored in sql 2005

Question

I have a SQL Server 2005 database which has a table called improvements.

Within Improvements is a field called TransactionID and one called Comments. The field Comments is a XML data type with such entries as follows.

<Comment>
    <CreatedBy>Bob</CreatedBy>
    <CreatedDateTime>2009-08-24T11:36:13.020774+01:00</CreatedDateTime>
    <CreaterRole>Manager</CreaterRole>
    <CreationStage>INITIAL REVIEW</CreationStage>
    <CreationStatus>APPROVED</CreationStatus>
    <UserComment>Approved</UserComment>
</Comment>
<Comment>
    <CreatedBy>Bob</CreatedBy>
    <CreatedDateTime>2009-08-24T11:36:25.7240616+01:00</CreatedDateTime>
    <CreaterRole>Manager</CreaterRole>
    <CreationStage>CAPTURE</CreationStage>
    <CreationStatus>ACCEPTED</CreationStatus>
    <UserComment />
</Comment>

I need to enter an additional entry along the lines of:

 <Comment>
    <CreatedBy>Bob</CreatedBy>
    <CreatedDateTime>2013-01-29T11:36:25.7240616+01:00</CreatedDateTime>
    <CreaterRole>Manager</CreaterRole>
    <CreationStage>CLOSED</CreationStage>
    <CreationStatus>CLOSED</CreationStatus>
    <UserComment>Closed as agreed<UserComment />
 </Comment>

Where the TransactionID = A transaction number.

I have found a number of solutions on the internet to amend an entry using SET..... but cannot figure out how to actually add an entry.

Can anyone help me with this one please?

Many thanks in advance.

Kev.

Mikael Eriksson · Accepted Answer · 2013-01-29 18:06:20Z

1

declare @TransactionID int
declare @Comment xml

set @TransactionID = 1
set @Comment = '
<Comment>
    <CreatedBy>Bob</CreatedBy>
    <CreatedDateTime>2013-01-29T11:36:25.7240616+01:00</CreatedDateTime>
    <CreaterRole>Manager</CreaterRole>
    <CreationStage>CLOSED</CreationStage>
    <CreationStatus>CLOSED</CreationStatus>
    <UserComment>Closed as agreed</UserComment>
</Comment>'

update improvements 
set Comments.modify('insert sql:variable("@Comment") as last into /')
where TransactionID = @TransactionID

answered Jan 29, 2013 at 18:06

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

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

Collectives™ on Stack Overflow

Add to xml stored in sql 2005

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related