Matrix extraction from xml using xslt

[lokesh.r.kandula]

Hi Buddy,

I have a requirement which is explained as below:
1. My input xml file is as shown:

<input>
    <historicalObservation>
        <observationDate>20110111</observationDate>
        <observedRate>14.74</observedRate>
        <observationWeight>1</observationWeight>
        <observationType>fixing</observationType>
    </historicalObservation>
    <historicalObservation>
        <observationDate>20110111</observationDate>
        <observedRate>10.28</observedRate>
        <observationWeight>2</observationWeight>
        <observationType>fixing</observationType>
    </historicalObservation>
    <historicalObservation>
        <observationDate>20110111</observationDate>
        <observedRate>27.96</observedRate>
        <observationWeight>3</observationWeight>
        <observationType>fixing</observationType>
    </historicalObservation>
    <historicalObservation>
        <observationDate>20110111</observationDate>
        <observedRate>4.94</observedRate>
        <observationWeight>4</observationWeight>
        <observationType>fixing</observationType>
    </historicalObservation>
    <historicalObservation>
        <observationDate>20110225</observationDate>
        <observedRate>14.18</observedRate>
        <observationWeight>1</observationWeight>
        <observationType>fixing</observationType>
    </historicalObservation>
    <historicalObservation>
        <observationDate>20110225</observationDate>
        <observedRate>9.21</observedRate>
        <observationWeight>2</observationWeight>
        <observationType>fixing</observationType>
    </historicalObservation>
    <historicalObservation>
        <observationDate>20110225</observationDate>
        <observedRate>29.68</observedRate>
        <observationWeight>3</observationWeight>
        <observationType>fixing</observationType>
    </historicalObservation>
    <historicalObservation>
        <observationDate>20110225</observationDate>
        <observedRate>4.68</observedRate>
        <observationWeight>4</observationWeight>
        <observationType>fixing</observationType>
    </historicalObservation>
</input>

1. Now, i want to extract the data which should look like this:

    Date            Rate1      Rate2       Rate3       Rate4
   20110111        14.74       10.28       27.96       4.94
   20110225        14.18       9.21        29.68       4.68
   

the logic is something like this:
"For each 'observationDate' value, pick the 'observedRate' value and populate under Rate1 or Rate2 or Rate3 or Rate4. Again the exact 'Rate' column is determined by the 'observationWeight' value."

the above logic should be applied to all the dates in the input xml file and the output should be displayed as matrix as shown above.

If not the matrix, can anybody tell atleast the logic to retrieve the values.

Thanks a lot in advance...Will be waiting for your response....

[Mike Askew]

Two questions.

1. Will the dates always be so neatly ordered, ie. all four entries for the same date together?

2. Will there always be the four rate entries or can it vary?

[lokesh.r.kandula]

Dates will be neatly ordered with all the four entries together and only four rate entries at all times.

[Mike Askew]

Ok I shall have a look at it, sorry for the delay in answering also, don't watch the forum as much when not in the office!

[Mike Askew]

Was a pain to do at first but learnt something new from it. The following is a tad messy but works.

