469,275 Members | 1,709 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,275 developers. It's quick & easy.

Grouping on Multiple Field

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

Expand|Select|Wrap|Line Numbers
  1. <?xml version = '1.0'?>
  2. <T0019>
  3.    <IFTA_ACCOUNT>
  4.       <IFTA_CARRIER_ID_NUMBER>705</IFTA_CARRIER_ID_NUMBER>
  5.       <IFTA_LICENSE_NUMBER>631227666</IFTA_LICENSE_NUMBER>
  6.       <IFTA_BASE_COUNTRY>US</IFTA_BASE_COUNTRY>
  7.       <IFTA_BASE_STATE>AL</IFTA_BASE_STATE>
  8.       <IFTA_STATUS_CODE>0  </IFTA_STATUS_CODE>
  9.       <IFTA_STATUS_DATE>2009-01-01</IFTA_STATUS_DATE>
  10.       <IFTA_ISSUE_DATE>2009-01-01</IFTA_ISSUE_DATE>
  11.       <IFTA_EXPIRE_DATE>2009-12-01</IFTA_EXPIRE_DATE>
  12.       <IFTA_UPDATE_DATE>2008-12-30</IFTA_UPDATE_DATE>
  13.       <NAME_TYPE>LG</NAME_TYPE>
  14.       <NAME>K D L TRUCKING INC</NAME>
  15.       <ADDRESS_TYPE>PH</ADDRESS_TYPE>
  16.       <STREET_LINE_1>200 MARTIN LANE</STREET_LINE_1>
  17.       <CITY>OHATCHEE</CITY>
  18.       <STATE>AL</STATE>
  19.       <ZIP_CODE>36271</ZIP_CODE>     
  20.       <COUNTY>CALHOUN COUNTY</COUNTY>
  21.       <COUNTRY>US</COUNTRY>
  22.   </IFTA_ACCOUNT>
  23.  
  24.    <IFTA_ACCOUNT>
  25.       <IFTA_CARRIER_ID_NUMBER>705</IFTA_CARRIER_ID_NUMBER>
  26.       <IFTA_LICENSE_NUMBER>631227666</IFTA_LICENSE_NUMBER>
  27.       <IFTA_BASE_COUNTRY>US</IFTA_BASE_COUNTRY>
  28.       <IFTA_BASE_STATE>AL</IFTA_BASE_STATE>
  29.       <IFTA_STATUS_CODE>0  </IFTA_STATUS_CODE>
  30.       <IFTA_STATUS_DATE>2009-01-01</IFTA_STATUS_DATE>
  31.       <IFTA_ISSUE_DATE>2009-01-01</IFTA_ISSUE_DATE>
  32.       <IFTA_EXPIRE_DATE>2009-12-01</IFTA_EXPIRE_DATE>
  33.       <IFTA_UPDATE_DATE>2008-12-30</IFTA_UPDATE_DATE>
  34.       <NAME_TYPE>LG</NAME_TYPE>
  35.       <NAME>K D L TRUCKING INC</NAME>
  36.       <ADDRESS_TYPE>MA</ADDRESS_TYPE>
  37.       <STREET_LINE_1>200 MARTIN LANE</STREET_LINE_1>
  38.       <CITY>OHATCHEE</CITY>
  39.       <STATE>AL</STATE>
  40.       <ZIP_CODE>36271</ZIP_CODE>
  41.       <COUNTRY>US</COUNTRY>
  42.     </IFTA_ACCOUNT>    
  43. </T0019>
Now I want to group the IFTA_ACCOUNT on IFTA_LICENSE_NUMBER,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....

Expand|Select|Wrap|Line Numbers
  1. <?xml version="1.0" encoding="UTF-8" ?> 
  2.  <T0019>
  3.     <IFTA_ACCOUNT>
  4.       <IFTA_CARRIER_ID_NUMBER>705</IFTA_CARRIER_ID_NUMBER> 
  5.       <IFTA_BASE_COUNTRY>US</IFTA_BASE_COUNTRY> 
  6.       <IFTA_BASE_STATE>AL</IFTA_BASE_STATE> 
  7.       <IFTA_LICENSE_NUMBER>631227666</IFTA_LICENSE_NUMBER> 
  8.       <IFTA_STATUS_CODE>0</IFTA_STATUS_CODE> 
  9.       <IFTA_STATUS_DATE>2009-01-01</IFTA_STATUS_DATE> 
  10.       <IFTA_ISSUE_DATE>2009-01-01</IFTA_ISSUE_DATE> 
  11.       <IFTA_EXPIRE_DATE>2009-12-01</IFTA_EXPIRE_DATE> 
  12.       <IFTA_UPDATE_DATE>2008-12-30</IFTA_UPDATE_DATE> 
  13.      <IFTA_NAME>
  14.       <NAME_TYPE>LG</NAME_TYPE> 
  15.       <NAME>K D L TRUCKING INC</NAME> 
  16.          <IFTA_ADDRESS>
  17.               <ADDRESS_TYPE>PH</ADDRESS_TYPE> 
  18.               <STREET_LINE_1>200 MARTIN LANE</STREET_LINE_1> 
  19.               <STREET_LINE_2 /> 
  20.               <CITY>OHATCHEE</CITY> 
  21.               <STATE>AL</STATE> 
  22.               <ZIP_CODE>36271</ZIP_CODE> 
  23.               <COUNTY>CALHOUN COUNTY</COUNTY> 
  24.               <COUNTRY>US</COUNTRY> 
  25.           </IFTA_ADDRESS>
  26.          <IFTA_ADDRESS>
  27.               <ADDRESS_TYPE>MA</ADDRESS_TYPE> 
  28.               <STREET_LINE_1>200 MARTIN LANE</STREET_LINE_1> 
  29.               <STREET_LINE_2 /> 
  30.               <CITY>OHATCHEE</CITY> 
  31.               <STATE>AL</STATE> 
  32.               <ZIP_CODE>36271</ZIP_CODE> 
  33.               <COUNTY /> 
  34.               <COUNTRY>US</COUNTRY> 
  35.           </IFTA_ADDRESS>
  36.       </IFTA_NAME>
  37.   </IFTA_ACCOUNT>
  38.    </T0019>
