Convert Many Elements Into One Element

[Subwayboy]

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!

[Martin Honnen]

The key to solving cross-references is the use of the xsl: key element and the XSLT key function, so assuming we have the stylesheet

<xsl:stylesheet  xmlns:xsl="http://www.w3.org/1999/XSL/Transform"  version="1.0">   <xsl:output indent="yes"/>   <xsl:key name="k1" match="Months/*" use="local-name()"/>   <xsl:template match="@* | node()">    <xsl:copy>	  <xsl:apply-templates select="@* | node()"/>    </xsl:copy>  </xsl:template>   <xsl:template match="Root">    <xsl:copy>	  <xsl:apply-templates select="Row/*[starts-with(local-name(), 'Month_')]" mode="row"/>    </xsl:copy>  </xsl:template>   <xsl:template match="Row/*[starts-with(local-name(), 'Month_')]" mode="row">    <Row ID="{../ID}">	  <xsl:apply-templates select="../Name | ../Task"/>	  <Month ID="{local-name()}">	    <xsl:value-of select="key('k1', local-name())"/>	  </Month>    </Row>  </xsl:template> </xsl:stylesheet>	

then the input

<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>

is transformed to

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

Your output description also contained "Effort" elements but otherwise there was no clue as to where to populate them from so I have not tried to include them

[Subwayboy]

Many thanks. The effort value comes from the values between each month value in the original xml. So <Month_1>12</Month_1> would need to become <Effort>12</Effort> I have tried the following, <Effort><xsl:value-of select="../*[key('k1', local-name())]" /> </Effort> But do not get all the effort values pulled through to the correct month

[Martin Honnen]

Well with the code I posted you only need a small change:

<xsl:stylesheet  xmlns:xsl="http://www.w3.org/1999/XSL/Transform"  version="1.0">   <xsl:output indent="yes"/>   <xsl:key name="k1" match="Months/*" use="local-name()"/>   <xsl:template match="@* | node()">    <xsl:copy>	  <xsl:apply-templates select="@* | node()"/>    </xsl:copy>  </xsl:template>   <xsl:template match="Root">    <xsl:copy>	  <xsl:apply-templates select="Row/*[starts-with(local-name(), 'Month_')]" mode="row"/>    </xsl:copy>  </xsl:template>   <xsl:template match="Row/*[starts-with(local-name(), 'Month_')]" mode="row">    <Row ID="{../ID}">	  <xsl:apply-templates select="../Name | ../Task"/>	  <Month ID="{local-name()}">	    <xsl:value-of select="key('k1', local-name())"/>	  </Month>	  <Effort>	    <xsl:value-of select="."/>	  </Effort>    </Row>  </xsl:template> </xsl:stylesheet>	

[Subwayboy]

Fabulous! I have been struggling with apply-templates for many months now, suddenly it is all clear now thanks to your replies