XSLT for converting column based xml to row based one

sayusekhar · March 8th, 2011, 04:59 AM

Hi,
I have an input xml file in the format given below:

Code:

<Column ID = "5">
<RefData>
<Data ID="1"> data for id 1</Data>
<Data ID="2"> data for id 2</Data>
<Data ID="3"> data for id 3</Data>
<Data ID="4"> data for id 4</Data>
</RefData>
</Column>

<Column ID="10">
<Measurements>

<Measurement>
<Label> 1 </Label>
<Data2>data2 for label 1 </Data2>
</Measurement>

<Measurement>
<Label> 2 </Label>
<Data2>data2 for label 2 </Data2>
</Measurement>

<Measurement>
<Label> 3 </Label>
<Data2>data2 for label 3 </Data2>
</Measurement>

<Measurement>
<Label> 4 </Label>
<Data2>data2 for label 4 </Data2>
</Measurement>

</Column>

Now, i need to translate this xml file to the following file:

Code:

<Measurementset>

<measurement>
<ID>1</ID>
<Data1>data for id 1</Data1>
<Data2>data2 for label 1</Data2>
</measurement>
<measurement>
<ID>2</ID>
<Data1>data for id 2</Data1>
<Data2>data2 for label 2</Data2>
</measurement>

<measurement>
<ID>3</ID>
<Data1>data for id 3</Data1>
<Data2>data2 for label 3</Data2>
</measurement>

<measurement>
<ID>1</ID>
<Data1>data for id 4</Data1>
<Data2>data2 for label 4</Data2>
</measurement>
</Measurementset>

I am not sure if I can perform the translation using xslt. Please let me know if this is possible using xslt.

samjudson · March 8th, 2011, 05:54 AM

Assuming that for the n'th RefData/Data item you simply want the same value from the n'th Data2 element in the second column then you could use something like this:

Code:

  <xsl:template match="/">   
      <Measurementset>
		       <xsl:apply-templates select="//Column[@ID='5']"/>
	     </Measurementset>
   </xsl:template>
 
   <xsl:variable name="m" select="//Column[@ID='10']/Measurements/Measurement"/>
 
  <xsl:template match="RefData/Data">
      <measurement>
		       <xsl:variable name="pos" select="position()"/>
		       <ID>
            <xsl:value-of select="@ID"/>
         </ID>
		       <Data1>
            <xsl:value-of select="text()"/>
         </Data1>
		       <Data2>
            <xsl:value-of select="$m[$pos]/Data2"/>
         </Data2>
	     </measurement>
  </xsl:template>

sayusekhar · March 8th, 2011, 08:06 AM

Thanks for the reply.
Actually the ID from Refdata/Data has to be matched with the Label from Measurements/Measurement.

samjudson · March 8th, 2011, 08:12 AM

In that case define a Key as so:

Code:

<xsl:key name="mKey" match="Measurement" use="normalize-space(Label)"/>

And then get the Data2 element like this:

Code:

<Data2><xsl:value-of select="key('mKey', @ID)/Data2"/></Data2>

You can get rid of the "pos" and "m" variables, as they are no longer needed.

Martin Honnen · March 8th, 2011, 08:20 AM

Assuming the input document is the following well-formed snippet

Code:

<Root>
<Column ID = "5">
<RefData>
<Data ID="1"> data for id 1</Data>
<Data ID="2"> data for id 2</Data>
<Data ID="3"> data for id 3</Data>
<Data ID="4"> data for id 4</Data>
</RefData>
</Column>


<Measurements>

<Measurement>
<Label> 1 </Label>
<Data2>data2 for label 1 </Data2>
</Measurement>

<Measurement>
<Label> 2 </Label>
<Data2>data2 for label 2 </Data2>
</Measurement>

<Measurement>
<Label> 3 </Label>
<Data2>data2 for label 3 </Data2>
</Measurement>

<Measurement>
<Label> 4 </Label>
<Data2>data2 for label 4 </Data2>
</Measurement>


</Measurements>

</Root>

then the stylesheet

Code:

<xsl:stylesheet
  xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
  version="1.0">
  
  <xsl:key name="k1" match="RefData/Data" use="@ID"/>
  
  <xsl:output indent="yes"/>
  <xsl:strip-space elements="*"/>
  
  <xsl:template match="@* | node()">
    <xsl:copy>
      <xsl:apply-templates select="@* | node()"/>
    </xsl:copy>
  </xsl:template>
  
  <xsl:template match="/">
    <xsl:apply-templates select="/Root/Measurements"/>
  </xsl:template>
  
  <xsl:template match="Measurement/Label">
    <xsl:apply-templates select="key('k1', normalize-space())"/>
  </xsl:template>
 
  <xsl:template match="RefData/Data">
    <ID><xsl:value-of select="@ID"/></ID>
    <Data1><xsl:value-of select="."/></Data1>
  </xsl:template>
  
</xsl:stylesheet>

creates the result:

Code:

<Measurements>
   <Measurement>
      <ID>1</ID>
      <Data1> data for id 1</Data1>
      <Data2>data2 for label 1 </Data2>
   </Measurement>
   <Measurement>
      <ID>2</ID>
      <Data1> data for id 2</Data1>
      <Data2>data2 for label 2 </Data2>
   </Measurement>
   <Measurement>
      <ID>3</ID>
      <Data1> data for id 3</Data1>
      <Data2>data2 for label 3 </Data2>
   </Measurement>
   <Measurement>
      <ID>4</ID>
      <Data1> data for id 4</Data1>
      <Data2>data2 for label 4 </Data2>
   </Measurement>
</Measurements>