jpegvarn
asked on
XML/XSL using Saxon processor to Output to Excel file
Hello,
Below is the current XSL file I'd like to use, but would like the column heading not hard-coded because depending on the query, those headings can change. Below the XSL file are two examples of the XML file that could possibly be used with the desired results (in excel format). Thanks.
Current XSL:
<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:saxon="http://icl.com/saxon" extension-element-prefixes ="saxon" version="1.0">
<xsl:variable name="input" select="document('ResultSe t.xml')"/>
<xsl:template match="/">
<saxon:output href="result.xls" method="text">
<xsl:text>LASTNAME</xsl:te xt>
<xsl:text>	</xsl:text>
<xsl:text>FIRSTNAME</xsl:t ext>
<xsl:text>	</xsl:text>
<xsl:text>OTHER</xsl:text>
<xsl:text>
</xsl:text >
<xsl:for-each select="$input/ResultSet/R esultSetDa ta/Row">
<xsl:for-each select="Column" >
<xsl:value-of select="."/>
<xsl:if test="not(position() = last())">
<xsl:text>	</xsl:text>
</xsl:if>
</xsl:for-each>
<xsl:text>
</xsl:text >
</xsl:for-each>
</saxon:output>
</xsl:template>
</xsl:stylesheet>
XML Example 1 with desired results (xls)
<?xml version="1.0" ?>
<ResultSet>
<ResultSetData>
<Row>
<Column name="LASTNAME">Shmoe</Col umn>
<Column name="FIRSTNAME">Joe</Colu mn>
<Column name="OTHER">Other Data</Column>
</Row>
<Row>
<Column name="LASTNAME">Smith</Col umn>
<Column name="FIRSTNAME">John</Col umn>
<Column name="OTHER">Other Data2</Column>
</Row>
</ResultSetData>
</ResultSet>
=
Column1 Column2 Column3
Row1 LASTNAME FIRSTNAME OTHER
Row2 Shmoe Joe Other Data
Row3 Smith John Other Data2
Or, it can look like this:
2.
<?xml version="1.0" ?>
<ResultSet>
<ResultSetData>
<Row>
<Column name="ID">234234</Column>
<Column name="PURCHASEID">5555</Co lumn>
<Column name="OTHER">Other Data</Column>
</Row>
<Row>
<Column name="ID">567567</Column>
<Column name="PURCHASEID">77777</C olumn>
<Column name="OTHER">Other Data2</Column>
</Row>
</ResultSetData>
</ResultSet>
=
Column1 Column2 Column3
Row1 ID PURCHASEID OTHER
Row2 234234 5555 Other Data
Row3 567567 77777 Other Data2
Below is the current XSL file I'd like to use, but would like the column heading not hard-coded because depending on the query, those headings can change. Below the XSL file are two examples of the XML file that could possibly be used with the desired results (in excel format). Thanks.
Current XSL:
<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:saxon="http://icl.com/saxon" extension-element-prefixes
<xsl:variable name="input" select="document('ResultSe
<xsl:template match="/">
<saxon:output href="result.xls" method="text">
<xsl:text>LASTNAME</xsl:te
<xsl:text>	</xsl:text>
<xsl:text>FIRSTNAME</xsl:t
<xsl:text>	</xsl:text>
<xsl:text>OTHER</xsl:text>
<xsl:text>
</xsl:text
<xsl:for-each select="$input/ResultSet/R
<xsl:for-each select="Column" >
<xsl:value-of select="."/>
<xsl:if test="not(position() = last())">
<xsl:text>	</xsl:text>
</xsl:if>
</xsl:for-each>
<xsl:text>
</xsl:text
</xsl:for-each>
</saxon:output>
</xsl:template>
</xsl:stylesheet>
XML Example 1 with desired results (xls)
<?xml version="1.0" ?>
<ResultSet>
<ResultSetData>
<Row>
<Column name="LASTNAME">Shmoe</Col
<Column name="FIRSTNAME">Joe</Colu
<Column name="OTHER">Other Data</Column>
</Row>
<Row>
<Column name="LASTNAME">Smith</Col
<Column name="FIRSTNAME">John</Col
<Column name="OTHER">Other Data2</Column>
</Row>
</ResultSetData>
</ResultSet>
=
Column1 Column2 Column3
Row1 LASTNAME FIRSTNAME OTHER
Row2 Shmoe Joe Other Data
Row3 Smith John Other Data2
Or, it can look like this:
2.
<?xml version="1.0" ?>
<ResultSet>
<ResultSetData>
<Row>
<Column name="ID">234234</Column>
<Column name="PURCHASEID">5555</Co
<Column name="OTHER">Other Data</Column>
</Row>
<Row>
<Column name="ID">567567</Column>
<Column name="PURCHASEID">77777</C
<Column name="OTHER">Other Data2</Column>
</Row>
</ResultSetData>
</ResultSet>
=
Column1 Column2 Column3
Row1 ID PURCHASEID OTHER
Row2 234234 5555 Other Data
Row3 567567 77777 Other Data2
https://www.experts-exchange.com/questions/21098399/Convert-XML-to-CVS.html#11834944
The operative part is:
<xsl:apply-templates select="Row[1]/Column" mode="mHeading"/>
and
<xsl:template match="Column" mode="mHeading">
<xsl:value-of select="@name"/>
<xsl:if test="position() != last()">
<xsl:text>,</xsl:text>
</xsl:if>
</xsl:template>
As for the date, you could add it to the transform as a parameter, then call the transformation, I suppose. At least, that's how I always do it for reporting applications, whether or not I even need the date.
Regards,
Mike Sharp
The operative part is:
<xsl:apply-templates select="Row[1]/Column" mode="mHeading"/>
and
<xsl:template match="Column" mode="mHeading">
<xsl:value-of select="@name"/>
<xsl:if test="position() != last()">
<xsl:text>,</xsl:text>
</xsl:if>
</xsl:template>
As for the date, you could add it to the transform as a parameter, then call the transformation, I suppose. At least, that's how I always do it for reporting applications, whether or not I even need the date.
Regards,
Mike Sharp
Like:
<xsl:param name="pDate" />
<saxon:output href="result{$pDate}.xls" method="text">
but you need to set the parameter in Java, before you call the transform. You can attach nodes, or whatever you want.
Or you can use the Saxon saxon:script extension, and use the java getUTCDate() function or some such to get the date.
<saxon:script implements-prefix="dates" language="java"
src="java:com.icl.saxon.fu nctions.Ex tensions">
Java code here to get the date
Regards,
Mike Sharp
<xsl:param name="pDate" />
<saxon:output href="result{$pDate}.xls" method="text">
but you need to set the parameter in Java, before you call the transform. You can attach nodes, or whatever you want.
Or you can use the Saxon saxon:script extension, and use the java getUTCDate() function or some such to get the date.
<saxon:script implements-prefix="dates" language="java"
src="java:com.icl.saxon.fu
Java code here to get the date
Regards,
Mike Sharp
Hi jpegvarn
Is it possible to change your original XML from
<?xml version="1.0" ?>
<ResultSet>
<ResultSetData>
.
.
.
To
<?xml version="1.0" ?>
<ResultSet>
<Date>date data</Date>
<ResultSetData>
.
.
.
If this possible, you would be able to name the file using <date> from the source file.
Is it possible to change your original XML from
<?xml version="1.0" ?>
<ResultSet>
<ResultSetData>
.
.
.
To
<?xml version="1.0" ?>
<ResultSet>
<Date>date data</Date>
<ResultSetData>
.
.
.
If this possible, you would be able to name the file using <date> from the source file.
ASKER
Unfortunatley not, I am sending a reponse to another server which gives me the formatted xml results.
The following will do the job for you and the result file will be in the format “Mon Sep 13 170906 GMT+0100 2004.xls”
***** the new style sheet *****
<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:saxon="http://icl.com/saxon"
xmlns:date="http://www.jclark.com/xt/java/java.util.Date"
extension-element-prefixes ="saxon" version="1.0">
<xsl:variable name="input" select="document('ResultSe t.xml')"/>
<xsl:template match="/">
<xsl:variable name="filename">
<xsl:if test="function-available(' date:to-st ring') and function-available('date:n ew')">
<xsl:value-of select="concat(translate(d ate:to-str ing(date:n ew()),':', ''),'.xls' )"/>
</xsl:if>
</xsl:variable>
<saxon:output href="{$filename}" method="text">
<xsl:text>LASTNAME</xsl:te xt>
<xsl:text>	</xsl:text>
<xsl:text>FIRSTNAME</xsl:t ext>
<xsl:text>	</xsl:text>
<xsl:text>OTHER</xsl:text>
<xsl:text>
</xsl:text >
<xsl:for-each select="$input/ResultSet/R esultSetDa ta/Row">
<xsl:for-each select="Column" >
<xsl:value-of select="."/>
<xsl:if test="not(position() = last())">
<xsl:text>	</xsl:text>
</xsl:if>
</xsl:for-each>
<xsl:text>
</xsl:text >
</xsl:for-each>
</saxon:output>
</xsl:template>
</xsl:stylesheet>
***** the new style sheet *****
<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:saxon="http://icl.com/saxon"
xmlns:date="http://www.jclark.com/xt/java/java.util.Date"
extension-element-prefixes
<xsl:variable name="input" select="document('ResultSe
<xsl:template match="/">
<xsl:variable name="filename">
<xsl:if test="function-available('
<xsl:value-of select="concat(translate(d
</xsl:if>
</xsl:variable>
<saxon:output href="{$filename}" method="text">
<xsl:text>LASTNAME</xsl:te
<xsl:text>	</xsl:text>
<xsl:text>FIRSTNAME</xsl:t
<xsl:text>	</xsl:text>
<xsl:text>OTHER</xsl:text>
<xsl:text>
</xsl:text
<xsl:for-each select="$input/ResultSet/R
<xsl:for-each select="Column" >
<xsl:value-of select="."/>
<xsl:if test="not(position() = last())">
<xsl:text>	</xsl:text>
</xsl:if>
</xsl:for-each>
<xsl:text>
</xsl:text
</xsl:for-each>
</saxon:output>
</xsl:template>
</xsl:stylesheet>
ASKER
Nice, works great.
Any chance on getting the answer to the original question above?
"Below is the current XSL file I'd like to use, but would like the column heading not hard-coded because depending on the query, those headings can change. Below the XSL file are two examples of the XML file that could possibly be used with the desired results (in excel format). "
thanks.
Any chance on getting the answer to the original question above?
"Below is the current XSL file I'd like to use, but would like the column heading not hard-coded because depending on the query, those headings can change. Below the XSL file are two examples of the XML file that could possibly be used with the desired results (in excel format). "
thanks.
Did you have some objection to my suggestion? That is, replacing:
<xsl:text>LASTNAME</xsl:te xt>
<xsl:text>	</xsl:text>
<xsl:text>FIRSTNAME</xsl:t ext>
<xsl:text>	</xsl:text>
<xsl:text>OTHER</xsl:text>
<xsl:text>
</xsl:text >
with
<xsl:for-each select="Row[1]/Column" />
<xsl:value-of select="@name"/>
<xsl:if test="position() != last()"><xsl:text>
</ xsl:text>
<xsl:text>,</xsl:text>
</xsl:if>
<xsl:if test="position() = last()">
<xsl:text>
</xsl:text >
</xsl:if>
</xsl:for-each>
<xsl:text>LASTNAME</xsl:te
<xsl:text>	</xsl:text>
<xsl:text>FIRSTNAME</xsl:t
<xsl:text>	</xsl:text>
<xsl:text>OTHER</xsl:text>
<xsl:text>
</xsl:text
with
<xsl:for-each select="Row[1]/Column" />
<xsl:value-of select="@name"/>
<xsl:if test="position() != last()"><xsl:text>
</
<xsl:text>,</xsl:text>
</xsl:if>
<xsl:if test="position() = last()">
<xsl:text>
</xsl:text
</xsl:if>
</xsl:for-each>
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Sorry for not responding to you rdcpro, but I did try that solution and it was throwing errors and I didn't understand why (the error was pointing toward a closing tag). Sorry I didn't send back the error at the time, but it was at the end of the day.
Wadalhag, your solution worked perfectly, thank you.
Please go here to see what else I need done for 500 points, thanks guys:
https://www.experts-exchange.com/questions/21129355/Tweaks-needed-for-XSL-sheet-using-Saxon-outputting-to-Excel-file-now.html
Wadalhag, your solution worked perfectly, thank you.
Please go here to see what else I need done for 500 points, thanks guys:
https://www.experts-exchange.com/questions/21129355/Tweaks-needed-for-XSL-sheet-using-Saxon-outputting-to-Excel-file-now.html
thanks jpegvarn
ASKER
Current: <saxon:output href="result.xls" method="text">