• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1282
  • Last Modified:

Converting an xml to another xml using xsl and group by function

I have an xml that I need to parse to another xml, I tried to use the group by function, but it traverses just the first iteration

Here is the input XML
XML1
--------
<?xml version="1.0" encoding=utf-8"?>
<Resultsets>
      <Record>
                <App>SmokeTest </App>
                <Location>Germany</Location>
                <Division>Parts</Division>
                <Desk>Help</Desk>
                <Product>Bulldozer</Product>
                <Access>Variable</Access>
      </Record>
      <Record>  
                <App>SmokeTest </App>
               <Location>Germany</Location>
                <Division>Supplies</Division>
                <Desk>Help</Desk>
                <Product>Bulldozer</Product>
                <Access>Fixed</Access>
      </Record>
.
.
.
</Resultsets>

An this is the desired output

Location name "Germany">
     <Division name ="Parts">
         <Desk name ="Help">
            <Product name="XYZ>
                    <Permission name="Variable"></Permission>
                    <Permission name="Test"></Permission>
            </Product>
           <Product name="XYZ>
                    <Permission name="Variable"></Permission>
                    <Permission name="Test"></Permission>
            </Product>
         </Desk>
         <Desk name ="Supplies">
             <Product name="XYZ>
                    <Permission name="Variable"></Permission>
                    <Permission name="Test"></Permission>
            </Product>          
         </Desk>
      </Division>
     <Division name ="Parts">
         <Desk name ="Help">
             <Product name="XYZ>
                    <Permission name="Variable"></Permission>
                    <Permission name="Test"></Permission>
            </Product>
            </Desk>
         <Desk name ="Supplies">
             <Product name="XYZ>
                    <Permission name="Variable"></Permission>
                    <Permission name="Test"></Permission>
            </Product>
         </Desk>
      </Division>
</Location>

And this is what I have so far
<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="2.0">
    <xsl:key name="loc-by-name" match="ResultSet1/*" use="LOCATION"/>
     
    <xsl:template match="/">
        <apps>
            <app>
                <xsl:apply-templates/>
            </app>
        </apps>
    </xsl:template>
    <xsl:template match="ResultSet1">
        <xsl:for-each-group select="*[generate-id() = generate-id(key('loc-by-name', LOCATION)[1])]" group-by="LOCATION">
            <xsl:sort data-type="text" select="LOCATION"/>
            <location name="{current-grouping-key()}">
            <xsl:for-each-group select="current-group()"  group-by="DIVISION">
                                          <xsl:sort data-type="text" select="DIVISION"/>                                          
                                          <division name="{current-grouping-key()}">
                                                            <xsl:for-each-group select="current-group()"  group-by="DESK">
                                                            <xsl:sort data-type="text" select="DESK"/>
                                                            <desk name="{current-grouping-key()}">
                                                                        <xsl:for-each-group select="current-group()"  group-by="PRODUCT">
                                                            <xsl:sort data-type="text" select="PRODUCT"/>
                                                            <product name="{current-grouping-key()}">
                                                            <xsl:for-each-group select="current-group()"  group-by="PERMISSION">
                                                            <xsl:sort data-type="text" select="PERMISSION"/>
                                                            <permission name="{current-grouping-key()}">
                                                            </permission>
                                                            </xsl:for-each-group>
                                                            </product>
                                                            </xsl:for-each-group>
                                                            </desk>
                                                            </xsl:for-each-group>
                                          </division>
                </xsl:for-each-group>
            </location>
           
    <!-- <xsl:for-each select="key('child-by-name', LOCATION)"> -->
                        
           
        </xsl:for-each-group>
    </xsl:template>
</xsl:stylesheet>

0
countrymeister
Asked:
countrymeister
1 Solution
 
Geert BormansCommented:
Here is a solution for XSLT2, using group-by
This should be a number of factors faster,
since it doesn't use the keys so often

The processor will likely group a set of nodes and keep them in memory, so that current-group() is lightning fast as compared to using keys for the same purpose (XPath in a selection versus XPath in the whole  XML file)

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="2.0">
  <xsl:output indent="yes" />
 
    <xsl:template match="Resultsets">
        <apps>
            <xsl:for-each-group select="//Record1" group-by="APP">
                <xsl:sort select="APP"/>
                <app name="{APP}">
                    <xsl:for-each-group select="current-group()" group-by="LOCATION">
                        <xsl:sort select="LOCATION"/>
                        <location name="{LOCATION}">
                            <xsl:for-each-group select="current-group()" group-by="DIVISION">
                                <xsl:sort select="DIVISION"/>
                                <division name="{DIVISION}">
                                    <xsl:for-each-group select="current-group()" group-by="DESK">
                                        <xsl:sort select="DESK"/>
                                        <desk name="{DESK}">
                                            <xsl:for-each-group select="current-group()" group-by="PRODUCT">
                                                <xsl:sort select="PRODUCT"/>
                                                <product name="{PRODUCT}">
                                                    <xsl:for-each select="current-group()">
                                                        <permission><xsl:value-of select="PERMISSION"/></permission>
                                                    </xsl:for-each>
                                                </product>
                                            </xsl:for-each-group>
                                        </desk>
                                    </xsl:for-each-group>
                                </division>
                            </xsl:for-each-group>
                        </location>
                   </xsl:for-each-group>
                 </app>
            </xsl:for-each-group>
        </apps>
        </xsl:template>
</xsl:stylesheet>

I based this solution on the second XML in your previous question
Note that element names in XML are case sensitive
This olution no longer has keys
so it is pretty obvious where you have to change the element names, if cases are different

have fun

Geert
0
 
countrymeisterAuthor Commented:
The group by function zooms thru, much faster than the previous solution i have a .NET pocess that i ran using xslt transform the first solution with keys took abou 8 secs processing, of course I have other functions in my process, but the group by zoomed thru in 2 secs
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now