Sum multiple nodes value on based on specific condition in XML in SQL Server 2014-CodePudding

Below is XML and I want to sum of "ClaimPayment.Amount" value where "ClaimPayment.TypeCode" is Indemnity. Means output would 10000. Remember nodes can be more.

    <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>

I have googled it and come upto this far:

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

CodePudding user response：

Please try the following solution.

A minimal reproducible example is not provided. So, I am shooting from the hip.

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;

Output

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

CodePudding user response：

You don't need to shred the XML with .nodes in this case, as you can sum it purely in 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;

ID	Amount
1	100000.000000000

db<>fiddle