How to Update XML in SQL based on values in that XML

Question

I have to update XML of table based on some conditions of that XML. Sample XML:

<CountryValues>
  <CountryRow>
    <CountryName>Brazil</CountryName>
    <PlaceName>Place 1</PlaceName>
    <Month>1</Month>
    <PlaceValue>0</PlaceValue>        
  </CountryRow>
  <CountryRow>
    <CountryName>Brazil</CountryName>
    <PlaceName>Place 1</PlaceName>
    <Month>2</Month>
    <PlaceValue>0</PlaceValue>        
  </CountryRow>
  <CountryRow>
    <CountryName>Brazil</CountryName>
    <PlaceName>Place 1</PlaceName>
    <Month>3</Month>
    <PlaceValue>0</PlaceValue>        
  </CountryRow>
  <CountryRow>
    <CountryName>Brazil</CountryName>
    <PlaceName>Place 1</PlaceName>
    <Month>4</Month>
    <PlaceValue>10</PlaceValue>        
  </CountryRow>
  <CountryRow>
    <CountryName>Australia</CountryName>
    <PlaceName>Place 1</PlaceName>
    <Month>1</Month>
    <PlaceValue>0</PlaceValue>        
  </CountryRow>
  <CountryRow>
    <CountryName>Australia</CountryName>
    <PlaceName>Place 1</PlaceName>
    <Month>1</Month>
    <PlaceValue>0</PlaceValue>        
  </CountryRow>
  <CountryRow>
    <CountryName>Australia</CountryName>
    <PlaceName>Place 1</PlaceName>
    <Month>1</Month>
    <PlaceValue>4</PlaceValue>        
  </CountryRow>
 </CountryValues>

Each Country can have multiple Places. I have to group on the basis of Country and Places, then I have to update PlaceValues to null for PlaceValue = 0 except 0 which is immediately preceding PlaceValue > 1. Example in this sample, for Country = Brazil and PlaceName = 1, PlaceValue for Month1 to Month2 will be Null but Month3 will remain 0 as its preceding Month4 which is greate than 0.

Answer 1

Basically, I see 2 ways of dealing with this. First - split xml to sql table/derived table, do your work and then combine into xml again.

declare @data xml = 
'<CountryValues>
  <CountryRow>
    <CountryName>Brazil</CountryName>
    <PlaceName>Place 1</PlaceName>
    <Month>1</Month>
    <PlaceValue>0</PlaceValue>        
  </CountryRow>
  <CountryRow>
    <CountryName>Brazil</CountryName>
    <PlaceName>Place 1</PlaceName>
    <Month>2</Month>
    <PlaceValue>0</PlaceValue>        
  </CountryRow>
  <CountryRow>
    <CountryName>Brazil</CountryName>
    <PlaceName>Place 1</PlaceName>
    <Month>3</Month>
    <PlaceValue>0</PlaceValue>        
  </CountryRow>
  <CountryRow>
    <CountryName>Brazil</CountryName>
    <PlaceName>Place 1</PlaceName>
    <Month>4</Month>
    <PlaceValue>10</PlaceValue>        
  </CountryRow>
 </CountryValues>'

;with cte as (
    select
        t.c.value('CountryName[1]', 'nvarchar(max)') as CountryName,
        t.c.value('PlaceName[1]', 'nvarchar(max)') as PlaceName,
        t.c.value('Month[1]', 'int') as [Month],
        t.c.value('PlaceValue[1]', 'int') as PlaceValue
    from @data.nodes('CountryValues/CountryRow') as t(c)
)
select
    c1.CountryName,
    c1.PlaceName,
    c1.[Month],
    case
        when c1.PlaceValue = 0 and isnull(c2.PlaceValue, 0) <= 1 then null
        else c1.PlaceValue
    end as PlaceValue
from cte as c1
    left outer join cte as c2 on c2.CountryName = c1.CountryName and c2.PlaceName = c1.PlaceName and c2.[Month] = c1.[Month] + 1
for xml path('CountryRow'), root('CountryValues')

----------------------------------
<CountryValues>
  <CountryRow>
    <CountryName>Brazil</CountryName>
    <PlaceName>Place 1</PlaceName>
    <Month>1</Month>
  </CountryRow>
  <CountryRow>
    <CountryName>Brazil</CountryName>
    <PlaceName>Place 1</PlaceName>
    <Month>2</Month>
  </CountryRow>
  <CountryRow>
    <CountryName>Brazil</CountryName>
    <PlaceName>Place 1</PlaceName>
    <Month>3</Month>
    <PlaceValue>0</PlaceValue>
  </CountryRow>
  <CountryRow>
    <CountryName>Brazil</CountryName>
    <PlaceName>Place 1</PlaceName>
    <Month>4</Month>
    <PlaceValue>10</PlaceValue>
  </CountryRow>
</CountryValues>

Second way would be to use xquery inside the xml itself.

The answer is really depends on what do you mean by "immediately preceding PlaceValue > 1". I've assumed here that this means - month right before month with value > 1.