Convert Many Elements Into One Element

[graham...@gmail.com]

Hi all,

I have been assigned a task to convert an xml output from an excel workbook into another xml structure. There are several elements in the xml file that need to be converted into one element. I do not have any flexibility in changing the structure of the output from the excel workbook

XML File structure



<Root>
<Row>
<ID>1</ID>
<Name>John</Name>
<Task>Task One</Task>
<Month_1>12</Month_1>
<Month_2>23</Month_2>
<Month_3>45</Month_3>
</Row>
<Row>
<ID>1</ID>
<Name>John</Name>
<Task>Task Two</Task>
<Month_2>7.5</Month_2>
<Month_3>4</Month_3>
</Row>
<Months>
<Month_1>2011-11</Month_1>
<Month_2>2011-12</Month_2>
<Month_3>2012-01</Month_3>
</Months>
</Root>




The file structure of the converted file after applying an xslt should be in the format of

<Root>
<Row ID="1">
<Name>John</Name>
<Task>Task One</Task>
<Month ID="Month_1">2011-11</Month>
<Effort>12</Effort>
</Row>
<Row ID="1">
<Name>John</Name>
<Task>Task One</Task>
<Month ID="Month_2">2011-12</Month>
<Effort>23</Effort>
</Row>
<Row ID="1">
<Name>John</Name>
<Task>Task One</Task>
<Month ID="Month_3">2012-01</Month>
<Effort>45</Effort>
</Row>
<Row ID="1">
<Name>John</Name>
<Task>Task Two</Task>
<Month ID="Month_2">2011-12</Month>
<Effort>7.5</Effort>
</Row>
<Row ID="1">
<Name>John</Name>
<Task>Task Two</Task>
<Month ID="Month_3">2012-01</Month>
<Effort>4</Effort>
</Row>
</Root>





The <Month> values in <Months> in the initial xml file could actually run from Month_1 thru to Month_18. I can get the ID, Name and Task elements repeating, but i am struggling with the xslt for the Month and Effort values.



<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="xml" indent="yes" />
<xsl:template match="/">
<xsl:element name="Root">
<xsl:for-each select="//Row">
<xsl:element name="Row">
<xsl:attribute name="ID">
<xsl:value-of select="ID" />
</xsl:attribute>
<xsl:element name="Name">
<xsl:value-of select="Name" />
</xsl:element>
<xsl:element name="Task">
<xsl:value-of select="Task" />
</xsl:element>
<!-- NEED SOMETHING HERE TO GET THE NAME OF THE NEXT FIELD AND LOOK UP THE VALUES FROM
THE MONTHS NODESET -->
<xsl:for-each select="/Root/Months">

</xsl:for-each>

</xsl:element>
</xsl:for-each>
</xsl:element>
</xsl:template>
</xsl:stylesheet>


I am sure (I think) i need to use local-name() to look up the name of each element in the <Months> nodeset and check if there is an element with that name in each of the <Row> elements and then pull the value of the month into the month element in the new structure.......this is where i am struggling....


Many thanks all!

[Marc]

look at something like *[start-width(name(),'Month')] or
*[substring-before(name(),'_')='Month']

Marc