I have a problem with processing following xml code:
<?xml version="1.0" encoding="UTF-8"?>
<searchresult>
<head>
<heading>
<title>Column1</title>
<dataType>TEXT</dataType>
</heading>
<heading>
<title>Column2</title>
<dataType>DATE</dataType>
<dataFormat>SHORT_DATE</dataFormat>
</heading>
</head>
<data>
<row>
<column>
<value>Hello</value>
<column>
<column>
<value>2012-07-12</value>
<column>
</row>
<row>
<column>
<value>Good bye</value>
<column>
<column>
<value>2012-07-13</value>
<column>
</row>
</data>
</searchresult>
I need to transform this xml to EXCEL compatible file (I use urn:schemas-microsoft-com:office:office, urn:schemas-microsoft-com:office:excel and urn:schemas-microsoft-com:office:spreadsheet namespaces for it)
Problem is that I don't know how to apply information from head/heading elements dataType + dataFormat (if available) on row/column/value. This will help Excel to recognize which datatype is inside its cells. It is obvious that i need to preserve order so. Number of columns and its metadata is dynamic and each XML may differ.
I need to get something like this:
<?xml version="1.0" encoding="ISO-8859-1"?>
<Workbook --several namespaces here-->
<Worksheet ss:Name="SearchResult">
<Table x:FullRows="1" x:FullColumns="1">
<Row ss:Height="12.75">
<Cell>
<Data ss:Type="String">Column1</Data>
</Cell>
<Cell>
<Data ss:Type="String">Column2</Data>
</Cell>
</Row>
<Row ss:Height="12.75">
<Cell>
<Data ss:Type="String">Hello : TEXT</Data>
</Cell>
<Cell>
<Data ss:Type="Date">2012-07-12 : DATE - SHORT_DATE</Data>
</Cell>
</Row>
<Row ss:Height="12.75">
<Cell>
<Data ss:Type="String">Good bye : TEXT</Data>
</Cell>
<Cell>
<Data ss:Type="Date">2012-07-12 : DATE - SHORT_DATE</Data>
</Cell>
</Row>
</Table>
</Worksheet>
</Workbook>
I tried a few times create something useful and working but all my attempts fail. Current version is here:
<xsl:template match="searchresult">
<Worksheet>
--some unimportant script--
<Table x:FullColumns="1" x:FullRows="1">
<xsl:apply-templates select="head" />
<xsl:apply-templates select="elements/row"/>
</Table>
</Worksheet>
</xsl:template>
<xsl:template match="head">
<Row>
<xsl:for-each select="*">
<!-- resolve data-type and remember it as variable -->
<xsl:variable name="concat('dataType', position())" select="dataType">
<xsl:choose>
<xsl:when test="TEXT">
<xsl:value-of select=".">String</xsl:value-of>
</xsl:when>
<xsl:when test="DATE">
<xsl:value-of select=".">DateTime</xsl:value-of>
</xsl:when>
</xsl:choose>
</xsl:variable>
<xsl:variable name="concat('dataFormat', position())" select="dataFormatter" >
<!-- create style IDs for different formats -->
</xsl:variable>
<Cell>
<Data ss:Type="String">
<xsl:value-of select="title/." />
</Data>
</Cell>
</xsl:for-each>
</Row>
</xsl:template>
<xsl:template match="elements/row/column">
<xsl:for-each select="values">
<Cell>
<!-- resolve order within loop and pick correct data-type variable -->
<xsl:variable name="type" select="concat('$dataType', position())" />
<xsl:variable name="format" select="concat('$dataFormat', position())" />
<Data ss:Type="$type">
<xsl:value-of select="concat(normalize-space(.),' : ', $type)"/>
<!-- check if data format is set -->
<xsl:if test="//TODO">
<xsl:value-of select="concat(' - ', $format)" />
</xsl:if>
</Data>
</Cell>
</xsl:for-each>
</xsl:template>
</xsl:stylesheet>
This version is useless because I cannot use as name of variable any variable value, it must be constant value. Parsing whole data works somehow, but when I tried to implement datatype and data format it broke.
Edit: The information about datatype and dataformat is placed in head element which holds all information about columns and their headings. Columns are processed in separate template and they are not directly connected to column definitions from head element. Relation is maintained just only via elements' order. I need to process datatype and possible dataformat (which is optional) information for each row and each cell (for proper column), not only for headings.
You could use a key to look up the heading elements by their position
<xsl:key name="headings" match="heading" use="count(preceding-sibling::heading)" />
Then, assuming you were positioned on a column element, you would get the assoicated data type, based on the position, like so
<xsl:variable
name="dataType"
select="key('headings', count(preceding-sibling::column))/dataType" />
i.e For the first column element in a row, you would look up the first heading element, and get the data type.
Some other things do note about your XSLT. Firstly, dynamic variables names, like the following are not allowed
<xsl:variable name="concat('dataType', position())" select="dataType">
Nor are you allowed to have non-empty content in a variable should you use the select attribute.
Secondly, if you want to use variable values in your output attributes, you need to use Attribute Value Templates. Instead of doing this...
<Data ss:Type="$type">
You woud do this
<Data ss:Type="{$type}">
Also, you should favour xsl:apply-templates over xsl:for-each, as they encourage code re-use, cut-down on nested code, and are more within the spirit of XSLT.
Anyway, here is the full XSLT (note using made-up namespaces)
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:x="x" xmlns:ss="ss">
<xsl:output method="xml" indent="yes"/>
<xsl:key name="headings" match="heading" use="count(preceding-sibling::heading)"/>
<xsl:template match="searchresult">
<Worksheet>
<Table x:FullColumns="1" x:FullRows="1">
<xsl:apply-templates select="head"/>
<xsl:apply-templates select="data/row"/>
</Table></Worksheet>
</xsl:template>
<xsl:template match="head">
<Row>
<xsl:apply-templates select="heading"/>
</Row>
</xsl:template>
<xsl:template match="heading">
<Cell>
<Data ss:Type="String">
<xsl:value-of select="title"/>
</Data>
</Cell>
</xsl:template>
<xsl:template match="row">
<row>
<xsl:apply-templates select="column"/>
</row>
</xsl:template>
<xsl:template match="column">
<Cell>
<xsl:variable name="dataType" select="key('headings', count(preceding-sibling::column))/dataType"/>
<xsl:variable name="type">
<xsl:choose>
<xsl:when test="$dataType = 'TEXT'">
<xsl:text>String</xsl:text>
</xsl:when>
<xsl:when test="$dataType = 'DATE'">
<xsl:text>Date</xsl:text>
</xsl:when>
</xsl:choose>
</xsl:variable>
<xsl:variable name="dataFormat" select="key('headings', count(preceding-sibling::column))/dataFormat"/>
<Data ss:Type="{$type}">
<xsl:value-of select="concat(normalize-space(.),' : ', $type)"/>
<xsl:if test="$dataFormat">
<xsl:value-of select="concat(' - ', $dataFormat)"/>
</xsl:if>
</Data>
</Cell>
</xsl:template>
</xsl:stylesheet>
When applied to your sample XML, the following is output
<Worksheet xmlns:x="x" xmlns:ss="ss">
<Table x:FullColumns="1" x:FullRows="1">
<Row>
<Cell>
<Data ss:Type="String">Column1</Data>
</Cell>
<Cell>
<Data ss:Type="String">Column2</Data>
</Cell>
</Row>
<row>
<Cell>
<Data ss:Type="String">Hello : String</Data>
</Cell>
<Cell>
<Data ss:Type="Date">2012-07-12 : Date - SHORT_DATE</Data>
</Cell>
</row>
<row>
<Cell>
<Data ss:Type="String">Good bye : String</Data>
</Cell>
<Cell>
<Data ss:Type="Date">2012-07-13 : Date - SHORT_DATE</Data>
</Cell>
</row>
</Table>
</Worksheet>