Grouping on Multiple Field

[nisargmca]

Hi , I am generating XML file from Database as below..

<?xml version = '1.0'?>
<T0019>
   <IFTA_ACCOUNT>
      <IFTA_CARRIER_ID_NUMBER>705</IFTA_CARRIER_ID_NUMBER>
      <IFTA_LICENSE_NUMBER>631227666</IFTA_LICENSE_NUMBER>
      <IFTA_BASE_COUNTRY>US</IFTA_BASE_COUNTRY>
      <IFTA_BASE_STATE>AL</IFTA_BASE_STATE>
      <IFTA_STATUS_CODE>0  </IFTA_STATUS_CODE>
      <IFTA_STATUS_DATE>2009-01-01</IFTA_STATUS_DATE>
      <IFTA_ISSUE_DATE>2009-01-01</IFTA_ISSUE_DATE>
      <IFTA_EXPIRE_DATE>2009-12-01</IFTA_EXPIRE_DATE>
      <IFTA_UPDATE_DATE>2008-12-30</IFTA_UPDATE_DATE>
      <NAME_TYPE>LG</NAME_TYPE>
      <NAME>K D L TRUCKING INC</NAME>
      <ADDRESS_TYPE>PH</ADDRESS_TYPE>
      <STREET_LINE_1>200 MARTIN LANE</STREET_LINE_1>
      <CITY>OHATCHEE</CITY>
      <STATE>AL</STATE>
      <ZIP_CODE>36271</ZIP_CODE>     
      <COUNTY>CALHOUN COUNTY</COUNTY>
      <COUNTRY>US</COUNTRY>
  </IFTA_ACCOUNT>

   <IFTA_ACCOUNT>
      <IFTA_CARRIER_ID_NUMBER>705</IFTA_CARRIER_ID_NUMBER>
      <IFTA_LICENSE_NUMBER>631227666</IFTA_LICENSE_NUMBER>
      <IFTA_BASE_COUNTRY>US</IFTA_BASE_COUNTRY>
      <IFTA_BASE_STATE>AL</IFTA_BASE_STATE>
      <IFTA_STATUS_CODE>0  </IFTA_STATUS_CODE>
      <IFTA_STATUS_DATE>2009-01-01</IFTA_STATUS_DATE>
      <IFTA_ISSUE_DATE>2009-01-01</IFTA_ISSUE_DATE>
      <IFTA_EXPIRE_DATE>2009-12-01</IFTA_EXPIRE_DATE>
      <IFTA_UPDATE_DATE>2008-12-30</IFTA_UPDATE_DATE>
      <NAME_TYPE>LG</NAME_TYPE>
      <NAME>K D L TRUCKING INC</NAME>
      <ADDRESS_TYPE>MA</ADDRESS_TYPE>
      <STREET_LINE_1>200 MARTIN LANE</STREET_LINE_1>
      <CITY>OHATCHEE</CITY>
      <STATE>AL</STATE>
      <ZIP_CODE>36271</ZIP_CODE>
      <COUNTRY>US</COUNTRY>
    </IFTA_ACCOUNT>    
</T0019>

Now I want to group the IFTA_ACCOUNT on IFTA_LICENSE_NU MBER,IFTA_BASE_ COUNTRY and IFTA_BASE_STATE if they matches then it will look for Name Type if it will match then Address Details will be under Name Type

Here is the Desite Output....

