XSLT transformation: Grouping coverage codes by vehicle category and calculating premium split

Question

Grouping in XSLT

Need to create a XSLT for below Request XML.

XSLT has to do below:

1. Group the Coverage codes based on vehicle category.
2. Sum up premium per coverage code
3. Sum up premium per coverage code and vehicle category
4. Get the Vehicle category based on vehicle sequence number.
5. calculate split percent based on values derived from 2 and 3 points.

InputXML:

<Root>
  <Request>
    <Vehicles>
      <Vehicle>
        <VehicleSequenceNo>1</VehicleSequenceNo>
        <VehicleCategory>Tractor</VehicleCategory>
      </Vehicle>
      <Vehicle>
        <VehicleSequenceNo>2</VehicleSequenceNo>
        <VehicleCategory>Tractor</VehicleCategory>
      </Vehicle>
      <Vehicle>
        <VehicleSequenceNo>3</VehicleSequenceNo>
        <VehicleCategory>Trailer</VehicleCategory>
      </Vehicle>
    </Vehicles>
    <Policies>
      <Policy>
        <PrimaryAuto>
          <Liability>
            <Exposures>
              <Vehicle>
                <VehicleSequenceNo>1</VehicleSequenceNo>
                <CoverageProvided>
                  <CoverageCode>L1</CoverageCode>
                  <Premium>100</Premium>
                </CoverageProvided>
              </Vehicle>
              <Vehicle>
                <VehicleSequenceNo>2</VehicleSequenceNo>
                <CoverageProvided>
                  <CoverageCode>L1</CoverageCode>
                  <Premium>200</Premium>
                </CoverageProvided>
              </Vehicle>
              <Vehicle>
                <VehicleSequenceNo>3</VehicleSequenceNo>
                <CoverageProvided>
                  <CoverageCode>L1</CoverageCode>
                  <Premium>150</Premium>
                </CoverageProvided>
                <CoverageProvided>
                  <CoverageCode>UI</CoverageCode>
                  <Premium>140</Premium>
                </CoverageProvided>
              </Vehicle>
            </Exposures>
          </Liability>
        </PrimaryAuto>
      </Policy>
    </Policies>
  </Request>
</Root>

Expected Output:

<PremiumSplit>
    <Liability>
        <Vehicle>
            <CoverageSection>Liability</CoverageSection>
            <CoverageCode>L1</CoverageCode>
            <!-- 100 + 200 + 150 = 450 (sum of all L1 code premium) , 
            100 + 200 = 300 (sum of Tractor L1 premium) , 
            450 / 300 = 1.5 -->
            <CoveragePercent>1.5</CoveragePercent>          
            <VehicleCategory>Tractor</VehicleCategory>
        </Vehicle>
        <Vehicle>
            <CoverageSection>Liability</CoverageSection>
            <CoverageCode>L1</CoverageCode>
            <!-- 100 + 200 + 150 = 450 (sum of all L1 code premium) , 
            150 (Trailer L1 premium) , 
            450 / 150 = 3 -->
            <CoveragePercent>3</CoveragePercent>        
            <VehicleCategory>Trailer</VehicleCategory>
        </Vehicle>
        <Vehicle>
            <CoverageSection>Liability</CoverageSection>
            <CoverageCode>UI</CoverageCode>
            <!-- 140 (Trailer UI premium) / 140 (Trailer UI premium) = 1 -->
            <CoveragePercent>1</CoveragePercent>            
            <VehicleCategory>Trailer</VehicleCategory>
        </Vehicle>
    </Liability>
</PremiumSplit>

I tried foreach on Root/Request/Policies/Policy/PrimaryAuto/Liability/Exposures/Vehicle/CoverageProvided and extracted CoverageCode and VehicleSequenceNumber.

Now I need to fetch Vehicle category for the extracted VehicleSequenceNumber from Root/Request/Vehicles/Vehicle

also need to find duplicate code and do premium addition and percentage calculation

Answer 1

The following XSLT 1.0 solution uses keys (with self-explanatory names) to determine the coverages per code and category and compares the generate-id() values to select only the first coverage for a given code or code-category combination.

