Databases
StackOverflow2013
Postsdbo
POXSL: create a cross tab under Excel with XSLT
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POXSL: create a cross tab under Excel with XSLT
    text
    copied!<p>I have an xml file like this :</p> <pre><code>&lt;products&gt; &lt;cars&gt; &lt;car&gt; &lt;brand&gt;Audi&lt;/brand&gt; &lt;type&gt;S3&lt;/type&gt; &lt;attribs&gt; &lt;attrib&gt; &lt;color&gt;1&lt;/color&gt; &lt;price&gt;45000&lt;/price&gt; &lt;/attrib&gt; &lt;attrib&gt; &lt;color&gt;2&lt;/color&gt; &lt;price&gt;75000&lt;/price&gt; &lt;/attrib&gt; &lt;attrib&gt; &lt;color&gt;4&lt;/color&gt; &lt;price&gt;35000&lt;/price&gt; &lt;/attrib&gt; &lt;/attribs&gt; &lt;/car&gt; &lt;!-- Many cars following --&gt; &lt;/cars&gt; &lt;colors&gt; &lt;color&gt; &lt;id&gt;1&lt;/id&gt; &lt;shortdesc&gt;Blue&lt;/shortdesc&gt; &lt;description&gt;Blue Lagoon&lt;/description&gt; &lt;/color&gt; &lt;color&gt; &lt;id&gt;2&lt;/id&gt; &lt;shortdesc&gt;Red&lt;/shortdesc&gt; &lt;description&gt;Red Sport&lt;/description&gt; &lt;/color&gt; &lt;color&gt; &lt;id&gt;3&lt;/id&gt; &lt;shortdesc&gt;Green&lt;/shortdesc&gt; &lt;description&gt;Green Forest&lt;/description&gt; &lt;/color&gt; &lt;color&gt; &lt;id&gt;4&lt;/id&gt; &lt;shortdesc&gt;Yellow&lt;/shortdesc&gt; &lt;description&gt;Yellow&lt;/description&gt; &lt;/color&gt; &lt;!-- many colors --&gt; &lt;/colors&gt; &lt;/products&gt; </code></pre> <p>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:</p> <pre><code>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 - </code></pre> <p>and the xslt looks like:</p> <pre><code>&lt;ss:Table&gt; &lt;ss:Row&gt; &lt;!-- This is the header row --&gt; &lt;ss:Cell&gt; &lt;ss:Data ss:Type="String"&gt;Brand / Model&lt;/ss:Data&gt; &lt;/ss:Cell&gt; &lt;xsl:for-each select="colors/color"&gt; &lt;ss:Cell&gt; &lt;ss:Data ss:Type="String"&gt; &lt;xsl:value-of select="id"/&gt;/&lt;xsl:value-of select="shortdesc"/&gt; &lt;/ss:Data&gt; &lt;/ss:Cell&gt; &lt;/xsl:for-each&gt; &lt;/ss:Row&gt; &lt;xsl:for-each select="cars/car"&gt; &lt;ss:Row&gt; &lt;ss:Cell&gt; &lt;ss:Data ss:Type="String"&gt; &lt;xsl:value-of select="brand"/&gt; &lt;xsl:value-of select="type"/&gt; &lt;/ss:Data&gt; &lt;/ss:Cell&gt; &lt;xsl:for-each select="attribs/attrib"&gt; &lt;!-- I Know this is incorrect, but what to put in the if ? --&gt; &lt;xsl:if test="color = /colors/color/id"&gt; &lt;ss:Cell&gt; &lt;ss:Data ss:Type="String"&gt; &lt;xsl:value-of select="price"/&gt; &lt;/ss:Data&gt; &lt;/ss:Cell&gt; &lt;/xsl:if&gt; &lt;xsl:if test="color != /colors/color/id"&gt; &lt;ss:Cell&gt; &lt;ss:Data ss:Type="String"&gt;-&lt;/ss:Data&gt; &lt;/ss:Cell&gt; &lt;/xsl:if&gt; &lt;/xsl:for-each&gt; &lt;/ss:Row&gt; &lt;/xsl:for-each&gt; &lt;/ss:Table&gt; </code></pre> <p>I am looking for the way to compare / write the price in the right column.</p> <p>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?</p> <p>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)</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload