問題描述

我必須根據該 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.

這篇關于如何根據該 XML 中的值更新 SQL 中的 XML的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！