Adding data to a XML data type in SQL Server

Question

I have a table that contains a xml data type which acts like an audit log.

CREATE TABLE T (i int, log xml);

Where the log structure is something like:

<auditLog>
<entry action="Created" description="New item created" value="banana" />
<entry action="Deleted" description="Deleted item" value="apple" />
</auditLog>

I am trying to figure out if/how it is possible to add another entry to this xml file. I have found lots of functions to add nodes to the current entries but not how to make a new entry. I assume that there must be a simple way of doing this but I cant seem to find it.

Any pointers would be very welcome.

Do you want to add a new row in the table or a new <entry> node inside <auditlog>? — Rhys Jones
– Rhys Jones, Commented Jan 22, 2015 at 15:32
You are adding a node to the current entry of <auditLog>, so those functions should work for you. If they don't please post your attempt :) — asontu
– asontu, Commented Jan 22, 2015 at 15:33
Ah, so would it be just a matter of adding a node inside <auditLog> which contains all of the information that is in the entry node — James Silvester
– James Silvester, Commented Jan 22, 2015 at 15:40

Code Different · Accepted Answer · 2015-01-22 15:42:24Z

2

You are probably confused by the fact that your XML node's name is entry. What you are doing is inserting a new node into your XML data. You can use this:

UPDATE T
    SET [log].modify('
    insert <entry action="Added" description="New entry added" value="orange" />
    as last
    into (/auditLog)[1]')
    WHERE i = 1

1 Comment

James Silvester Over a year ago

Yes of course. I was getting rather confused and didn't realize that I could simply add a node to <auditLog> to effectively add an 'entry'.

Collectives™ on Stack Overflow

Adding data to a XML data type in SQL Server

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related