Excel Generation with XSLT from PL/SQL

user10316500

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

Paulzip

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.

jaramill

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

Sorry

the correct sql I have is:

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

But the problem remains.

jaramill

user10316500 wrote:But the problem remains.

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

John Thorton

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

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.

user10316500

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

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

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

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

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.