I have Source XML in this format. The relevant part of the source is under the values tag, nested about four levels down, and represents one or more tables of data where the elements are of the format: name = TableName-Row#.FieldName and value = fieldValue.
<root>
<someData>1</someData>
<otherData>free</otherData>
<parent1>
<parent2>
<parent3>
<values>
<data>
<name>ComputerInfo-1.CPU</name>
<value>4</value>
</data>
<data>
<name>ComputerInfo-1.Memory</name>
<value>32</value>
</data>
<data>
<name>ComputerInfo-1.Storage</name>
<value>1024</value>
</data>
<data>
<name>ComputerInfo-2.CPU</name>
<value>2</value>
</data>
<data>
<name>ComputerInfo-2.Memory</name>
<value>64</value>
</data>
<data>
<name>ComputerInfo-2.Storage</name>
<value>2048</value>
</data>
<data>
<name>ComputerInfo-3.CPU</name>
<value>4</value>
</data>
<data>
<name>ComputerInfo-3.Memory</name>
<value>16</value>
</data>
<data>
<name>ComputerInfo-3.Storage</name>
<value>512</value>
</data>
<data>
<name>UserInfo-1.firstName</name>
<value>Mary</value>
</data>
<data>
<name>UserInfo-1.lastName</name>
<value>Jones</value>
</data>
<data>
<name>UserInfo-1.login</name>
<value>mjones</value>
</data>
<data>
<name>UserInfo-2.firstName</name>
<value>Doctor</value>
</data>
<data>
<name>UserInfo-2.lastName</name>
<value>Who</value>
</data>
<data>
<name>UserInfo-2.login</name>
<value>dwho</value>
</data>
<data>
<name>UserInfo-3.firstName</name>
<value>John</value>
</data>
<data>
<name>UserInfo-3.lastName</name>
<value>Mellencamp</value>
</data>
<data>
<name>UserInfo-3.login</name>
<value>cougar69</value>
</data>
</values>
</parent3>
</parent2>
</parent1>
</root>
The desired result is a CSV file with the header row on top, followed by the corresponding rows:
CPU,Memory,Storage
4,32,1024
2,64,2048
4,16,512
I also wanted to use a variable to hold the particular table I wish to process, e.g.,
<xsl:variable name="table" select="/root/tableName"/>
This is because I have the ability via the application to include the tableName in the source XML if I choose to. However, xsl:key doesn't allow me to use variables in my match and I'm also forced to use XSLT 1.0.
My issue is I can do this in steps, but I really need to do this in a single transform.
Here's what I have so far.
I can pass the Source XML to this transform:
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="xml" indent="yes" omit-xml-declaration="yes"/>
<xsl:template match="/root/parent1/parent2/parent3/values/data">
<xsl:element name="{name}">
<xsl:value-of select="value" />
</xsl:element>
</xsl:template>
<xsl:template match="@* | node()">
<xsl:copy>
<xsl:apply-templates select="@*|node()"/>
</xsl:copy>
</xsl:template>
<xsl:template match="/">
<values>
<xsl:apply-templates select="//values/node()"/>
</values>
</xsl:template>
</xsl:stylesheet>
and it will produce this result:
<values>
<ComputerInfo-1.CPU>4</ComputerInfo-1.CPU>
<ComputerInfo-1.Memory>32</ComputerInfo-1.Memory>
<ComputerInfo-1.Storage>1024</ComputerInfo-1.Storage>
<ComputerInfo-2.CPU>2</ComputerInfo-2.CPU>
<ComputerInfo-2.Memory>64</ComputerInfo-2.Memory>
<ComputerInfo-2.Storage>2048</ComputerInfo-2.Storage>
<ComputerInfo-3.CPU>4</ComputerInfo-3.CPU>
<ComputerInfo-3.Memory>16</ComputerInfo-3.Memory>
<ComputerInfo-3.Storage>512</ComputerInfo-3.Storage>
<UserInfo-1.firstName>Mary</UserInfo-1.firstName>
<UserInfo-1.lastName>Jones</UserInfo-1.lastName>
<UserInfo-1.login>mjones</UserInfo-1.login>
<UserInfo-2.firstName>Doctor</UserInfo-2.firstName>
<UserInfo-2.lastName>Who</UserInfo-2.lastName>
<UserInfo-2.login>dwho</UserInfo-2.login>
<UserInfo-3.firstName>John</UserInfo-3.firstName>
<UserInfo-3.lastName>Mellencamp</UserInfo-3.lastName>
<UserInfo-3.login>cougar69</UserInfo-3.login>
</values>
I can then take this result and apply the following transform:
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="xml" indent="yes" omit-xml-declaration="yes"/>
<xsl:key name="elementByRow" match="/*/*[contains(name(), 'ComputerInfo')]" use="substring-before(name(), '.')"/>
<xsl:template match="/*">
<values>
<xsl:apply-templates select="*[generate-id() = generate-id(key('elementByRow', substring-before(name(), '.'))[1])]" />
</values>
</xsl:template>
<xsl:template match="*">
<Row>
<xsl:for-each select="key('elementByRow', substring-before(name(), '.'))">
<xsl:element name="{substring-after(name(), '.')}">
<xsl:value-of select="." />
</xsl:element>
</xsl:for-each>
</Row>
</xsl:template>
</xsl:stylesheet>
which will produce this result:
<values>
<Row>
<CPU>4</CPU>
<Memory>32</Memory>
<Storage>1024</Storage>
</Row>
<Row>
<CPU>2</CPU>
<Memory>64</Memory>
<Storage>2048</Storage>
</Row>
<Row>
<CPU>4</CPU>
<Memory>16</Memory>
<Storage>512</Storage>
</Row>
</values>
I specifically aimed to get it to this previous format, because I found another posting here ( convert xml document to comma delimited (CSV) file using xslt stylesheet ), which allows me to take this result and apply the transform from the aforementioned post to get the desired result. Here is the transform from that post for reference:
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:key name="field" match="/*/*/*" use="name()" />
<xsl:output method="text"/>
<xsl:template match="/">
<xsl:for-each select="*/*/*[generate-id() = generate-id(key('field',name())[1])]">
<xsl:value-of select="name()" />
<xsl:if test="position() != last()">,</xsl:if>
</xsl:for-each>
<xsl:text> </xsl:text>
<xsl:apply-templates select="*/*" mode="row"/>
</xsl:template>
<xsl:template match="*" mode="row">
<xsl:variable name="row" select="*" />
<xsl:for-each select="/*/*/*[generate-id() = generate-id(key('field',name())[1])]">
<xsl:variable name="name" select="name()" />
<xsl:apply-templates select="$row[name()=$name]" mode="data" />
<xsl:if test="position() != last()">,</xsl:if>
</xsl:for-each>
<xsl:text> </xsl:text>
</xsl:template>
<xsl:template match="*" mode="data">
<xsl:choose>
<xsl:when test="contains(text(),',')">
<xsl:text>"</xsl:text>
<xsl:call-template name="doublequotes">
<xsl:with-param name="text" select="text()" />
</xsl:call-template>
<xsl:text>"</xsl:text>
</xsl:when>
<xsl:otherwise>
<xsl:value-of select="." />
</xsl:otherwise>
</xsl:choose>
<xsl:if test="position() != last()">,</xsl:if>
</xsl:template>
<xsl:template name="doublequotes">
<xsl:param name="text" />
<xsl:choose>
<xsl:when test="contains($text,'"')">
<xsl:value-of select="concat(substring-before($text,'"'),'""')" />
<xsl:call-template name="doublequotes">
<xsl:with-param name="text" select="substring-after($text,'"')" />
</xsl:call-template>
</xsl:when>
<xsl:otherwise>
<xsl:value-of select="$text" />
</xsl:otherwise>
</xsl:choose>
</xsl:template>
</xsl:stylesheet>
