3 Replies Latest reply: Apr 16, 2014 5:20 AM by odie_63 RSS

    Generate HTML Table from XML

    user13410062

      Hi All

       

       

      In the below function I need to add HEAD and STYLE to the HTML tags but when I try to hardcode it, it gives me an XML Parsing Error.

      Please can you let me know how can I achieve it?

       

       

      <html>

      <head>

      <style>

      table,th,td

      {

      border:1px solid black;

      border-collapse:collapse;

      }

      </style>

      </head>

      <body>

      <table style='width:300px'>

       

      create or replace FUNCTION get_html_report (p_query IN VARCHAR2) RETURN  CLOB IS

        ctxh            dbms_xmlgen.ctxhandle;

        xslt_tranfsorm  XMLTYPE;

        l_mail_body     CLOB; 

      BEGIN

      ctxh:= dbms_xmlgen.newcontext(p_query);

       

        -- XSLT Transformation to HTML

          xslt_tranfsorm := NEW XMLTYPE('

              <xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">

                <xsl:template match="/ROWSET">

                   <table>   

                    <tr>

                       <xsl:for-each select="ROW[1]/*">

                         <th> <xsl:value-of select="name()"/></th>

                         </xsl:for-each>

                       <xsl:apply-templates/>

                    </tr>

                   </table>

                </xsl:template>

                <xsl:template match="ROW">

                  <tr><xsl:apply-templates/></tr>

                </xsl:template>

                <xsl:template match="ROW/*">

                  <td style="text-align:left;"><xsl:value-of select="."/></td>

                </xsl:template>

              </xsl:stylesheet>'); 

             

          dbms_xmlgen.setnullhandling(ctxh, dbms_xmlgen.empty_tag);

         

          dbms_xmlgen.setxslt(ctxh, xslt_tranfsorm);

         

          l_mail_body := dbms_xmlgen.getxml(ctxh);

         

          dbms_xmlgen.closecontext(ctxh);  

         

         RETURN l_mail_body;

      END get_html_report;

        • 1. Re: Generate HTML Table from XML
          odie_63

          In the below function I need to add HEAD and STYLE to the HTML tags but when I try to hardcode it, it gives me an XML Parsing Error.

          Please, show us what you've tried then.

          • 2. Re: Generate HTML Table from XML
            user13410062

            create or replace FUNCTION get_html_report (p_query IN VARCHAR2) RETURN  CLOB IS

              ctxh            dbms_xmlgen.ctxhandle;

              xslt_tranfsorm  XMLTYPE;

              l_mail_body     CLOB; 

            BEGIN

            ctxh:= dbms_xmlgen.newcontext(p_query);

             

              -- XSLT Transformation to HTML

                xslt_tranfsorm := NEW XMLTYPE('

                    <xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">

                      <xsl:template match="/ROWSET">

            <html>

            <head>

            <style>

            table,th,td

            {

            border:1px solid black;

            border-collapse:collapse;

            }

            </style>

            </head>

            <body>

            <table style='width:300px'>

                          <tr>

                             <xsl:for-each select="ROW[1]/*">

                               <th> <xsl:value-of select="name()"/></th>

                               </xsl:for-each>

                             <xsl:apply-templates/>

                          </tr>

                         </table>

            </body>

                      </xsl:template>

                      <xsl:template match="ROW">

                        <tr><xsl:apply-templates/></tr>

                      </xsl:template>

                      <xsl:template match="ROW/*">

                        <td style="text-align:left;"><xsl:value-of select="."/></td>

                      </xsl:template>

                    </xsl:stylesheet>'); 

                   

                dbms_xmlgen.setnullhandling(ctxh, dbms_xmlgen.empty_tag);

               

                dbms_xmlgen.setxslt(ctxh, xslt_tranfsorm);

               

                l_mail_body := dbms_xmlgen.getxml(ctxh);

               

                dbms_xmlgen.closecontext(ctxh);  

               

               RETURN l_mail_body;

            END get_html_report;

            After compiling this, you can try invoking this function as

             

            DECLARE

            x CLOB;

            BEGIN

            x:= get_html_report('select * from emp');

            dbms_output.put_line(x);

            END;

             

            But invoking this function gives XML Parsing error

            • 3. Re: Generate HTML Table from XML
              odie_63

              First of all the function does not compile, because of this :

              <table style='width:300px'>

              Since the stylesheet is declared inline as a string literal, single quotes must be escaped, or use double quotes : 

              <table style=''width:300px''>

              <table style="width:300px">

               

              Then you get the real error :

              LPX-00225: end-element tag "xsl:template" does not match start-element tag "html"

              I find it pretty self-explanatory, don't you?

              You have not closed the <html> tag :

                      </tr>

                    </table>

                  </body>

                </html>

              </xsl:template>