none
need xslt with xpath help

    Question

  • I have an xml file that has 5 columns output from sql.

    I'm trying to create the below html file using the below xslt. I can't get the Date on top of the html table to be centered and read Date 9/10/2013

    Also I need to add "%" to the Percentage column

    Here is a sample XML

    <FieldingCounts>
      <Row>
        <day_date>2013-09-23T00:00:00</day_date>
        <account_name>[none]</account_name>
        <account_key>-1</account_key>
        <lob_name>[none]</lob_name>
        <lob_key>-1</lob_key>
        <CallHandled>678</CallHandled>
        <Calls_Recorded>336</Calls_Recorded>
        <CallMissing>342</CallMissing>
        <Percentage>49.55</Percentage>
      </Row>
      <Row>
        <day_date>2013-09-23T00:00:00</day_date>
        <account_name>ABC</account_name>
        <account_key>342</account_key>
        <lob_name>Grass</lob_name>
        <lob_key>1382</lob_key>
        <CallHandled>1389</CallHandled>
        <Calls_Recorded>1226</Calls_Recorded>
        <CallMissing>163</CallMissing>
        <Percentage>88.26</Percentage>
      </Row>
      <Row>
        <day_date>2013-09-23T00:00:00</day_date>
        <account_name>ABC</account_name>
        <account_key>347</account_key>
        <lob_name>Blocks</lob_name>
        <lob_key>1866</lob_key>
        <CallHandled>2544</CallHandled>
        <Calls_Recorded>2485</Calls_Recorded>
        <CallMissing>59</CallMissing>
        <Percentage>99.28</Percentage>
      </Row>
      <Row>
        <day_date>2013-09-23T00:00:00</day_date>
        <account_name>HD</account_name>
        <account_key>347</account_key>
        <lob_name>Wood</lob_name>
        <lob_key>2417</lob_key>
        <CallHandled>567</CallHandled>
        <Calls_Recorded>567</Calls_Recorded>
        <CallMissing>0</CallMissing>
        <Percentage>100</Percentage>
      </Row>
      <Row>
        <day_date>2013-09-23T00:00:00</day_date>
        <account_name>Lowes</account_name>
        <account_key>505</account_key>
        <lob_name>Nails</lob_name>
        <lob_key>2388</lob_key>
        <CallHandled>2622</CallHandled>
        <Calls_Recorded>0</Calls_Recorded>
        <CallMissing>2622</CallMissing>
        <Percentage>0.00</Percentage>
      </Row>
    </FieldingCounts>

    Here is the sample HTML

    <?xml version="1.0" encoding="UTF-8"?>
    <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:foo="http://www.foo.org/" xmlns:bar="http://www.bar.org">
    <FieldingCounts>
    	<tr>
    	    <td>Date 09-23-2013</td>
        </tr>
      <table border="1">
        <tr bgcolor="lightgrey" cellspacing="right">
          <td>AccountName</td>
          <td>AccountKey</td>
          <td>LOB</td>
          <td>LOBKey</td>
          <td>CallHandled</td>
          <td>CallRecorded</td>
          <td>CallMissing</td>
          <td>Percentage</td>
        </tr>
        <tr  bgcolor="lightyellow">
          <td>[none]</td>
          <td>-1</td>
          <td>[none]</td>
          <td>-1</td>
          <td>678</td>
          <td bgcolor="RED">336</td>
          <td bgcolor="RED">342</td>
          <td bgcolor="RED">49.55%</td>
        </tr>
        <tr  bgcolor="lightyellow">
          <td>ABC</td>
          <td>342</td>
          <td>Grass</td>
          <td>1382</td>
          <td>1389</td>
          <td bgcolor="RED">1226</td>
          <td bgcolor="RED">163</td>
          <td bgcolor="RED">88.26%</td>
        </tr>
        <tr  bgcolor="lightyellow">
          <td>ABC</td>
          <td>347</td>
          <td>Blocks</td>
          <td>1866</td>
          <td>2544</td>
          <td bgcolor="RED">2485</td>
          <td bgcolor="RED">59</td>
          <td bgcolor="RED">97.68%</td>
        </tr>
        <tr  bgcolor="lightyellow">
          <td>HD</td>
          <td>347</td>
          <td>Wood</td>
          <td>2417</td>
          <td>567</td>
          <td bgcolor="lightGreen">567</td>
          <td bgcolor="lightGreen">0</td>
          <td bgcolor="lightGreen">100%</td>
        </tr>
        <tr  bgcolor="lightyellow">
          <td>Lowes</td>
          <td>505</td>
          <td>Nails</td>
          <td>2388</td>
          <td>2622</td>
          <td bgcolor="RED">0</td>
          <td bgcolor="RED">2622</td>
          <td bgcolor="RED">0.00%</td>
      </tr>
      </table>
    </FieldingCounts>

    here is my XSLT so far....

    <?xml version="1.0" encoding="UTF-8"?>
    <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
        <xsl:output method="html" indent="yes"/>
         <xsl:template match="/">
     	<tr>
           	  <td>
     	   <xsl:value-of select="."/>
     	  </td>
         	</tr>
        </xsl:template>
        <xsl:template match="/">
            <table border="1">
            		<tr>Date
    			  <xsl:value-of select="//Row/day_date"/>
    			</tr>
                <tr bgcolor="lightgrey">
                    <td></td>
                    <td>AccountName</td>
                    <td>AccountKey</td>
                    <td>LOB</td>
                    <td>LOBKey</td>
                    <td>CallHandled</td>
                    <td>CallRecorded</td>
                    <td>Missing</td>
                    <td>Percentage</td>
                </tr>
                <xsl:for-each select="FieldingCounts/Row">
                    <tr bgcolor="lightyellow">
                        <xsl:for-each select="./*">
                            <td>
                                <!--<xsl:attribute name="bgcolor">-->
                                    <xsl:choose>
                                        <xsl:when test="local-name() = 'Calls_Recorded' and ../CallHandled != text()">
                                           <xsl:attribute name="bgcolor">
                                    	       <xsl:text>F08080</xsl:text>
                                    	</xsl:attribute>
                                    	<xsl:value-of select="."/>
                                        </xsl:when>
                                        <xsl:when test="local-name() = 'Calls_Recorded' and ../CallHandled = text()">
    				        <xsl:attribute name="bgcolor">
    				       <xsl:text>LightGreen</xsl:text>
    				       </xsl:attribute>
    				       <xsl:value-of select="."/>
                                        </xsl:when>
                                        <xsl:when test="local-name() = 'CallMissing' and text() != 0">
    				        <xsl:attribute name="bgcolor">
    				      <xsl:text>F08080</xsl:text>
    				      </xsl:attribute>
    				      <xsl:value-of select="."/>
                                        </xsl:when>
                                        <xsl:when test="local-name() = 'Percentage' and text()&lt;90">
    				       <xsl:attribute name="bgcolor">
    				      <xsl:text>Pink</xsl:text>
    				       </xsl:attribute>
    				       <xsl:value-of select="."/>
                                        </xsl:when>
                                        <xsl:when test="local-name() = 'Percentage' and text()>=99 and text()!=100">
    				       <xsl:attribute name="bgcolor">
    				      <xsl:text>Yellow</xsl:text>
    				       </xsl:attribute>
    				       <xsl:value-of select="."/>
                                        </xsl:when>
                                        <xsl:when test="local-name() = 'Percentage' and text()=100">
    				       <xsl:attribute name="bgcolor">
    				      <xsl:text>LightGreen</xsl:text>
    				       </xsl:attribute>
    				       <xsl:value-of select="."/>
                                        </xsl:when>
                                        <xsl:when test="local-name() != 'day_date'">
    					<xsl:value-of select="."/>
    		    		    </xsl:when>
                                    </xsl:choose>
                              <!--  </xsl:attribute>
                                <xsl:value-of select="."/>-->
                            </td>
                        </xsl:for-each>
                    </tr>
                </xsl:for-each>
            </table>
        </xsl:template>
    </xsl:stylesheet>

    Wednesday, October 02, 2013 5:13 PM

