根據SQLServer2014中XML中的特定條件對多個節點值求和

下面是 XML，我想匯總“ClaimPayment.Amount”值，其中“ClaimPayment.TypeCode”是賠償。意味著輸出將 10000。記住節點可以更多。

    <ZObject>   
      <Attribute Name="ClaimPayment">
        <ZObject>
          <Attribute Name="Re4eba255bf394bdbaccba77b6edca4f5">
            <ZObject>
              <Attribute Name="ClaimPayment.TypeCode">
                <ZObject>
                  <Value xsi:typeName="xs:string">Expense</Value>
                </ZObject>
              </Attribute>
              <Attribute Name="ClaimPayment.Amount">
                <ZObject>
                  <Value xsi:typeName="xs:decimal">3000.0000</Value>
                </ZObject>
              </Attribute>
            </ZObject>
          </Attribute>
          <Attribute Name="R0ffc51fa96594b7989a7dec13a4ddd15">
            <ZObject>
              <Attribute Name="ClaimPayment.TypeCode">
                <ZObject>
                  <Value xsi:typeName="xs:string">Indemnity</Value>
                </ZObject>
              </Attribute>
              <Attribute Name="ClaimPayment.Amount">
                <ZObject>
                  <Value xsi:typeName="xs:decimal">50000.0000</Value>
                </ZObject>
              </Attribute>
            </ZObject>
          </Attribute>
          <Attribute Name="R64b104f17aa94ffebb75ad0b2d8b2775">
            <ZObject>
              <Attribute Name="ClaimPayment.TypeCode">
                <ZObject>
                  <Value xsi:typeName="xs:string">Indemnity</Value>
                </ZObject>
              </Attribute>
              <Attribute Name="ClaimPayment.Amount">
                <ZObject>
                  <Value xsi:typeName="xs:decimal">50000.0000</Value>
                </ZObject>
              </Attribute>
            </ZObject>
          </Attribute>
        </ZObject>   
    </Attribute> 
</ZObject>

我已經用谷歌搜索了它并走到了這一步：

create table #claims(id int identity(1,1) , customdata xml)
;WITH XMLNAMESPACES (
Default 'http://www.oceanwide.com/ZObject/2014',
'http://www.w3.org/2001/XMLSchema' as xs,
'http://www.w3.org/2001/XMLSchema-instance' as xsi)
insert into #claims(customdata )
Select CAST(CustomData AS XML)
from Claims.Resources_Claim cB

;WITH XMLNAMESPACES (
Default 'http://www.oceanwide.com/ZObject/2014',
'http://www.w3.org/2001/XMLSchema' as xs,
'http://www.w3.org/2001/XMLSchema-instance' as xsi)
select x.y.query('(//ZObject/Attribute[@Name="ClaimPayment.TypeCode"]/ZObject/Value/text())')
, ISNULL(CAST(CAST(CustomData AS XML).query('sum(/ZObject/Attribute/ZObject/Attribute/ZObject/Attribute[@Name="ClaimPayment.Amount"]/ZObject/Value/text())') as nvarchar(max)),'') AS 'amount'
,cb.*from #claims cB
CROSS APPLY CB.CustomData.nodes('/ZObject/Attribute[@Name = "ClaimPayment"]') as x(y)
where CB.customdata.exist('(//ZObject/Attribute[@Name="ClaimPayment.TypeCode"]/ZObject/Value[.="Indemnity"])')=1

uj5u.com熱心網友回復：

請嘗試以下解決方案。

沒有提供最小的可重現示例。所以，我是從臀部射擊。

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, customdata XML);
INSERT INTO @tbl (customdata) VALUES
(N'<ZObject xmlns="http://www.oceanwide.com/ZObject/2014" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <Attribute Name="ClaimPayment">
        <ZObject>
            <Attribute Name="Re4eba255bf394bdbaccba77b6edca4f5">
                <ZObject>
                    <Attribute Name="ClaimPayment.TypeCode">
                        <ZObject>
                            <Value xsi:typeName="xs:string">Expense</Value>
                        </ZObject>
                    </Attribute>
                    <Attribute Name="ClaimPayment.Amount">
                        <ZObject>
                            <Value xsi:typeName="xs:decimal">3000.0000</Value>
                        </ZObject>
                    </Attribute>
                </ZObject>
            </Attribute>
            <Attribute Name="R0ffc51fa96594b7989a7dec13a4ddd15">
                <ZObject>
                    <Attribute Name="ClaimPayment.TypeCode">
                        <ZObject>
                            <Value xsi:typeName="xs:string">Indemnity</Value>
                        </ZObject>
                    </Attribute>
                    <Attribute Name="ClaimPayment.Amount">
                        <ZObject>
                            <Value xsi:typeName="xs:decimal">50000.0000</Value>
                        </ZObject>
                    </Attribute>
                </ZObject>
            </Attribute>
            <Attribute Name="R64b104f17aa94ffebb75ad0b2d8b2775">
                <ZObject>
                    <Attribute Name="ClaimPayment.TypeCode">
                        <ZObject>
                            <Value xsi:typeName="xs:string">Indemnity</Value>
                        </ZObject>
                    </Attribute>
                    <Attribute Name="ClaimPayment.Amount">
                        <ZObject>
                            <Value xsi:typeName="xs:decimal">50000.0000</Value>
                        </ZObject>
                    </Attribute>
                </ZObject>
            </Attribute>
        </ZObject>
    </Attribute>
</ZObject>');
-- DDL and sample data population, end

;WITH XMLNAMESPACES 
(
    DEFAULT 'http://www.oceanwide.com/ZObject/2014',
    'http://www.w3.org/2001/XMLSchema' as xs,
    'http://www.w3.org/2001/XMLSchema-instance' as xsi
)
SELECT ID
    , Amount = SUM(c.value('(./text())[1]', 'DECIMAL(15,4)'))
FROM @tbl AS t
OUTER APPLY customdata.nodes('/ZObject/Attribute/ZObject/Attribute/ZObject[Attribute/ZObject/Value/text()="Indemnity"]/Attribute[@Name="ClaimPayment.Amount"]/ZObject/Value') AS t1(c)
GROUP BY ID;

輸出

 ---- ------------- 
| ID |   Amount    |
 ---- ------------- 
|  1 | 100000.0000 |
 ---- ------------- 

uj5u.com熱心網友回復：

在這種情況下，您不需要分解 XML .nodes，因為您可以純粹在 XQuery 中對其進行求和

WITH XMLNAMESPACES 
(
    DEFAULT 'http://www.oceanwide.com/ZObject/2014',
    'http://www.w3.org/2001/XMLSchema' as xs,
    'http://www.w3.org/2001/XMLSchema-instance' as xsi
)
SELECT ID
    , Amount = customdata.value('sum(
      ZObject/Attribute/ZObject/Attribute/ZObject
      [
        Attribute[@Name = "ClaimPayment.TypeCode"]
        /ZObject/Value[text() = "Indemnity"]
      ]/Attribute[@Name = "ClaimPayment.Amount"]
      /ZObject/Value/text()
    )', 'decimal(18,9)')
FROM @tbl AS t;

db<>小提琴

標籤：sql sql服务器

上一篇：對特定列區分大小寫

下一篇：SNOWFLAKE插入失敗-無法識別時間戳“06/28/201620:35:06:703000”