It may no longer work as desired if the input XML contains more than one <Exposures> element. For a full solution, a full specification of the meaning of the elements in the input XML would be needed. But the given solution can serve as a starting point.

More elegant solutions are possible with XSLT 2.0 or 3.0, if you are able to use that.

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
  <xsl:output indent="yes"/>
  <xsl:key name="category-by-vehicle" match="VehicleCategory"
    use="../VehicleSequenceNo" />
  <xsl:key name="coverages-per-code" match="CoverageProvided"
    use="CoverageCode" />
  <xsl:key name="coverages-per-code-and-category" match="CoverageProvided"
    use="concat(CoverageCode,' ',key('category-by-vehicle',../VehicleSequenceNo))" />
  <xsl:template match="/">
    <PremiumSplit>
      <Liability>
        <!-- Process only the first <CoverageProvided> with a given <CoverageCode>. -->
        <xsl:apply-templates select="descendant::CoverageProvided
          [generate-id()=generate-id(key('coverages-per-code',CoverageCode)[1])]"
          mode="per-code" />
      </Liability>
    </PremiumSplit>
  </xsl:template>
  <xsl:template match="CoverageProvided" mode="per-code">
     <!-- Process only the first <CoverageProvided> with the current <CoverageCode>
          and a given <VehicleCategory>. -->
    <xsl:apply-templates select="key('coverages-per-code',CoverageCode)
      [generate-id()=generate-id(key('coverages-per-code-and-category',
        concat(CoverageCode,' ',key('category-by-vehicle',../VehicleSequenceNo)))[1])]"
      mode="per-code-and-category" />
  </xsl:template>
  <xsl:template match="CoverageProvided" mode="per-code-and-category">
    <xsl:variable name="category" select="key('category-by-vehicle',../VehicleSequenceNo)" />
    <!-- Compute the sums of <Premium>s described by steps 2 and 3. -->
    <xsl:variable name="step2"
      select="sum(key('coverages-per-code',CoverageCode)/Premium)" />
    <xsl:variable name="step3"
      select="sum(key('coverages-per-code-and-category',
        concat(CoverageCode,' ',$category))/Premium)" />
    <Vehicle>
      <CoverageSection>Liability</CoverageSection>
      <xsl:copy-of select="CoverageCode" />
      <CoveragePercent>
        <xsl:value-of select="$step2 div $step3" />
      </CoveragePercent>
      <xsl:copy-of select="$category" />
    </Vehicle>
  </xsl:template>
</xsl:stylesheet>

Answer 2

Using XSLT 3 (as supported by current editions of Saxon on the Java, .NET, Python platform and by SaxonJS in the browser):

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="3.0"
  xmlns:xs="http://www.w3.org/2001/XMLSchema"
  exclude-result-prefixes="#all"
  expand-text="yes">

  <xsl:mode on-no-match="shallow-skip"/>

  <xsl:output indent="yes"/>
  
  <xsl:key name="vehicle-category" match="Vehicles/Vehicle/VehicleCategory" use="../VehicleSequenceNo"/>
  
  <xsl:template match="Liability">
    <xsl:copy>
      <xsl:for-each-group select="Exposures/Vehicle/CoverageProvided" group-by="CoverageCode">
        <xsl:variable name="code-sum" select="sum(current-group()/Premium)"/>
        <xsl:variable name="coverage-code" select="current-grouping-key()"/>
        <xsl:for-each-group select="current-group()" group-by="key('vehicle-category', ../VehicleSequenceNo)">
           <Vehicle>
             <CoverageSection>Liability</CoverageSection>
             <CoverageCode>{$coverage-code}</CoverageCode>
             <CoveragePercent>{$code-sum div sum(current-group()/Premium)}</CoveragePercent>
             <VehicleCategory>{current-grouping-key()}</VehicleCategory>
           </Vehicle>
         </xsl:for-each-group>           
      </xsl:for-each-group>
    </xsl:copy>
  </xsl:template>

  <xsl:template match="/">
    <PremiumSplit>
      <xsl:apply-templates/>
    </PremiumSplit>
  </xsl:template>

</xsl:stylesheet>