Grouping on Multiple Field

nisargmca · May 7th, 2010, 08:13 AM

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

Code:

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

I have taken first two records from generated XSLT.
With the use of XSLT I have tried lot to group record on basis of
IFTA_LICENSE_NUMBER,IFTA_BASE_COUNTRY,IFTA_BASE_ST ATE,NAME_TYPE,ADDRESS_TYPE but i failed to generated XML like this...

Code:

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

Can anybody please help me ??

mhkay · May 7th, 2010, 10:33 AM

Are you using XSLT 1.0 or 2.0? Grouping is much easier in 2.0.

It would be useful to know what you have tried, to avoid giving you lots of information that you already know.

nisargmca · May 10th, 2010, 02:42 AM

I have tried in XSLT1.0 but it is not working for me...Can you please give solution in XSLT 2.0

XSLT i have tried is as below ...

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

<xsl:output method="text"/>

<xsl:key name="IFTA_LICENSE_NUMBERS" match="IFTA_ACCOUNT" use="IFTA_LICENSE_NUMBER"/>
<xsl:key name="IFTA_BASE_COUNTRYS" match="IFTA_ACCOUNT" use="IFTA_BASE_COUNTRY"/>

<xsl:template match="/T0019">
<xsl:apply-templates select="IFTA_ACCOUNT[generate-id() = generate-id(key('IFTA_LICENSE_NUMBERS',IFTA_LICENSE_NUMBER)[1])]"/>
</xsl:template>

<xsl:template match="IFTA_ACCOUNT">
<xsl:for-each select="key('IFTA_LICENSE_NUMBERS',IFTA_LICENSE_NU MBER)[generate-id() = generate-id(key('IFTA_BASE_COUNTRYS', IFTA_BASE_COUNTRY)[IFTA_LICENSE_NUMBER=current()/IFTA_LICENSE_NUMBER][1])]">
<xsl:variable name="bills" select="key('IFTA_LICENSE_NUMBERS', IFTA_LICENSE_NUMBER)[IFTA_BASE_COUNTRY = current()/IFTA_BASE_COUNTRY]"/>

<xsl:text><IFTA_LICENSE_NUMBER></xsl:text>
<xsl:value-of select="IFTA_LICENSE_NUMBER"/>
<xsl:text></IFTA_LICENSE_NUMBER></xsl:text>

<xsl:text><IFTA_BASE_COUNTRY></xsl:text>
<xsl:value-of select="IFTA_BASE_COUNTRY"/>
<xsl:text></IFTA_BASE_COUNTRY></xsl:text>

<xsl:text><IFTA_BASE_STATE></xsl:text>
<xsl:value-of select="IFTA_BASE_STATE"/>
<xsl:text></IFTA_BASE_STATE></xsl:text>

<xsl:for-each select="$bills">
<IRP_NAME>
<xsl:text><NAME_TYPE></xsl:text>
<xsl:value-of select="NAME_TYPE"/>
<xsl:text></NAME_TYPE></xsl:text>

<xsl:text><NAME></xsl:text>
<xsl:value-of select="NAME"/>
<xsl:text></NAME></xsl:text>
<IRP_ADDRESS>
<xsl:text><ADDRESS_TYPE></xsl:text>
<xsl:value-of select="ADDRESS_TYPE"/>
<xsl:text></ADDRESS_TYPE></xsl:text>

<xsl:text><STREET_LINE_1></xsl:text>
<xsl:value-of select="STREET_LINE_1"/>
<xsl:text></STREET_LINE_1></xsl:text>

<xsl:text><CITY></xsl:text>
<xsl:value-of select="CITY"/>
<xsl:text></CITY></xsl:text>
</IRP_ADDRESS>
</IRP_NAME>
</xsl:for-each>
</xsl:for-each>
</xsl:template>
</xsl:stylesheet>

Can u pls give me a solution in XSLT 2.0