3

In my table I have a column with XML data.

For example lets say that schema is as below:

<person>
  <id></id>
  <name></name>
</person>

Problem is that some of these nodes has got additional node <lastname>

Now I would like to update all elements in this column so that each one has got node lastname with default value, for example smith.

gotqn
  • 42,737
  • 46
  • 157
  • 243
gruber
  • 28,739
  • 35
  • 124
  • 216

2 Answers2

5

Try something like this:

UPDATE 
    dbo.YourTable
SET 
    XmlColumn.modify('insert <lastname>Smith</lastname> as last into (/person)[1]')
WHERE 
    XmlColumn.exist('/person/lastname') = 0

This updates all rows where the <lastname> node does not exist inside <person>, and insert <lastname>Smith</lastname> into those XML values.

Update: if you want to pick certain names, use this query:

UPDATE 
    dbo.YourTable
SET 
    XmlColumn.modify('insert <lastname>Smith</lastname> as last into (/person)[1]')
WHERE 
    XmlColumn.exist('/person[name="John"]') = 1

Update #2: to prove my query is right - try this little test here:

DECLARE @test TABLE (ID INT, XmlCol XML)

INSERT INTO @test 
   VALUES(1, '<person><id>1</id><name>John</name></person>'),
         (2, '<person name="John"><id>2</id><name>Fred</name></person>'),
         (3, '<person><id>3</id><name>Wally</name></person>')

SELECT *
FROM @test
WHERE XmlCol.exist('/person[name="John"]') = 1

If you run this (on SQL Server 2008 or newer), you will get:

1    <person><id>1</id><name>John</name></person>

as the output; the selection criteria is testing on the XML element <name> having a value of John

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • How can I filter only users with name john ? Thanks for guick answeer – gruber Mar 17 '11 at 12:20
  • @gruber: no you are wrong on this one; the query I specified is checking for the value of "John" inside an **XML element** called `name` - **NOT** an XML attribute! This **will** select entries where you have `John` - try it! – marc_s Mar 17 '11 at 14:42
0

Check out this link:

http://msdn.microsoft.com/en-us/library/ms345117%28SQL.90%29.aspx

There you can find this:

UPDATE docs SET xCol.modify('
  insert 
   <section num="2">   
        <title>Background</title>
   </section>
  after (/doc//section[@num=1])[1]')

HTH

Raja
  • 3,608
  • 3
  • 28
  • 39
  • How is this taking into account that *some* of the nodes already have a subnode?? This will insert a new node into **all** rows - that's **not** what the OP is looking for – marc_s Mar 17 '11 at 11:35
  • 1
    I was just giving him an example which I found in that link (a big article)....Your answer makes sense and I have already given a +1 for that :-) – Raja Mar 17 '11 at 11:36