Forum Stats

  • 3,838,863 Users
  • 2,262,407 Discussions
  • 7,900,774 Comments

Discussions

Excel Generation with XSLT from PL/SQL

user10316500
user10316500 Member Posts: 38 Blue Ribbon
edited Dec 12, 2017 9:27AM in SQL & PL/SQL

Hi

I have a problem with the name of header column in Excel file (xls) generated  with XSLT.

For Example:

If I have following query :

select  'month' as '2017-10' from dual;

In the generated Excel I have '2017-10' converted in 0x0032..ecc...

This is the code I use in PL/SQL:

 v_context := DBMS_XMLGEN.NEWCONTEXT (v_query);            DBMS_XMLGEN.SETNULLHANDLING (v_context, 1);            DBMS_XMLGEN.SETCONVERTSPECIALCHARS (v_context, true);            v_xml_data := DBMS_XMLGEN.GETXMLTYPE (v_context, DBMS_XMLGEN.NONE);            v_output := v_xml_data.transform (XMLTYPE (v_xls_xsl));            v_clob := tool_email_pkg.xmltype2clob (v_output);

The problem is in the XML generated.

My XSL:

<xsl:stylesheet version="1.0"     xmlns="urn:schemas-microsoft-com:office:spreadsheet"     xmlns:xsl="http://www.w3.org/1999/XSL/Transform"     xmlns:msxsl="urn:schemas-microsoft-com:xslt"     xmlns:user="urn:my-scripts"     xmlns:o="urn:schemas-microsoft-com:office:office"     xmlns:x="urn:schemas-microsoft-com:office:excel"     xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" >  <xsl:template match="/*">     <Worksheet>      <xsl:attribute name="ss:Name">        <xsl:value-of select="local-name(/*/*)"/>      </xsl:attribute>      <Table x:FullColumns="1" x:FullRows="1" ss:DefaultColumnWidth="150">        <Row>          <xsl:for-each select="/ROWSET/ROW[1]/*">            <Cell ss:StyleID="StileCella">              <Data ss:Type="String">                <xsl:value-of select="name()"/>              </Data>            </Cell>          </xsl:for-each>        </Row>        <xsl:apply-templates/>      </Table>    </Worksheet>  </xsl:template><xsl:template match="/*/*">  <Row>    <xsl:apply-templates/>  </Row></xsl:template><xsl:template match="/*/*/*"> <Cell>    <Data ss:Type="String">    <xsl:value-of select="." />    </Data> </Cell></xsl:template></xsl:stylesheet>

Stefano

Tagged:
L. Fernigriniuser10316500Paulzip