Thanks...
May 10 '10 #1
4 5721
jkmyoung
2,057 Expert 2GB
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.
May 10 '10 #2
I am using XSLT 2.0
May 11 '10 #3
jkmyoung
2,057 Expert 2GB
I'm not an expert in xslt 2.0, but I'd think it'd look something like:
Expand|Select|Wrap|Line Numbers
  1. <xsl:for-each-group select="IFTA_ACCOUNT" group-by="IFTA_LICENSE_NUMBER">
  2.   <xsl:variable name="license" select="current-grouping-key()"/>
  3.      <xsl:for-each-group select="current-group()/." group-by="IFTA_BASE_COUNTRY"> 
  4.     <xsl:variable name="country" select="current-grouping-key()"/>
  5. ...
I am however confused by your result: You state that you are grouping on IFTA_LICENSE_NUMBER,IFTA_BASE_COUNTRY and IFTA_BASE_STATE, but in your element at the base level, you have:
Expand|Select|Wrap|Line Numbers
  1.      <IFTA_CARRIER_ID_NUMBER>705</IFTA_CARRIER_ID_NUMBER>  
  2.       <IFTA_BASE_COUNTRY>US</IFTA_BASE_COUNTRY>  
  3.       <IFTA_BASE_STATE>AL</IFTA_BASE_STATE>  
  4.       <IFTA_LICENSE_NUMBER>631227666</IFTA_LICENSE_NUMBER>  
  5.       <IFTA_STATUS_CODE>0</IFTA_STATUS_CODE>  
  6.       <IFTA_STATUS_DATE>2009-01-01</IFTA_STATUS_DATE>  
  7.       <IFTA_ISSUE_DATE>2009-01-01</IFTA_ISSUE_DATE>  
  8.       <IFTA_EXPIRE_DATE>2009-12-01</IFTA_EXPIRE_DATE>  
  9.       <IFTA_UPDATE_DATE>2008-12-30</IFTA_UPDATE_DATE>  
Will those values automatically be the same if the first 3 match? Please clarify.
May 11 '10 #4
Thanks for reply jkmyoung,

I have solved this with Grouping

