Jump to content

Convert Many Elements Into One Element


Recommended Posts

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!

Link to post
Share on other sites

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

Link to post
Share on other sites

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

Link to post
Share on other sites

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>	

  • Like 1
Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...