XML - XSL to select from specific node and output horizontally

Question

I have a xml file looks like below and I would like to get the Rate from a specific Date (2017-01-01 in my example). But the problem is some of them have multiple rates and they should be all come out horizontally. The format has no problem and I can get the Rate1 based on xsl:stylesheet version="1.0". But how do I get the Rate2 and Rate3? Please help and here is the example.

Here is the xml:

<Table>
    <TableName>Table A</TableName>
    <Details>
        <Date>2016-01-01</Date>
        <Rate>0.01</Rate>
    </Details>
    <Details>
        <Date>2017-01-01</Date>
        <Rate>0.01</Rate>
    </Details>
</Table>
<Table>
    <TableName>Table B</TableName>
    <Details>
        <Date>2016-01-01</Date>
        <Rate>0.02</Rate>
    </Details>
    <Details>
        <Date>2017-01-01</Date>
        <Rate>0.02</Rate>
    </Details>
</Table>
<Table>
    <TableName>Table C</TableName>
    <Details>
        <Date>2016-01-01</Date>
        <Rate>0.03</Rate>
    </Details>
    <Details>
        <Date>2016-01-01</Date>
        <Rate>0.04</Rate>
    </Details>
    <Details>
        <Date>2016-01-01</Date>
        <Rate>0.05</Rate>
    </Details>
    <Details>
        <Date>2017-01-01</Date>
        <Rate>0.03</Rate>
    </Details>
    <Details>
        <Date>2017-01-01</Date>
        <Rate>0.04</Rate>
    </Details>
    <Details>
        <Date>2017-01-01</Date>
        <Rate>0.05</Rate>
    </Details>
</Table>

Here is the XSL:

<!--I have a variable defined $Date20170101 to get 2017-01-01-->
<xsl:if test="Details/Date = $Date20170101">
    <tr>
        <th>
            <xsl:value-of select="TableName"/>
        </th>
        <td>
            <!--"format-date" is to format the date to mm/dd/yyyy-->
            <xsl:call-template name="format-date">
                <xsl:with-param name="date">
                    <xsl:value-of select="Details/Date"/>
                </xsl:with-param>
            </xsl:call-template>
        </td>
        <!--Below is the problem I have-->
        <xsl:if test="(Details/Rate)[1]">
            <td>
                <xsl:value-of select="(Details/Rate)[1]"/>
            </td>
        </xsl:if>
        <xsl:if test="(Details/Rate)[2]">
            <td>
                <xsl:value-of select="(Details/Rate)[2]"/>
            </td>
        </xsl:if>
        <xsl:if test="(Details/Rate)[3]">
            <td>
                <xsl:value-of select="(Details/Rate)[2]"/>
            </td>
        </xsl:if>
    </tr>
</xsl:if>

Here is the expected results:

Table Name          Date        Rate1       Rate2       Rate3
Table A             1/1/2017    0.01        N/A         N/A
Table B             1/1/2017    0.02        N/A         N/A
Table C             1/1/2017    0.03        0.04        0.05

"some of them have multiple rates and they should be all come out horizontally." So if one of the tables has 5 rates for the given dates, you want the output to have 5 RateN columns? — michael.hor257k
– michael.hor257k, Commented Aug 9, 2016 at 15:55
Thank you for helping. The maximum columns of rates will be 3 and not more. I hope they don't increase that requirement in the future. — ian0411
– ian0411, Commented Aug 10, 2016 at 22:31

michael.hor257k · Accepted Answer · 2016-08-10 22:55:41Z

The maximum columns of rates will be 3 and not more

Well, then it can be rather simple:

XSLT 1.0

<xsl:stylesheet version="1.0" 
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="xml" omit-xml-declaration="yes" version="1.0" encoding="utf-8" indent="yes"/>

<xsl:param name="given-date">2017-01-01</xsl:param>

<xsl:template match="/root">
    <table border="1">
        <tr>
            <th>Table Name</th>
            <th>Date</th>
            <th>Rate1</th>
            <th>Rate2</th>
            <th>Rate3</th>
        </tr>
        <xsl:for-each select="Table">
            <tr>
                <td>
                    <xsl:value-of select="TableName"/>
                </td>
                <td>
                    <xsl:value-of select="$given-date"/>
                </td>
                <xsl:variable name="details" select="Details[Date=$given-date]" />
                <td>
                    <xsl:value-of select="$details[1]/Rate"/>
                </td>
                <td>
                    <xsl:value-of select="$details[2]/Rate"/>
                </td>
                <td>
                    <xsl:value-of select="$details[3]/Rate"/>
                </td>
            </tr>
        </xsl:for-each>
    </table>
</xsl:template>

</xsl:stylesheet>

When this is applied to a well-formed (!) XML input such as:

XML

<root>
    <Table>
        <TableName>Table A</TableName>
        <Details>
            <Date>2016-01-01</Date>
            <Rate>0.01</Rate>
        </Details>
        <Details>
            <Date>2017-01-01</Date>
            <Rate>0.01</Rate>
        </Details>
    </Table>
    <Table>
        <TableName>Table B</TableName>
        <Details>
            <Date>2016-01-01</Date>
            <Rate>0.02</Rate>
        </Details>
        <Details>
            <Date>2017-01-01</Date>
            <Rate>0.02</Rate>
        </Details>
    </Table>
    <Table>
        <TableName>Table C</TableName>
        <Details>
            <Date>2016-01-01</Date>
            <Rate>0.03</Rate>
        </Details>
        <Details>
            <Date>2016-01-01</Date>
            <Rate>0.04</Rate>
        </Details>
        <Details>
            <Date>2016-01-01</Date>
            <Rate>0.05</Rate>
        </Details>
        <Details>
            <Date>2017-01-01</Date>
            <Rate>0.03</Rate>
        </Details>
        <Details>
            <Date>2017-01-01</Date>
            <Rate>0.04</Rate>
        </Details>
        <Details>
            <Date>2017-01-01</Date>
            <Rate>0.05</Rate>
        </Details>
    </Table>
</root>

the result will be:

<table border="1">
   <tr>
      <th>Table Name</th>
      <th>Date</th>
      <th>Rate1</th>
      <th>Rate2</th>
      <th>Rate3</th>
   </tr>
   <tr>
      <td>Table A</td>
      <td>2017-01-01</td>
      <td>0.01</td>
      <td/>
      <td/>
   </tr>
   <tr>
      <td>Table B</td>
      <td>2017-01-01</td>
      <td>0.02</td>
      <td/>
      <td/>
   </tr>
   <tr>
      <td>Table C</td>
      <td>2017-01-01</td>
      <td>0.03</td>
      <td>0.04</td>
      <td>0.05</td>
   </tr>
</table>

rendered as:

Wow, this is awesome and it worked like a charm. The <xsl:variable name="details" select="Details[Date=$given-date]" /> is the key here. That took me a while to understand but it is very neat and clear. Thank you so much, Michael, I really appreciated your time and quick help.

Collectives™ on Stack Overflow

XML - XSL to select from specific node and output horizontally

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related