Best Answer

  • Paulzip
    Paulzip Member Posts: 8,720 Blue Diamond
    edited Dec 11, 2017 2:21PM Answer ✓

    Your problem is with the dbms_xmlgen.getxmltype routine and having a column name starting with a number.  XML has specific rules regarding element names, in particular, Element names must start with a letter or underscore, so it won't allow element names beginning with a number.  Oracle knows this, so when an attempt is made to convert your query to a row/rowset form, it automatically converts the invalid element name to a valid one.

    select dbms_xmlgen.getxmltype(q'[select 'month' as "2017-10" from dualfrom dual

    <ROWSET>

      <ROW>

        <_x0032_017-10>month</_x0032_017-10>

      </ROW>

    </ROWSET>

    Put say an underscore in front...

    select dbms_xmlgen.getxmltype(q'[select 'month' as "_2017-10" from dualfrom dual

    <ROWSET>

      <ROW>

        <_2017-10>month</_2017-10>

      </ROW>

    </ROWSET>

    All is fine.  You need to understand XML has specific rules and they are there for a good reason.

    L. Fernigrini
«1

Answers

  • jaramill
    jaramill Member Posts: 4,299 Gold Trophy
    edited Dec 11, 2017 11:03AM
    user10316500 wrote:Hi I have a problem with the name of header column in Excel file (xls) generated with XSLT.For Example:If I have following query :select 'month' as '2017-10' from dual;Stefano

    Just as an FYI - the SQL you have is incorrect.

    SELECT 'month' AS '2017-10'  FROM dual;

    ORA-00923: FROM keyword not found where expected00923. 00000 -  "FROM keyword not found where expected"*Cause:    *Action:Error at Line: 1 Column: 19

    You're selecting a "string" and labelling it as a quoted string which without the quotes is an ILLEGAL string (must start with an alpha character, and not contain illegal characters such as the minus sign).

    The correct SQL would be :

    SELECT 'month' AS x2017_10  FROM dual;
  • user10316500
    user10316500 Member Posts: 38 Blue Ribbon
    edited Dec 11, 2017 11:24AM

    Sorry

    the correct sql I have is:

    1. SELECT 'month' AS "2017_10" 
    2.   FROM dual; 

    But the problem remains.

  • jaramill
    jaramill Member Posts: 4,299 Gold Trophy
    edited Dec 11, 2017 11:30AM
    user10316500 wrote:But the problem remains.

    What problem??  I don't see any Oracle error you've posted.

  • John Thorton
    John Thorton Member Posts: 14,493 Silver Crown
    edited Dec 11, 2017 11:54AM
    user10316500 wrote:Sorrythe correct sql I have is:SELECT 'month' AS "2017_10"  FROM dual; But the problem remains.

    use COPY & PASTE so we can see what you do & how Oracle responds.

    How do I ask a question on the forums?

  • Paulzip
    Paulzip Member Posts: 8,720 Blue Diamond
    edited Dec 11, 2017 2:21PM Answer ✓

    Your problem is with the dbms_xmlgen.getxmltype routine and having a column name starting with a number.  XML has specific rules regarding element names, in particular, Element names must start with a letter or underscore, so it won't allow element names beginning with a number.  Oracle knows this, so when an attempt is made to convert your query to a row/rowset form, it automatically converts the invalid element name to a valid one.

    select dbms_xmlgen.getxmltype(q'[select 'month' as "2017-10" from dualfrom dual

    <ROWSET>

      <ROW>

        <_x0032_017-10>month</_x0032_017-10>

      </ROW>

    </ROWSET>

    Put say an underscore in front...

    select dbms_xmlgen.getxmltype(q'[select 'month' as "_2017-10" from dualfrom dual

    <ROWSET>

      <ROW>

        <_2017-10>month</_2017-10>

      </ROW>

    </ROWSET>

    All is fine.  You need to understand XML has specific rules and they are there for a good reason.

    L. Fernigrini
  • user10316500
    user10316500 Member Posts: 38 Blue Ribbon
    edited Dec 12, 2017 4:14AM

    Thanks.

    You resolved my problem.

    However, after I generate XML I need delete the underscore ( in the excel file I wan't see it).

    Stefano

  • Paulzip
    Paulzip Member Posts: 8,720 Blue Diamond
    edited Dec 12, 2017 5:27AM

    Then you'll need to change your XSLT to search and replace any underscore that prefixes node names. However, you can only do this if the node gets turned into text content not a node name in the Excel doc's XML.

    user10316500
  • user10316500
    user10316500 Member Posts: 38 Blue Ribbon
    edited Dec 12, 2017 8:09AM

    I used TRANSLATE function and it works:

    <Row>  <xsl:for-each select="/ROWSET/ROW[1]/*">    <Cell ss:StyleID="StileCella">      <Data ss:Type="String">        <xsl:value-of select="translate(name(), '_', '')"/>      </Data>    </Cell>  </xsl:for-each></Row>

    Instead REPLACE function doesn't works.

  • Paulzip
    Paulzip Member Posts: 8,720 Blue Diamond
    edited Dec 12, 2017 8:16AM

    Great. Well if your question is sorted, please mark it as ANSWERED so others with a similar problem will know why and how to solve it.

  • Paulzip
    Paulzip Member Posts: 8,720 Blue Diamond
    edited Dec 12, 2017 8:26AM

    It's noticeable from your posting history that you're a repeat offender in asking questions and never marking them as ANSWERED, this is abusing the forum.  If you ask questions and people take the time and effort to help answer them, extend the courtesy and mark answers HELPFUL or ANSWERED if they aided or solved your issue.  It not only helps you, it helps maintain the site as a knowledge base for others who may have the same issue and are searching for the solution.

    You should go through your posts and close questions by marking with the correct answer.

This discussion has been closed.