I have a XML as below -
<?xml version='1.0' encoding='UTF-8'?>
<wd:Report_Data xmlns:wd="urn:com.workday.report/Page_14_Direct_Premiums_Written">
<wd:Report_Entry>
<wd:Region>AL</wd:Region>
<wd:Fire_DPW>0</wd:Fire_DPW>
<wd:Allied_DPW>0</wd:Allied_DPW>
<wd:Private_Flood_DPW>0</wd:Private_Flood_DPW>
<wd:Farmowners_Multiple_Peril_DPW>0</wd:Farmowners_Multiple_Peril_DPW>
<wd:Homeowners_Multiple_Peril_DPW>0</wd:Homeowners_Multiple_Peril_DPW>
<wd:Commercial_Multiple_Peril_Non-Liability_DPW>0</wd:Commercial_Multiple_Peril_Non-Liability_DPW>
<wd:CF_SCommercial_Multiple_Peril_Liability_DPW>0</wd:CF_SCommercial_Multiple_Peril_Liability_DPW>
<wd:Ocean_Marine_DPW>0</wd:Ocean_Marine_DPW>
<wd:Inland_Marine_DPW>0</wd:Inland_Marine_DPW>
<wd:Earthquake_DPW>0</wd:Earthquake_DPW>
<wd:Workers_Compensation_DPW>0</wd:Workers_Compensation_DPW>
<wd:Other_Liability_Occurrence_DPW>-431</wd:Other_Liability_Occurrence_DPW>
<wd:Other_Liability_Claims_Made_DPW>0</wd:Other_Liability_Claims_Made_DPW>
<wd:Excess_Workers_Compensation_DPW>0</wd:Excess_Workers_Compensation_DPW>
<wd:Products_Liability_Occurrence_State_Page_DPW>0</wd:Products_Liability_Occurrence_State_Page_DPW>
<wd:Products_Liability_Claims_Made_State_Page_DPW>0</wd:Products_Liability_Claims_Made_State_Page_DPW>
<wd:Private_Passenger_Auto_No_Fault_DPW>0</wd:Private_Passenger_Auto_No_Fault_DPW>
<wd:Private_Passenger_Auto_Liability_Other_DPW>0</wd:Private_Passenger_Auto_Liability_Other_DPW>
<wd:Commercial_Auto_No_Fault_DPW>0</wd:Commercial_Auto_No_Fault_DPW>
<wd:Commercial_Auto_Liability_Other_DPW>0</wd:Commercial_Auto_Liability_Other_DPW>
<wd:Private_Passenger_Auto_Physical_Damage_DPW>0</wd:Private_Passenger_Auto_Physical_Damage_DPW>
<wd:Commercial_Auto_Physical_Damage_DPW>0</wd:Commercial_Auto_Physical_Damage_DPW>
<wd:Aircraft_DPW>0</wd:Aircraft_DPW>
<wd:Fidelity_DPW>0</wd:Fidelity_DPW>
<wd:Surety_DPW>0</wd:Surety_DPW>
<wd:Burglary_Theft_DPW>0</wd:Burglary_Theft_DPW>
<wd:Boiler_Machinery_DPW>0</wd:Boiler_Machinery_DPW>
</wd:Report_Entry>
<wd:Report_Entry>
<wd:Region>AL</wd:Region>
<wd:Fire_DPW>0</wd:Fire_DPW>
<wd:Allied_DPW>0</wd:Allied_DPW>
<wd:Private_Flood_DPW>0</wd:Private_Flood_DPW>
<wd:Farmowners_Multiple_Peril_DPW>0</wd:Farmowners_Multiple_Peril_DPW>
<wd:Homeowners_Multiple_Peril_DPW>0</wd:Homeowners_Multiple_Peril_DPW>
<wd:Commercial_Multiple_Peril_Non-Liability_DPW>0</wd:Commercial_Multiple_Peril_Non-Liability_DPW>
<wd:CF_SCommercial_Multiple_Peril_Liability_DPW>0</wd:CF_SCommercial_Multiple_Peril_Liability_DPW>
<wd:Ocean_Marine_DPW>0</wd:Ocean_Marine_DPW>
<wd:Inland_Marine_DPW>0</wd:Inland_Marine_DPW>
<wd:Earthquake_DPW>0</wd:Earthquake_DPW>
<wd:Workers_Compensation_DPW>2894</wd:Workers_Compensation_DPW>
<wd:Other_Liability_Occurrence_DPW>0</wd:Other_Liability_Occurrence_DPW>
<wd:Other_Liability_Claims_Made_DPW>0</wd:Other_Liability_Claims_Made_DPW>
<wd:Excess_Workers_Compensation_DPW>0</wd:Excess_Workers_Compensation_DPW>
<wd:Products_Liability_Occurrence_State_Page_DPW>0</wd:Products_Liability_Occurrence_State_Page_DPW>
<wd:Products_Liability_Claims_Made_State_Page_DPW>0</wd:Products_Liability_Claims_Made_State_Page_DPW>
<wd:Private_Passenger_Auto_No_Fault_DPW>0</wd:Private_Passenger_Auto_No_Fault_DPW>
<wd:Private_Passenger_Auto_Liability_Other_DPW>0</wd:Private_Passenger_Auto_Liability_Other_DPW>
<wd:Commercial_Auto_No_Fault_DPW>0</wd:Commercial_Auto_No_Fault_DPW>
<wd:Commercial_Auto_Liability_Other_DPW>0</wd:Commercial_Auto_Liability_Other_DPW>
<wd:Private_Passenger_Auto_Physical_Damage_DPW>0</wd:Private_Passenger_Auto_Physical_Damage_DPW>
<wd:Commercial_Auto_Physical_Damage_DPW>0</wd:Commercial_Auto_Physical_Damage_DPW>
<wd:Aircraft_DPW>0</wd:Aircraft_DPW>
<wd:Fidelity_DPW>0</wd:Fidelity_DPW>
<wd:Surety_DPW>0</wd:Surety_DPW>
<wd:Burglary_Theft_DPW>0</wd:Burglary_Theft_DPW>
<wd:Boiler_Machinery_DPW>0</wd:Boiler_Machinery_DPW>
</wd:Report_Entry>
</wd:Report_Data>
This structure is repeating multiple times with multiple regions (regions might repeat as well) I have to achieve an output XML which will sum each element for the same regions and then after doing a sum of each element. I have to display the sum of all elements for a region.
The output should be like -
<Direct_Premiums_Written xmlns:wd="urn:com.workday.report/Page_14_Direct_Premiums_Written"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:statutorytype="us-stat-2020-annual-stmt-pc"
xmlns:xbrli="http://www.xbrl.org/2001/instance"
xmlns:iso4217="http://www.xe.com/iso4217.htm"
xmlns:stat="urn:www-iasa-org/tempuri">
<stat:explAKFireDirectPremiumsWritten>0</stat:explAKFireDirectPremiumsWritten>
<stat:explAKAlliedLinesDirectPremiumsWritten>0</stat:explAKAlliedLinesDirectPremiumsWritten>
<stat:explAKPrivateFloodDirectPremiumsWritten>0</stat:explAKPrivateFloodDirectPremiumsWritten>
<stat:explAKFarmownersMultiplePerilDirectPremiumsWritten>0</stat:explAKFarmownersMultiplePerilDirectPremiumsWritten>
<stat:explAKHomeownersMultiplePerilDirectPremiumsWritten>0</stat:explAKHomeownersMultiplePerilDirectPremiumsWritten>
<stat:explAKCommercialMultiplePerilNonLiabilityPortionDirectPremiumsWritten>0</stat:explAKCommercialMultiplePerilNonLiabilityPortionDirectPremiumsWritten>
<stat:explAKCommercialMultiplePerilLiabilityPortionDirectPremiumsWritten>0</stat:explAKCommercialMultiplePerilLiabilityPortionDirectPremiumsWritten>
<stat:explAKOceanMarineDirectPremiumsWritten>0</stat:explAKOceanMarineDirectPremiumsWritten>
<stat:explAKInlandMarineDirectPremiumsWritten>0</stat:explAKInlandMarineDirectPremiumsWritten>
<stat:explAKEarthquakeDirectPremiumsWritten>0</stat:explAKEarthquakeDirectPremiumsWritten>
<stat:explAKWorkersCompensationDirectPremiumsWritten>-13203</stat:explAKWorkersCompensationDirectPremiumsWritten>
<stat:explAKOtherLiabilityOccurrenceDirectPremiumsWritten>-1765</stat:explAKOtherLiabilityOccurrenceDirectPremiumsWritten>
<stat:explAKOtherLiabilityClaimsMadeDirectPremiumsWritten>0</stat:explAKOtherLiabilityClaimsMadeDirectPremiumsWritten>
<stat:explAKExcessWorkersCompensationDirectPremiumsWritten>0</stat:explAKExcessWorkersCompensationDirectPremiumsWritten>
<stat:explAKProductsLiabilityDirectPremiumsWritten>0</stat:explAKProductsLiabilityDirectPremiumsWritten>
<stat:explAKPrivatePassengerAutoNoFaultDirectPremiumsWritten>0</stat:explAKPrivatePassengerAutoNoFaultDirectPremiumsWritten>
<stat:explAKOtherPrivatePassengerAutoLiabilityDirectPremiumsWritten>0</stat:explAKOtherPrivatePassengerAutoLiabilityDirectPremiumsWritten>
<stat:explAKCommercialAutoNoFaultDirectPremiumsWritten>0</stat:explAKCommercialAutoNoFaultDirectPremiumsWritten>
<stat:explAKOtherCommercialAutoLiabilityDirectPremiumsWritten>0</stat:explAKOtherCommercialAutoLiabilityDirectPremiumsWritten>
<stat:explAKPrivatePassengerAutoPhysicalDamageDirectPremiumsWritten>0</stat:explAKPrivatePassengerAutoPhysicalDamageDirectPremiumsWritten>
<stat:explAKCommercialAutoPhysicalDamageDirectPremiumsWritten>0</stat:explAKCommercialAutoPhysicalDamageDirectPremiumsWritten>
<stat:explAKAircraftDirectPremiumsWritten>0</stat:explAKAircraftDirectPremiumsWritten>
<stat:explAKFidelityDirectPremiumsWritten>0</stat:explAKFidelityDirectPremiumsWritten>
<stat:explAKSuretyDirectPremiumsWritten>-1575</stat:explAKSuretyDirectPremiumsWritten>
<stat:explAKBurglaryAndTheftDirectPremiumsWritten>0</stat:explAKBurglaryAndTheftDirectPremiumsWritten>
<stat:explAKBoilerAndMachineryDirectPremiumsWritten>0</stat:explAKBoilerAndMachineryDirectPremiumsWritten>
<stat:explTotalsADirectPremiumsWritten>sum of all above elements for ergion AK</stat:explTotalsADirectPremiumsWritten>
continue next region....
Till now I have an xslt which is doing sum of individual elements but I am not sure how I can add all derived elements at the end.
The xslt is like this -
<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:wd="urn:com.workday.report/Page_14_Direct_Premiums_Written"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:statutorytype="us-stat-2020-annual-stmt-pc"
xmlns:xbrli="http://www.xbrl.org/2001/instance" xmlns:iso4217="http://www.xe.com/iso4217.htm"
xmlns:stat="urn:www-iasa-org/tempuri" exclude-result-prefixes="xs" version="2.0">
<xsl:output omit-xml-declaration="yes" indent="yes" method="xml"/>
<xsl:template match="node() | @*">
<xsl:copy>
<xsl:apply-templates select="node() | @*"/>
</xsl:copy>
</xsl:template>
<xsl:template match="wd:Report_Data">
<Direct_Premiums_Written>
<xsl:for-each-group select="wd:Report_Entry" group-by="wd:Region">
<xsl:sort select="wd:Region" order="ascending"/>
<xsl:variable name="tag1">
<xsl:value-of
select="concat('stat:expl', current-group()[1]/wd:Region, 'FireDirectPremiumsWritten')"
/>
</xsl:variable>
<xsl:element name="{$tag1}">
<xsl:value-of
select="sum(current-group()/wd:Fire_DPW)"/>
</xsl:element>
<xsl:variable name="tag2">
<xsl:value-of
select="concat('stat:expl', current-group()[1]/wd:Region, 'AlliedLinesDirectPremiumsWritten')"
/>
</xsl:variable>
<xsl:element name="{$tag2}">
<xsl:value-of
select="sum(current-group()/wd:Allied_DPW)"/>
</xsl:element>
<xsl:variable name="tag3">
<xsl:value-of
select="concat('stat:expl', current-group()[1]/wd:Region, 'PrivateFloodDirectPremiumsWritten')"
/>
</xsl:variable>
<xsl:element name="{$tag3}">
<xsl:value-of
select="sum(current-group()/wd:Private_Flood_DPW)"/>
</xsl:element>
<xsl:variable name="tag4">
<xsl:value-of
select="concat('stat:expl', current-group()[1]/wd:Region, 'FarmownersMultiplePerilDirectPremiumsWritten')"
/>
</xsl:variable>
<xsl:element name="{$tag4}">
<xsl:value-of
select="sum(current-group()/wd:Farmowners_Multiple_Peril_DPW)"/>
</xsl:element>
<xsl:variable name="tag5">
<xsl:value-of
select="concat('stat:expl', current-group()[1]/wd:Region, 'HomeownersMultiplePerilDirectPremiumsWritten')"
/>
</xsl:variable>
<xsl:element name="{$tag5}">
<xsl:value-of
select="sum(current-group()/wd:Homeowners_Multiple_Peril_DPW)"/>
</xsl:element>
<xsl:variable name="tag6">
<xsl:value-of
select="concat('stat:expl', current-group()[1]/wd:Region, 'CommercialMultiplePerilNonLiabilityPortionDirectPremiumsWritten')"
/>
</xsl:variable>
<xsl:element name="{$tag6}">
<xsl:value-of
select="sum(current-group()/wd:Commercial_Multiple_Peril_Non-Liability_DPW)"/>
</xsl:element>
<xsl:variable name="tag7">
<xsl:value-of
select="concat('stat:expl', current-group()[1]/wd:Region, 'CommercialMultiplePerilLiabilityPortionDirectPremiumsWritten')"
/>
</xsl:variable>
<xsl:element name="{$tag7}">
<xsl:value-of
select="sum(current-group()/wd:CF_SCommercial_Multiple_Peril_Liability_DPW)"/>
</xsl:element>
<xsl:variable name="tag8">
<xsl:value-of
select="concat('stat:expl', current-group()[1]/wd:Region, 'OceanMarineDirectPremiumsWritten')"
/>
</xsl:variable>
<xsl:element name="{$tag8}">
<xsl:value-of
select="sum(current-group()/wd:Ocean_Marine_DPW)"/>
</xsl:element>
<xsl:variable name="tag9">
<xsl:value-of
select="concat('stat:expl', current-group()[1]/wd:Region, 'InlandMarineDirectPremiumsWritten')"
/>
</xsl:variable>
<xsl:element name="{$tag9}">
<xsl:value-of
select="sum(current-group()/wd:Inland_Marine_DPW)"/>
</xsl:element>
<xsl:variable name="tag10">
<xsl:value-of
select="concat('stat:expl', current-group()[1]/wd:Region, 'EarthquakeDirectPremiumsWritten')"
/>
</xsl:variable>
<xsl:element name="{$tag10}">
<xsl:value-of
select="sum(current-group()/wd:Earthquake_DPW)"/>
</xsl:element>
<xsl:variable name="tag11">
<xsl:value-of
select="concat('stat:expl', current-group()[1]/wd:Region, 'WorkersCompensationDirectPremiumsWritten')"
/>
</xsl:variable>
<xsl:element name="{$tag11}">
<xsl:value-of
select="sum(current-group()/wd:Workers_Compensation_DPW)"/>
</xsl:element>
<xsl:variable name="tag12">
<xsl:value-of
select="concat('stat:expl', current-group()[1]/wd:Region, 'OtherLiabilityOccurrenceDirectPremiumsWritten')"
/>
</xsl:variable>
<xsl:element name="{$tag12}">
<xsl:value-of
select="sum(current-group()/wd:Other_Liability_Occurrence_DPW)"/>
</xsl:element>
<xsl:variable name="tag13">
<xsl:value-of
select="concat('stat:expl', current-group()[1]/wd:Region, 'OtherLiabilityClaimsMadeDirectPremiumsWritten')"
/>
</xsl:variable>
<xsl:element name="{$tag13}">
<xsl:value-of
select="sum(current-group()/wd:Other_Liability_Claims_Made_DPW)"/>
</xsl:element>
<xsl:variable name="tag14">
<xsl:value-of
select="concat('stat:expl', current-group()[1]/wd:Region, 'ExcessWorkersCompensationDirectPremiumsWritten')"
/>
</xsl:variable>
<xsl:element name="{$tag14}">
<xsl:value-of
select="sum(current-group()/wd:Excess_Workers_Compensation_DPW)"/>
</xsl:element>
<xsl:variable name="tag15">
<xsl:value-of
select="concat('stat:expl', current-group()[1]/wd:Region, 'ProductsLiabilityDirectPremiumsWritten')"
/>
</xsl:variable>
<xsl:element name="{$tag15}">
<xsl:value-of
select="sum(current-group()/wd:Products_Liability_Occurrence_State_Page_DPW)"/>
</xsl:element>
<xsl:variable name="tag16">
<xsl:value-of
select="concat('stat:expl', current-group()[1]/wd:Region, 'PrivatePassengerAutoNoFaultDirectPremiumsWritten')"
/>
</xsl:variable>
<xsl:element name="{$tag16}">
<xsl:value-of
select="sum(current-group()/wd:Private_Passenger_Auto_No_Fault_DPW)"/>
</xsl:element>
<xsl:variable name="tag17">
<xsl:value-of
select="concat('stat:expl', current-group()[1]/wd:Region, 'OtherPrivatePassengerAutoLiabilityDirectPremiumsWritten')"
/>
</xsl:variable>
<xsl:element name="{$tag17}">
<xsl:value-of
select="sum(current-group()/wd:Private_Passenger_Auto_Liability_Other_DPW)"/>
</xsl:element>
<xsl:variable name="tag18">
<xsl:value-of
select="concat('stat:expl', current-group()[1]/wd:Region, 'CommercialAutoNoFaultDirectPremiumsWritten')"
/>
</xsl:variable>
<xsl:element name="{$tag18}">
<xsl:value-of
select="sum(current-group()/wd:Commercial_Auto_No_Fault_DPW)"/>
</xsl:element>
<xsl:variable name="tag19">
<xsl:value-of
select="concat('stat:expl', current-group()[1]/wd:Region, 'OtherCommercialAutoLiabilityDirectPremiumsWritten')"
/>
</xsl:variable>
<xsl:element name="{$tag19}">
<xsl:value-of
select="sum(current-group()/wd:Commercial_Auto_Liability_Other_DPW)"/>
</xsl:element>
<xsl:variable name="tag20">
<xsl:value-of
select="concat('stat:expl', current-group()[1]/wd:Region, 'PrivatePassengerAutoPhysicalDamageDirectPremiumsWritten')"
/>
</xsl:variable>
<xsl:element name="{$tag20}">
<xsl:value-of
select="sum(current-group()/wd:Private_Passenger_Auto_Physical_Damage_DPW)"/>
</xsl:element>
<xsl:variable name="tag21">
<xsl:value-of
select="concat('stat:expl', current-group()[1]/wd:Region, 'CommercialAutoPhysicalDamageDirectPremiumsWritten')"
/>
</xsl:variable>
<xsl:element name="{$tag21}">
<xsl:value-of
select="sum(current-group()/wd:Commercial_Auto_Physical_Damage_DPW)"/>
</xsl:element>
<xsl:variable name="tag22">
<xsl:value-of
select="concat('stat:expl', current-group()[1]/wd:Region, 'AircraftDirectPremiumsWritten')"
/>
</xsl:variable>
<xsl:element name="{$tag22}">
<xsl:value-of
select="sum(current-group()/wd:Aircraft_DPW)"/>
</xsl:element>
<xsl:variable name="tag23">
<xsl:value-of
select="concat('stat:expl', current-group()[1]/wd:Region, 'FidelityDirectPremiumsWritten')"
/>
</xsl:variable>
<xsl:element name="{$tag23}">
<xsl:value-of
select="sum(current-group()/wd:Fidelity_DPW)"/>
</xsl:element>
<xsl:variable name="tag24">
<xsl:value-of
select="concat('stat:expl', current-group()[1]/wd:Region, 'SuretyDirectPremiumsWritten')"
/>
</xsl:variable>
<xsl:element name="{$tag24}">
<xsl:value-of
select="sum(current-group()/wd:Surety_DPW)"/>
</xsl:element>
<xsl:variable name="tag25">
<xsl:value-of
select="concat('stat:expl', current-group()[1]/wd:Region, 'BurglaryAndTheftDirectPremiumsWritten')"
/>
</xsl:variable>
<xsl:element name="{$tag25}">
<xsl:value-of
select="sum(current-group()/wd:Burglary_Theft_DPW)"/>
</xsl:element>
<xsl:variable name="tag26">
<xsl:value-of
select="concat('stat:expl', current-group()[1]/wd:Region, 'BoilerAndMachineryDirectPremiumsWritten')"
/>
</xsl:variable>
<xsl:element name="{$tag26}">
<xsl:value-of
select="sum(current-group()/wd:Boiler_Machinery_DPW)"/>
</xsl:element>
</xsl:for-each-group>
</Direct_Premiums_Written>
</xsl:template>
</xsl:stylesheet>
Any help is appreciated.
CodePudding user response：
I would use an inner grouping
<xsl:variable name="region" select="current-grouping-key()"/>
<xsl:for-each-group select="current-group()/*[. castable as xs:decimal]" group-by="node-name(.)">
<xsl:element name="stat:expl{$region}{replace(local-name(), '_', '')}">
<xsl:value-of select="sum(current-group())"/>
</xsl:element>
</xsl:for-each-group>
plus
<stat:explTotalsADirectPremiumsWritten>
<xsl:value-of select="sum(current-group()/*[. castable as xs:decimal])"/>>
</stat:explTotalsADirectPremiumsWritten>
for the totals of all elements.