<?xml version="1.0" encoding="UTF-8" ?> 
 <T0019>
    <IFTA_ACCOUNT>
      <IFTA_CARRIER_ID_NUMBER>705</IFTA_CARRIER_ID_NUMBER> 
      <IFTA_BASE_COUNTRY>US</IFTA_BASE_COUNTRY> 
      <IFTA_BASE_STATE>AL</IFTA_BASE_STATE> 
      <IFTA_LICENSE_NUMBER>631227666</IFTA_LICENSE_NUMBER> 
      <IFTA_STATUS_CODE>0</IFTA_STATUS_CODE> 
      <IFTA_STATUS_DATE>2009-01-01</IFTA_STATUS_DATE> 
      <IFTA_ISSUE_DATE>2009-01-01</IFTA_ISSUE_DATE> 
      <IFTA_EXPIRE_DATE>2009-12-01</IFTA_EXPIRE_DATE> 
      <IFTA_UPDATE_DATE>2008-12-30</IFTA_UPDATE_DATE> 
     <IFTA_NAME>
      <NAME_TYPE>LG</NAME_TYPE> 
      <NAME>K D L TRUCKING INC</NAME> 
         <IFTA_ADDRESS>
              <ADDRESS_TYPE>PH</ADDRESS_TYPE> 
              <STREET_LINE_1>200 MARTIN LANE</STREET_LINE_1> 
              <STREET_LINE_2 /> 
              <CITY>OHATCHEE</CITY> 
              <STATE>AL</STATE> 
              <ZIP_CODE>36271</ZIP_CODE> 
              <COUNTY>CALHOUN COUNTY</COUNTY> 
              <COUNTRY>US</COUNTRY> 
          </IFTA_ADDRESS>
         <IFTA_ADDRESS>
              <ADDRESS_TYPE>MA</ADDRESS_TYPE> 
              <STREET_LINE_1>200 MARTIN LANE</STREET_LINE_1> 
              <STREET_LINE_2 /> 
              <CITY>OHATCHEE</CITY> 
              <STATE>AL</STATE> 
              <ZIP_CODE>36271</ZIP_CODE> 
              <COUNTY /> 
              <COUNTRY>US</COUNTRY> 
          </IFTA_ADDRESS>
      </IFTA_NAME>
  </IFTA_ACCOUNT>
   </T0019>

Thanks...

[jkmyoung]

What technology are you using to modify the XML? If XSLT 1.0, look into Muenchian grouping. If XSLT 2.0, look into the for-each-group functions. If something else, please specify.

[nisargmca]

I am using XSLT 2.0

[jkmyoung]

I'm not an expert in xslt 2.0, but I'd think it'd look something like:

<xsl:for-each-group select="IFTA_ACCOUNT" group-by="IFTA_LICENSE_NUMBER">
  <xsl:variable name="license" select="current-grouping-key()"/>
     <xsl:for-each-group select="current-group()/." group-by="IFTA_BASE_COUNTRY"> 
    <xsl:variable name="country" select="current-grouping-key()"/>
...

I am however confused by your result: You state that you are grouping on IFTA_LICENSE_NU MBER,IFTA_BASE_ COUNTRY and IFTA_BASE_STATE , but in your element at the base level, you have:

     <IFTA_CARRIER_ID_NUMBER>705</IFTA_CARRIER_ID_NUMBER>  
      <IFTA_BASE_COUNTRY>US</IFTA_BASE_COUNTRY>  
      <IFTA_BASE_STATE>AL</IFTA_BASE_STATE>  
      <IFTA_LICENSE_NUMBER>631227666</IFTA_LICENSE_NUMBER>  
      <IFTA_STATUS_CODE>0</IFTA_STATUS_CODE>  
      <IFTA_STATUS_DATE>2009-01-01</IFTA_STATUS_DATE>  
      <IFTA_ISSUE_DATE>2009-01-01</IFTA_ISSUE_DATE>  
      <IFTA_EXPIRE_DATE>2009-12-01</IFTA_EXPIRE_DATE>  
      <IFTA_UPDATE_DATE>2008-12-30</IFTA_UPDATE_DATE>

Will those values automatically be the same if the first 3 match? Please clarify.

[nisargmca]

Thanks for reply jkmyoung,

I have solved this with Grouping

