I'm using the sql extension to dinamically retrieve some information from a database .
 
<!-- Setup the JDBC connection -->
<xsl:variable name="connection" as="java:java.sql.Connection" xmlns:java="http://saxon.sf.net/java-type">
<sql:connect database="jdbc:oracle:thin:@myserver.com:1521:DEMODB" driver="oracle.jdbc.driver.OracleDriver" user="DUMMY" password="xxx"/>
</xsl:variable>
<!-- Run the query -->
<xsl:variable name="queryResults">
<sql:query connection="$connection" table="REPORT_PARAMS" where="REPORT_ID=$id" column="PARAMETER_NAME,PARAMETER_VALUE,PARAMETER_TYPE" row-tag="row" column-tag="col"/>
</xsl:variable>
 
The result is something like :
 
<row>
 <col>Parameter1</col>
 <col>CLEAR</col>
 <col>T</col>
</row>
<row>
 <col>Parameter2</col>
 <col>35</col>
 <col>N</col>
</row>
 
I can retrieve data with
<xsl:value-of select="$queryResults/row[1]/col[1]"/>
But this is really bad, and it make things impossible when the position of the fields retrieved are not known.
 
The result I would like to have is :
 
<row>
 <PARAMETER_NAME>Parameter1</PARAMETER_NAME>
 <PARAMETER_VALUE>CLEAR</PARAMETER_VALUE>
 <PARAMETER_TYPE>T</PARAMETER_TYPE>
</row>
...
 
Is that possible to change SQLQuery.java in saxon-sql library in order to have a parameter that include tha field name in the tag instead a fixed one?
 
For example ( from an old SQLQuery.java ) :
 
                // -- Execute Statement
                rs = ps.executeQuery();

                // -- Print out Result
                Receiver out = context.getReceiver();
                int icol = rs.getMetaData().getColumnCount();
                while (rs.next()) {                            // next row
                    //System.out.print("<- SQL : "+ rowStart);
                    out.startElement(rowCode, StandardNames.XS_UNTYPED, locationId, 0);
                    for (int col = 1; col <= icol; col++) {     // next column
                        // Read result from RS only once, because
                        // of JDBC-Specifications
                        result = rs.getString(col);
                        out.startElement(colCode, StandardNames.XS_UNTYPED, locationId, 0);
                        if (result != null) {
                            out.characters(result, locationId, options);
                        }
                        out.endElement();
                    }
                    //System.out.println(rowEnd);
                    out.endElement();
                }
Can be changed in ( this is just an unvalidated suggestion ) :
 
                // -- Execute Statement
                rs = ps.executeQuery();

                // -- Print out Result
                Receiver out = context.getReceiver();
                ResultSetMetaData rmd = rs.getMetaData(); // ResultSet metadata
                int icol = rmd.getColumnCount();
                while (rs.next()) {                            // next row
                    //System.out.print("<- SQL : "+ rowStart);
                    out.startElement(rowCode, StandardNames.XS_UNTYPED, locationId, 0);
                    for (int col = 1; col <= icol; col++) {     // next column
                        // Read result from RS only once, because
                        // of JDBC-Specifications
                        result = rs.getString(col);
                        fieldName = rmd.getColumnName(col);
                        fieldCode = pool.allocate(fieldName);
                        out.startElement(fieldCode , StandardNames.XS_UNTYPED, locationId, 0);
                        if (result != null) {
                            out.characters(result, locationId, options);
                        }
                        out.endElement();
                    }
                    //System.out.println(rowEnd);
                    out.endElement();
                }
Thanks and Regards
Andy