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.

What does “XML of table” mean? You have an XML file that you want to update using T-SQL for some reason? You have an XML column in a table? You want to update the `placeValue` element value of this column? — Dour High Arch, Apr 20 '15 at 19:57
There is XML Column in a table. I have to update this column based on conditions mentioned in the question. I cannot do this in C#, I have to create Sql script only. — Navneet Duggal, Apr 20 '15 at 20:15

score 0 · Answer 1 · answered Apr 20 '15 at 19:49

If you are fine using C# you should look at XDocument.

Example :

XDocument document = XDocument.Parse("<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>");
var countries = document.Root.Elements("CountryRow");

foreach (var country in countries)
{
    //Do your work here

    //You can access sub element like so :
    Console.WriteLine (country.Element("CountryName").Value);
}

Output:

Brazil
Brazil
Brazil
Brazil
Australia
Australia
Australia

I cant do this in C#. It has to be sql script – Navneet Duggal Apr 20 '15 at 20:15 — Navneet Duggal, Apr 20 '15 at 20:15

score 0 · Answer 2 · answered Apr 20 '15 at 22:08

You need to use the .modify() method of the xml data type (see here: https://msdn.microsoft.com/en-us/library/ms187093.aspx) and XQuery to find which nodes you should change. You can find some nice examples in this link: http://www.mssqltips.com/sqlservertip/2738/examples-of-using-xquery-to-update-xml-data-in-sql-server/

score 0 · Accepted Answer · answered Apr 21 '15 at 09:00

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.

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

3 Answers3

Linked