XSL: create a cross tab under Excel with XSLT

Question

I have an xml file like this :

<products>
    <cars>
        <car>
            <brand>Audi</brand>
            <type>S3</type>
            <attribs>
                <attrib>
                    <color>1</color>
                    <price>45000</price>
                </attrib>
                <attrib>
                    <color>2</color>
                    <price>75000</price>
                </attrib>
                <attrib>
                    <color>4</color>
                    <price>35000</price>
                </attrib>
            </attribs>      
        </car>

        <!-- Many cars following -->

    </cars>
    <colors>
        <color>
            <id>1</id>
            <shortdesc>Blue</shortdesc>
            <description>Blue Lagoon</description>
        </color>
        <color>
            <id>2</id>
            <shortdesc>Red</shortdesc>
            <description>Red Sport</description>
        </color>
        <color>
            <id>3</id>
            <shortdesc>Green</shortdesc>
            <description>Green Forest</description>
        </color>
        <color>
            <id>4</id>
            <shortdesc>Yellow</shortdesc>
            <description>Yellow</description>
        </color>
                <!-- many colors -->
    </colors>
</products>

I am converting this XML in an excel cross-sheet where I have the cars on the vertical and the colors in horizontal like this:

Brand / Model       1/Blue   2/Red   3/Green   4/Yellow

Audi S3             45000    75000   -         35000    
Audi S6             66000    68000   59000     -
Jaguar x-type       98000    -       99500     -

and the xslt looks like:

<ss:Table>
    <ss:Row>
        <!-- This is the header row -->
        <ss:Cell>
            <ss:Data ss:Type="String">Brand / Model</ss:Data>
        </ss:Cell>
        <xsl:for-each select="colors/color">
            <ss:Cell>
                <ss:Data ss:Type="String">
                    <xsl:value-of select="id"/>/<xsl:value-of select="shortdesc"/>
                </ss:Data>
            </ss:Cell>
        </xsl:for-each>
    </ss:Row>
    <xsl:for-each select="cars/car">
        <ss:Row>
            <ss:Cell>
                <ss:Data ss:Type="String">
                    <xsl:value-of select="brand"/> <xsl:value-of select="type"/>
                </ss:Data>
            </ss:Cell>
            <xsl:for-each select="attribs/attrib">
                <!-- I Know this is incorrect, but what to put in the if ? -->
                <xsl:if test="color = /colors/color/id">
                    <ss:Cell>
                        <ss:Data ss:Type="String">
                            <xsl:value-of select="price"/>
                        </ss:Data>
                    </ss:Cell>
                </xsl:if>
                <xsl:if test="color != /colors/color/id">
                    <ss:Cell>
                        <ss:Data ss:Type="String">-</ss:Data>
                    </ss:Cell>
                </xsl:if>
            </xsl:for-each>
        </ss:Row>
    </xsl:for-each>
</ss:Table>

I am looking for the way to compare / write the price in the right column.

The template is referencing the node /products in order to be able to access both cars and colors. A possible way is to create an array in the same time I write the colors in the header and to compare with it when I process the cars; Is this a possible way or something better is possible?

Another detail: I cannot change the XML as it is already designed like this (in fact it is not cars I am processing, but just to make it simpler)

_The element type "type" must be terminated by the matching end-tag ""._ Take Saxon XSLT processor word for it! — Emiliano Poggi, Aug 05 '11 at 19:50
_The end-tag for element type "color" must end with a '>' delimiter._ Take Saxon XSLT processor word for it! — Emiliano Poggi, Aug 05 '11 at 19:50
Final note, you could collapse the main two `xsl:for-each` into two separate templates. — Emiliano Poggi, Aug 05 '11 at 20:50
Thanks for pointing to the errors, I correct them in the question. My source XML is correct, I made mistake while typing the example in the question. — рüффп, Aug 07 '11 at 17:01

Emiliano Poggi · Accepted Answer · 2011-08-08T17:05:32.180

You can use a composite key to collect the cars by brand, type and color:

<xsl:output indent="yes"/>
<xsl:key name="k_cars" 
    match="/products/cars/car/attribs/attrib" 
    use="concat(../../brand,../../type,color)"/>

Then iterate on cars/car and, because you need to check the price for all colors (some car may miss a color), sub-iterate on colors/color and use xsl:choose testing on the key. If the key returns a node, then print corresponding price; otherwise print -:

<xsl:variable name="car" select="."/>

<xsl:for-each select="/products/colors/*">

    <xsl:variable name="v_CarColor" 
        select="key('k_cars',concat($car/brand,$car/type,id))"/>

    <xsl:choose>

        <xsl:when test="$v_CarColor">
            <ss:Cell>
                <ss:Data ss:Type="String">
                    <xsl:value-of select="$v_CarColor/price"/>
                </ss:Data>
            </ss:Cell>
        </xsl:when>

        <xsl:otherwise>
            <ss:Cell>
                <ss:Data ss:Type="String">-</ss:Data>
            </ss:Cell>
        </xsl:otherwise>

    </xsl:choose>
</xsl:for-each>

Your final transform:

<xsl:stylesheet version="1.0"
    xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    xmlns:ss="excel.xml">

    <xsl:output indent="yes"/>
    <xsl:key name="k_cars" 
        match="/products/cars/car/attribs/attrib" 
        use="concat(../../brand,../../type,color)"/>

    <xsl:template match="/products">
        <ss:Table>
            <ss:Row>
                <!-- This is the header row -->
                <ss:Cell>
                    <ss:Data ss:Type="String">Brand / Model</ss:Data>
                </ss:Cell>
                <xsl:for-each select="colors/color">
                    <ss:Cell>
                        <ss:Data ss:Type="String">
                            <xsl:value-of select="id"/>/<xsl:value-of select="shortdesc"/>
                        </ss:Data>
                    </ss:Cell>
                </xsl:for-each>
            </ss:Row>

            <xsl:for-each select="cars/car">

                <ss:Row>
                    <ss:Cell>
                        <ss:Data ss:Type="String">
                            <xsl:value-of select="brand"/> <xsl:value-of select="type"/>
                        </ss:Data>
                    </ss:Cell>


                    <xsl:variable name="car" select="."/>

                    <xsl:for-each select="/products/colors/*">

                        <xsl:variable name="v_CarColor" 
                            select="key('k_cars',concat($car/brand,$car/type,id))"/>

                        <xsl:choose>

                            <xsl:when test="$v_CarColor">
                                <ss:Cell>
                                    <ss:Data ss:Type="String">
                                        <xsl:value-of select="$v_CarColor/price"/>
                                    </ss:Data>
                                </ss:Cell>
                            </xsl:when>

                            <xsl:otherwise>
                                <ss:Cell>
                                    <ss:Data ss:Type="String">-</ss:Data>
                                </ss:Cell>
                            </xsl:otherwise>

                        </xsl:choose>
                    </xsl:for-each>
                </ss:Row>
            </xsl:for-each>
        </ss:Table> 
    </xsl:template>

</xsl:stylesheet>

My xsl is a bit more complicated but with your clear example, you point me to the right direction. That xsl:key is very powerful! Thanks a lot. — рüффп, Aug 08 '11 at 12:55

XSL: create a cross tab under Excel with XSLT

1 Answers1