[SOLVED] XPath in T-SQL update schema

XPath in T-SQL update schema

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.

Solution

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