<xsl:stylesheet
    version="2.0"
    xmlns:xsl="http://www.w3.org/1999/XSL/Transform"> 
    
    <xsl:template match="T0019">
        <xsl:copy>
            <xsl:for-each-group select="IFTA_ACCOUNT" group-by="IFTA_LICENSE_NUMBER">
                <xsl:for-each-group select="current-group()" group-by="IFTA_BASE_COUNTRY">
                    <xsl:for-each-group select="current-group()" group-by="IFTA_BASE_STATE">
                        <IFTA_ACCOUNT>
                            <xsl:apply-templates select="IFTA_CARRIER_ID_NUMBER|IFTA_BASE_COUNTRY|IFTA_BASE_STATE|IFTA_LICENSE_NUMBER|IFTA_STATUS_CODE|IFTA_STATUS_DATE|IFTA_ISSUE_DATE|IFTA_EXPIRE_DATE|IFTA_UPDATE_DATE"/>
                            <xsl:for-each-group select="current-group()" group-by="NAME_TYPE">
                                <IFTA_NAME>
                                    <xsl:apply-templates select="NAME_TYPE|NAME"/>
                                    <xsl:for-each select="current-group()">
                                        <IFTA_ADDRESS>
                                            <xsl:apply-templates select="ADDRESS_TYPE|STREET_LINE_1|STREET_LINE_2|CITY|STATE|ZIP_CODE|COUNTY|COUNTRY"/>                                            
                                        </IFTA_ADDRESS>                                        
                                    </xsl:for-each>                                    
                                </IFTA_NAME>
                            </xsl:for-each-group>                            
                        </IFTA_ACCOUNT>                        
                    </xsl:for-each-group>                    
                </xsl:for-each-group>  
            </xsl:for-each-group>
        </xsl:copy>
        
    </xsl:template>
    <xsl:template match="IFTA_CARRIER_ID_NUMBER">
        <IFTA_CARRIER_ID_NUMBER>
            <xsl:value-of select="."/>
        </IFTA_CARRIER_ID_NUMBER>            
    </xsl:template>       
    <xsl:template match="IFTA_LICENSE_NUMBER">
        <IFTA_LICENSE_NUMBER>
            <xsl:value-of select="."/>
        </IFTA_LICENSE_NUMBER>            
    </xsl:template>   
    <xsl:template match="IFTA_BASE_COUNTRY">
        <IFTA_BASE_COUNTRY>
            <xsl:value-of select="."/>
        </IFTA_BASE_COUNTRY>                        
    </xsl:template>   
    <xsl:template match="IFTA_BASE_STATE">
        <IFTA_BASE_STATE>
            <xsl:value-of select="."/>
        </IFTA_BASE_STATE>            
    </xsl:template>   
    <xsl:template match="IFTA_STATUS_CODE">
        <IFTA_STATUS_CODE>
            <xsl:value-of select="."/>
        </IFTA_STATUS_CODE>            
    </xsl:template>   
    <xsl:template match="IFTA_STATUS_DATE">
        <IFTA_STATUS_DATE>
            <xsl:value-of select="."/> 
        </IFTA_STATUS_DATE>            
    </xsl:template>   
    <xsl:template match="IFTA_ISSUE_DATE">
        <IFTA_ISSUE_DATE>
            <xsl:value-of select="."/>
        </IFTA_ISSUE_DATE>            
    </xsl:template>
    <xsl:template match="IFTA_EXPIRE_DATE">
        <IFTA_STATUS_DATE>
            <xsl:value-of select="."/> 
        </IFTA_STATUS_DATE>            
    </xsl:template>   
    <xsl:template match="IFTA_UPDATE_DATE">
        <IFTA_ISSUE_DATE>
            <xsl:value-of select="."/>
        </IFTA_ISSUE_DATE>            
    </xsl:template>	
    <xsl:template match="NAME_TYPE">
        <NAME_TYPE>
            <xsl:value-of select="."/>
        </NAME_TYPE>            
    </xsl:template>
    <xsl:template match="NAME">
        <NAME_TYPE>
            <xsl:value-of select="."/>
        </NAME_TYPE>            
    </xsl:template> 	
    <xsl:template match="ADDRESS_TYPE">
        <ADDRESS_TYPE>
            <xsl:value-of select="."/>
        </ADDRESS_TYPE>            
    </xsl:template>
    
    <xsl:template match="STREET_LINE_1">
        <STREET_LINE_1>
            <xsl:value-of select="."/>
        </STREET_LINE_1>            
    </xsl:template>
    <xsl:template match="STREET_LINE_2">
        <STREET_LINE_2>
            <xsl:value-of select="."/>
        </STREET_LINE_2>            
    </xsl:template>
    <xsl:template match="CITY">
        <CITY>
            <xsl:value-of select="."/> 
        </CITY>            
    </xsl:template>   
    <xsl:template match="STATE">
        <STATE>
            <xsl:value-of select="."/>
        </STATE>            
    </xsl:template>	
    <xsl:template match="ZIP_CODE">
        <ZIP_CODE>
            <xsl:value-of select="."/>
        </ZIP_CODE>            
    </xsl:template>  
    <xsl:template match="COUNTY">
        <COUNTY>
            <xsl:value-of select="."/>
        </COUNTY>            
    </xsl:template>
    <xsl:template match="COUNTRY">
        <COUNTRY>
            <xsl:value-of select="."/>
        </COUNTRY>            
    </xsl:template>    
</xsl:stylesheet>

I will try with your solution also to best solution..

Thanks

Nisarg