Answers

  • Hello,

    Thanks for posting your question to this forum.

    With your code, I made a sample and please have a look at it below:

    <?xml version="1.0" encoding="UTF-8"?>
    <xsl:stylesheet version="1.0"
                    xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
                    xmlns="http://www.w3.org/1999/xhtml"
                    >
      <xsl:output method="html" indent="yes"/>
      <xsl:template match="/">
        <table border="1">
          <tr>
            <td colspan="8" style="border:none;text-align:center">
              Date:<xsl:value-of select="substring(//Row/day_date,0,11)" />
            </td>
          </tr>
          <tr bgcolor="lightgrey">
            <th>AccountName</th>
            <th>AccountKey</th>
            <th>LOB</th>
            <th>LOBKey</th>
            <th>CallHandled</th>
            <th>CallRecorded</th>
            <th>Missing</th>
            <th>Percentage</th>
          </tr>
          <xsl:for-each select="FieldingCounts/Row">
            <tr bgcolor="lightyellow">
              <td>
                <xsl:value-of select="account_name"/>
              </td>
              <td>
                <xsl:value-of select="account_key"/>
              </td>
              <td>
                <xsl:value-of select="lob_name"/>
              </td>
              <td>
                <xsl:value-of select="lob_key"/>
              </td>
              <td>
                <xsl:value-of select="CallHandled"/>
              </td>
              <td>
                <xsl:if test="CallHandled != Calls_Recorded">
                  <xsl:attribute name="bgcolor">
                    <xsl:text>F08080</xsl:text>
                  </xsl:attribute>
                  <xsl:value-of select="Calls_Recorded"/>
                </xsl:if>
                <xsl:if test="CallHandled = Calls_Recorded">
                  <xsl:attribute name="bgcolor">
                    <xsl:text>LightGreen</xsl:text>
                  </xsl:attribute>
                  <xsl:value-of select="Calls_Recorded"/>
                </xsl:if>
              </td>
              <td>
                <xsl:if test="CallMissing != 0">
                  <xsl:attribute name="bgcolor">
                    <xsl:text>F08080</xsl:text>
                  </xsl:attribute>
                  <xsl:value-of select="CallMissing"/>
                </xsl:if>
                <xsl:if test="CallMissing = 0">
                  <xsl:value-of select="CallMissing"/>
                </xsl:if>
              </td>
              <td>
                <xsl:if test="Percentage &lt;90">
                  <xsl:attribute name="bgcolor">
                    <xsl:text>Pink</xsl:text>
                  </xsl:attribute>
                  <xsl:value-of select="Percentage"/>%
                </xsl:if>
                <xsl:if test="Percentage >=99 and Percentage!=100">
                  <xsl:attribute name="bgcolor">
                    <xsl:text>Yellow</xsl:text>
                  </xsl:attribute>
                  <xsl:value-of select="Percentage"/>%
                </xsl:if>
                <xsl:if test="Percentage = 100">
                  <xsl:attribute name="bgcolor">
                    <xsl:text>LightGreen</xsl:text>
                  </xsl:attribute>
                  <xsl:value-of select="Percentage"/>%
                </xsl:if>
              </td>
            </tr>
          </xsl:for-each>
        </table>
      </xsl:template>
    </xsl:stylesheet>

    And the result:

    More information about

    XSL:

    http://www.w3schools.com/xsl/xsl_languages.asp

    XPath function:

    http://www.w3schools.com/xpath/xpath_functions.asp

    Regards.


    <THE CONTENT IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, WHETHER EXPRESS OR IMPLIED>
    Thanks
    MSDN Community Support

    Please remember to "Mark as Answer" the responses that resolved your issue. It is a common way to recognize those who have helped you, and makes it easier for other visitors to find the resolution later.



    Friday, October 04, 2013 8:24 AM
    Moderator