Nested Loops Using XSLT and SQL

Question

I have a web page, which I did not write, but have to modify (using the asp dot net store front product if you are not familiar with that it wont really matter), that uses XLST. It is getting data from SQL queries. I have the following scenario:

I have products that fit cars. We have pages that list car models by make, each make has a year(although it is not used here). The page was built to list each model for the make selected. Now we have decided to add groupings of the models, so each model has both a make and a model_group. There are only two tables involved, one holds make, model, year(not displayed on page in this case) and model_group id. The model_grouping table holds only the model_group name and id.

The SQL looks like this, the @make is the currently selected make:

SELECT DISTINCT CMMY.Make,mg.model_group, CMMY.Model
FROM Car_MakeModelYear CMMY WITH (NOLOCK) LEFT JOIN model_grouping mg ON 
CMMY.model_grouping_id = mg.model_grouping_id
WHERE Make = @make
Order By Make, Model

The XSLT looks like below. What I ended up doing is the crude hack where I compare the first seven charachters of the model name and if they have changed I show the next model_group. But what I would really like is to loop through each model for model_group1 then move to model_group2 and so on, nested loops.

No matter how I have tried this I get either, the inner loop never executes, or it prints out all models regardless of sub group.

I've seen examples of this when reading the data from an XML file, but none of them seem to fit this scenario. Do I need to re-write the SQL somehow to make it work?

<xsl:for-each select="/root  /Auto/MMY[translate(Make,$lcletters,$ucletters)=$CurrentMake and not(Model=preceding::Model)]">
    <div class="listModels">
         <xsl:if test="substring(Model,1,7) != substring(preceding::Model[1],1,7)">
            <h2><br/><xsl:value-of select="model_group" /><br/></h2>
        </xsl:if>
        <h3>
        <a>
        <xsl:attribute name="href">
         <xsl:value-of select="translate(customSiteFunction:StrToUpper...(this is text formamting for url).aspx
        </xsl:attribute>
        <xsl:value-of select="Model" /> 
        </a>
        </h3>
        </div>

Do you have an example of the actual XML that the XSLT is being applied to? Thanks! — Tim C, Apr 03 '14 at 20:16

score 0 · Answer 1 · edited May 23 '17 at 10:26

I believe you want to use XSL:Key (on Make) and the Muenchian method to loop through your keys.

Here is an overview of the Muenchian method: http://www.jenitennison.com/xslt/grouping/muenchian.html

And here is a more practical use case: XSLT grouping on multiple keys using Muenchian method

If you provide the XML that the XSLT is being applied to, I can help you with more specific code examples.

Nested Loops Using XSLT and SQL

1 Answers1