2 Replies Latest reply: Mar 8, 2011 8:35 AM by 828172 RSS

    Reporting tools and XMLType data

    828172
      Looking for a reporting tool that will allow business users troubleshooting data issue the capability of writing reports against tables that have data in XMLType columns without requiring that the users be able to fluently read XML. All help appreciated.

      Art
        • 1. Re: Reporting tools and XMLType data
          odie_63
          Hi,

          Not a ready-to-use tool, but you may use XSLT to transform the XML content into a user-friendly HTML report.

          Here's an example :

          From this XML document :
          <departments>
            <department name="Administration">
              <employee id="200">
                <first_name>Jennifer</first_name>
                <last_name>Whalen</last_name>
                <phone_number>515.123.4444</phone_number>
              </employee>
            </department>
            <department name="Marketing">
              <employee id="201">
                <first_name>Michael</first_name>
                <last_name>Hartstein</last_name>
                <phone_number>515.123.5555</phone_number>
              </employee>
              <employee id="202">
                <first_name>Pat</first_name>
                <last_name>Fay</last_name>
                <phone_number>603.123.6666</phone_number>
              </employee>
            </department>
            <department name="Purchasing">
              <employee id="114">
                <first_name>Den</first_name>
                <last_name>Raphaely</last_name>
                <phone_number>515.127.4561</phone_number>
              </employee>
          
           <!-- ... more elements here -->
          
            </department>
            <department name="Accounting">
              <employee id="205">
                <first_name>Shelley</first_name>
                <last_name>Higgins</last_name>
                <phone_number>515.123.8080</phone_number>
              </employee>
              <employee id="206">
                <first_name>William</first_name>
                <last_name>Gietz</last_name>
                <phone_number>515.123.8181</phone_number>
              </employee>
            </department>
          </departments>
          with this XSL stylesheet :
          <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
          <xsl:output method="html"/>
          
          <xsl:template match="/">
           <html>
                <head>
                 <style type="text/css">
                  <![CDATA[
              table {border: 1px solid black; border-collapse: collapse;}
              td,th {border: 1px solid black; padding: 3px;}
                  ]]>
                 </style>
                </head>
            <body>
             <div>
                  <h1>Contents : </h1>
                  <ul>
                   <xsl:for-each select="/departments/department">
                        <li>
                     <a href="#{generate-id(@name)}"><xsl:value-of select="@name"/></a>
                    </li>
                   </xsl:for-each>
                  </ul>
                 </div>
                 <xsl:apply-templates/>
            </body>
           </html>
          </xsl:template>
          
          <xsl:template match="department">
           <h2 id="{generate-id(@name)}"><xsl:value-of select="@name"/></h2>
           <table>
            <tr>
             <xsl:for-each select="employee[1]/*">
              <th><xsl:value-of select="translate(name(),'_',' ')"/></th>
             </xsl:for-each>
            </tr>
            <xsl:apply-templates/>
           </table>
          </xsl:template>
          
          <xsl:template match="employee">
           <tr>
            <xsl:apply-templates/>
           </tr>
          </xsl:template>
          
          <xsl:template match="employee/*">
           <td><xsl:value-of select="."/></td>
          </xsl:template>
          
          </xsl:stylesheet>
          we get this : Sample HTML report
          • 2. Re: Reporting tools and XMLType data
            828172
            Interesting thought. Will think about it and if it works for our user community. Thanks for the response and the sample page!