Modify XML values identified through cross apply

Question

I've got a data issue with some values stored in an XML column in a database. I've reproduced the problem as the following example:

Setup Script:

create table XMLTest
(
    [XML] xml
)

--A row with two duff entries
insert XMLTest values ('
    <root>
        <item>
            <flag>false</flag>
            <frac>0.5</frac>
        </item>
        <item>
            <flag>false</flag>
            <frac>0</frac>
        </item>
        <item>
            <flag>false</flag>
            <frac>0.5</frac>
        </item>     
        <item>
            <flag>true</flag>
            <frac>0.5</frac>
        </item>
    </root>
    ')

In the XML portion the incorrect entries are those with <flag>false</flag> and <frac>0.5</frac> as the value of flag should be true for non-zero frac values.

The following SQL identifies the XML item nodes that require update:

select
    i.query('.')
from
    XMLTest
    cross apply xml.nodes('root/item[flag="false" and frac > 0]') x(i)

I want to do an update to correct these nodes, but I don't see how to modify the item elements identified by a cross apply. I saw the update as looking something like this:

 update t
    set
        x.i.modify('replace value of (flag/text())[1] with "true"')
    from
        XMLTest t
        cross apply xml.nodes('root/item[flag="false" and frac > 0]') x(i)

However this isn't working: I get the error "Incorrect syntax near 'modify'".

Can this be done through this method?

I know an alternative would be to do a string replace on the xml column, but I don't like that as being a bit unsubtle (and I'm not confident it wouldn't break things in my real-word problem)

score 5 · Accepted Answer · answered Jul 22 '11 at 17:22

It is not possible to update the one XML instance in more than one place at a time so you have to do the updates in a loop until you are done.

From http://msdn.microsoft.com/en-us/library/ms190675.aspx "Expression1: Identifies a node whose value is to be updated. It must identify only a single node."

-- While there are rows that needs to be updated
while exists(select *
             from XMLTest
             where [XML].exist('root/item[flag="false" and frac > 0]') = 1)
begin
  -- Update the first occurence in each XML instance 
  update XMLTest set
    [XML].modify('replace value of (root/item[flag="false" and frac > 0]/flag/text())[1] with "true"')
  where xml.exist('root/item[flag="false" and frac > 0]') = 1
end

My point is that i am updating single nodes (sort of). Each updatable node item is a seperate row because of the cross join. — Jon Egerton, Jul 22 '11 at 19:18
@Jon - Yes I understand that. But the XML you need to update is in one row in the original table and it is **also** not possible to update the same row more than once with one update statement. — Mikael Eriksson, Jul 22 '11 at 19:26

Modify XML values identified through cross apply

1 Answers1