Expand|Select|Wrap|Line Numbers
  1. <xsl:stylesheet
  2.     version="2.0"
  3.     xmlns:xsl="http://www.w3.org/1999/XSL/Transform"> 
  4.  
  5.     <xsl:template match="T0019">
  6.         <xsl:copy>
  7.             <xsl:for-each-group select="IFTA_ACCOUNT" group-by="IFTA_LICENSE_NUMBER">
  8.                 <xsl:for-each-group select="current-group()" group-by="IFTA_BASE_COUNTRY">
  9.                     <xsl:for-each-group select="current-group()" group-by="IFTA_BASE_STATE">
  10.                         <IFTA_ACCOUNT>
  11.                             <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"/>
  12.                             <xsl:for-each-group select="current-group()" group-by="NAME_TYPE">
  13.                                 <IFTA_NAME>
  14.                                     <xsl:apply-templates select="NAME_TYPE|NAME"/>
  15.                                     <xsl:for-each select="current-group()">
  16.                                         <IFTA_ADDRESS>
  17.                                             <xsl:apply-templates select="ADDRESS_TYPE|STREET_LINE_1|STREET_LINE_2|CITY|STATE|ZIP_CODE|COUNTY|COUNTRY"/>                                            
  18.                                         </IFTA_ADDRESS>                                        
  19.                                     </xsl:for-each>                                    
  20.                                 </IFTA_NAME>
  21.                             </xsl:for-each-group>                            
  22.                         </IFTA_ACCOUNT>                        
  23.                     </xsl:for-each-group>                    
  24.                 </xsl:for-each-group>  
  25.             </xsl:for-each-group>
  26.         </xsl:copy>
  27.  
  28.     </xsl:template>
  29.     <xsl:template match="IFTA_CARRIER_ID_NUMBER">
  30.         <IFTA_CARRIER_ID_NUMBER>
  31.             <xsl:value-of select="."/>
  32.         </IFTA_CARRIER_ID_NUMBER>            
  33.     </xsl:template>       
  34.     <xsl:template match="IFTA_LICENSE_NUMBER">
  35.         <IFTA_LICENSE_NUMBER>
  36.             <xsl:value-of select="."/>
  37.         </IFTA_LICENSE_NUMBER>            
  38.     </xsl:template>   
  39.     <xsl:template match="IFTA_BASE_COUNTRY">
  40.         <IFTA_BASE_COUNTRY>
  41.             <xsl:value-of select="."/>
  42.         </IFTA_BASE_COUNTRY>                        
  43.     </xsl:template>   
  44.     <xsl:template match="IFTA_BASE_STATE">
  45.         <IFTA_BASE_STATE>
  46.             <xsl:value-of select="."/>
  47.         </IFTA_BASE_STATE>            
  48.     </xsl:template>   
  49.     <xsl:template match="IFTA_STATUS_CODE">
  50.         <IFTA_STATUS_CODE>
  51.             <xsl:value-of select="."/>
  52.         </IFTA_STATUS_CODE>            
  53.     </xsl:template>   
  54.     <xsl:template match="IFTA_STATUS_DATE">
  55.         <IFTA_STATUS_DATE>
  56.             <xsl:value-of select="."/> 
  57.         </IFTA_STATUS_DATE>            
  58.     </xsl:template>   
  59.     <xsl:template match="IFTA_ISSUE_DATE">
  60.         <IFTA_ISSUE_DATE>
  61.             <xsl:value-of select="."/>
  62.         </IFTA_ISSUE_DATE>            
  63.     </xsl:template>
  64.     <xsl:template match="IFTA_EXPIRE_DATE">
  65.         <IFTA_STATUS_DATE>
  66.             <xsl:value-of select="."/> 
  67.         </IFTA_STATUS_DATE>            
  68.     </xsl:template>   
  69.     <xsl:template match="IFTA_UPDATE_DATE">
  70.         <IFTA_ISSUE_DATE>
  71.             <xsl:value-of select="."/>
  72.         </IFTA_ISSUE_DATE>            
  73.     </xsl:template>    
  74.     <xsl:template match="NAME_TYPE">
  75.         <NAME_TYPE>
  76.             <xsl:value-of select="."/>
  77.         </NAME_TYPE>            
  78.     </xsl:template>
  79.     <xsl:template match="NAME">
  80.         <NAME_TYPE>
  81.             <xsl:value-of select="."/>
  82.         </NAME_TYPE>            
  83.     </xsl:template>     
  84.     <xsl:template match="ADDRESS_TYPE">
  85.         <ADDRESS_TYPE>
  86.             <xsl:value-of select="."/>
  87.         </ADDRESS_TYPE>            
  88.     </xsl:template>
  89.  
  90.     <xsl:template match="STREET_LINE_1">
  91.         <STREET_LINE_1>
  92.             <xsl:value-of select="."/>
  93.         </STREET_LINE_1>            
  94.     </xsl:template>
  95.     <xsl:template match="STREET_LINE_2">
  96.         <STREET_LINE_2>
  97.             <xsl:value-of select="."/>
  98.         </STREET_LINE_2>            
  99.     </xsl:template>
  100.     <xsl:template match="CITY">
  101.         <CITY>
  102.             <xsl:value-of select="."/> 
  103.         </CITY>            
  104.     </xsl:template>   
  105.     <xsl:template match="STATE">
  106.         <STATE>
  107.             <xsl:value-of select="."/>
  108.         </STATE>            
  109.     </xsl:template>    
  110.     <xsl:template match="ZIP_CODE">
  111.         <ZIP_CODE>
  112.             <xsl:value-of select="."/>
  113.         </ZIP_CODE>            
  114.     </xsl:template>  
  115.     <xsl:template match="COUNTY">
  116.         <COUNTY>
  117.             <xsl:value-of select="."/>
  118.         </COUNTY>            
  119.     </xsl:template>
  120.     <xsl:template match="COUNTRY">
  121.         <COUNTRY>
  122.             <xsl:value-of select="."/>
  123.         </COUNTRY>            
  124.     </xsl:template>    
  125. </xsl:stylesheet>

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

Thanks

Nisarg
May 11 '10 #5

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by suzanne shelton | last post: by
1 post views Thread by itm | last post: by
2 posts views Thread by vito | last post: by
2 posts views Thread by dylanhughes | last post: by
3 posts views Thread by Kevin Davis | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.