It allows the weighting to be in any order within the block of four nodes and it will sort it out.

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <xsl:output omit-xml-declaration="yes" indent="yes" method ="text"/>
  <xsl:strip-space elements="*"/>

  <xsl:key name="keyByID" match="historicalObservation" use="observationDate"/>

  <xsl:template match="/">
    <xsl:text>Date&#9;&#9;Rate1&#9;Rate2&#9;Rate3&#9;Rate4&#0010;</xsl:text>
    <xsl:apply-templates/>
  </xsl:template>

  <xsl:template match="input">
    <xsl:for-each select="historicalObservation[generate-id()=generate-id(key('keyByID', observationDate)[1])]">

      <xsl:variable name="RateOne">
        <xsl:if test ="./observationWeight = 1">
          <xsl:value-of select="./observedRate"/>
        </xsl:if>
        <xsl:if test ="following-sibling::historicalObservation[1]/observationWeight = 1">
          <xsl:value-of select="following-sibling::historicalObservation[1]/observedRate"/>
        </xsl:if>
        <xsl:if test ="following-sibling::historicalObservation[2]/observationWeight = 1">
          <xsl:value-of select="following-sibling::historicalObservation[2]/observedRate"/>
        </xsl:if>
        <xsl:if test ="following-sibling::historicalObservation[3]/observationWeight = 1">
          <xsl:value-of select="following-sibling::historicalObservation[3]/observedRate"/>
        </xsl:if>
      </xsl:variable>
      <xsl:variable name="RateTwo">
        <xsl:if test ="./observationWeight = 2">
          <xsl:value-of select="./observedRate"/>
        </xsl:if>
        <xsl:if test ="following-sibling::historicalObservation[1]/observationWeight = 2">
          <xsl:value-of select="following-sibling::historicalObservation[1]/observedRate"/>
        </xsl:if>
        <xsl:if test ="following-sibling::historicalObservation[2]/observationWeight = 2">
          <xsl:value-of select="following-sibling::historicalObservation[2]/observedRate"/>
        </xsl:if>
        <xsl:if test ="following-sibling::historicalObservation[3]/observationWeight = 2">
          <xsl:value-of select="following-sibling::historicalObservation[3]/observedRate"/>
        </xsl:if>
      </xsl:variable>
      <xsl:variable name="RateThree">
        <xsl:if test ="./observationWeight = 3">
          <xsl:value-of select="./observedRate"/>
        </xsl:if>
        <xsl:if test ="following-sibling::historicalObservation[1]/observationWeight = 3">
          <xsl:value-of select="following-sibling::historicalObservation[1]/observedRate"/>
        </xsl:if>
        <xsl:if test ="following-sibling::historicalObservation[2]/observationWeight = 3">
          <xsl:value-of select="following-sibling::historicalObservation[2]/observedRate"/>
        </xsl:if>
        <xsl:if test ="following-sibling::historicalObservation[3]/observationWeight = 3">
          <xsl:value-of select="following-sibling::historicalObservation[3]/observedRate"/>
        </xsl:if>
      </xsl:variable>
      <xsl:variable name="RateFour">
        <xsl:if test ="./observationWeight = 4">
          <xsl:value-of select="./observedRate"/>
        </xsl:if>
        <xsl:if test ="following-sibling::historicalObservation[1]/observationWeight = 4">
          <xsl:value-of select="following-sibling::historicalObservation[1]/observedRate"/>
        </xsl:if>
        <xsl:if test ="following-sibling::historicalObservation[2]/observationWeight = 4">
          <xsl:value-of select="following-sibling::historicalObservation[2]/observedRate"/>
        </xsl:if>
        <xsl:if test ="following-sibling::historicalObservation[3]/observationWeight = 4">
          <xsl:value-of select="following-sibling::historicalObservation[3]/observedRate"/>
        </xsl:if>
      </xsl:variable>

      <xsl:value-of select ="./observationDate"/>
      <xsl:text>&#9;</xsl:text>
      <xsl:value-of select="$RateOne"/>
      <xsl:text>&#9;</xsl:text>
      <xsl:value-of select="$RateTwo"/>
      <xsl:text>&#9;</xsl:text>
      <xsl:value-of select="$RateThree"/>
      <xsl:text>&#9;</xsl:text>
      <xsl:value-of select="$RateFour"/>
      <xsl:text>&#0010;</xsl:text>
  </xsl:for-each>
  </xsl:template>

</xsl:stylesheet>

[lokesh.r.kandula]

Thanks a lot for your code. Can you clarify me the below 2 lines of your code. I couldn't understand how are you iterating through each 'historicalObservation' tag by using key element and generate-id function?

<xsl:key name="keyByID" match="historicalObservation" use="observationDate"/>


<xsl:for-each select="historicalObservation[generate-id()=generate-id(key('keyByID', observationDate)[1])]">

Thanks.

[Mike Askew]

http://www.w3.org/TR/xslt#key

The key is used to match the unique date entries. For example when applied to your example data, select="historicalObservation[generate-id()=generate-id(key('keyByID', observationDate)[1])]" returns nodes #1 and #5, the two start nodes of date blocks, from which I know the next three nodes are the others related to the selected date.

[lokesh.r.kandula]

Thank you for your code and explanation MikeyIsMe.
But somehow I am not feeling comfortable with xsl:key and 'generate-id' function. I am reading about these on the web and probably I may need to look into more examples to get used to these. In the mean while, I was trying for the code and able to come up with the below shown. I have mentioned 'Line' and 'Col' in the code for easy understanding of Matrix type output.
Thanks a lot again...!!!

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
  <xsl:template match="/">
    <xsl:variable name="dates" select="//historicalObservation/observationDate[not(.=preceding::observationDate)]" />
    <xsl:variable name="observations" select="//historicalObservations/historicalObservation" />
    <xsl:for-each select="$dates">
      <xsl:variable name="this" select="." />
      <Line>
        <Col1 Alias="Date">
          <xsl:value-of select="." />
        </Col1>
        <Col2 Alias="observedRate1">
          <xsl:value-of select="$observations[observationDate[.=$this] and observationWeight=1]/observedRate" />
        </Col2>
        <Col3 Alias="observedRate2">
          <xsl:value-of select="$observations[observationDate[.=$this] and observationWeight=2]/observedRate" />
        </Col3>
        <Col4 Alias="observedRate3">
          <xsl:value-of select="$observations[observationDate[.=$this] and observationWeight=3]/observedRate" />
        </Col4>
        <Col5 Alias="observedRate4">
          <xsl:value-of select="$observations[observationDate[.=$this] and observationWeight=4]/observedRate" />
        </Col5>
      </Line>
    </xsl:for-each>
  </xsl:template>
</xsl:stylesheet>