如何根据该 XML 中的值更新 SQL 中的 XML

2021-10-01 00:00:00 xml sql tsql sql-server sqlxml

我必须根据该 XML 的某些条件更新表的 XML.示例 XML:

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>

每个国家/地区可以有多个地方.我必须根据 Country 和 Places 进行分组，然后我必须将 PlaceValues 更新为 null for PlaceValue = 0 除了 0 紧接在 PlaceValue > 1 之前.此示例中的示例，对于 Country = Brazil 和 PlaceName = 1，PlaceValue forMonth1 到 Month2 将为 Null 但 Month3 将保持为 0 作为其前一个 Month4 大于 0.

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.

推荐答案

基本上，我看到了两种处理方法.首先 - 将 xml 拆分为 sql 表/派生表，完成您的工作，然后再次组合成 xml.

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>

第二种方法是在 xml 本身中使用 xquery.

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

答案实际上取决于您所说的紧接在 PlaceValue > 1 之前"是什么意思.我在这里假设这意味着 - 值 > 1 的月份之前的月份.

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.

相关文章