Transform XML in txt file with XSL file

Hi Jurn, I can send my files?

just attach them to this thread, so other people can also help.

Hi,

Even I have a similar problem hope I will get some help here.

I am trying to generate excel report from an ASP application using xsl and excel 2010.

When I generate the report, all the data is populating in only one cell in the excel file instead of being populated as a report with multiple rows.

I have attached the .xsl file. can anyone help me correct it?

<?xml version=“1.0”?>
<xsl:stylesheet xmlns:xsl=“http://www.w3.org/TR/WD-xsl”>
<xsl:template match=“/”>
<xsl:pi name=“xml”>version=“1.0”</xsl:pi>
<Workbook xmlns=“urn:schemas-microsoft-com:office:spreadsheet”
xmlns:o=“urn:schemas-microsoft-com:office:office”
xmlns:x=“urn:schemas-microsoft-com:office:excel”
xmlns:ss=“urn:schemas-microsoft-com:office:spreadsheet”
xmlns:html=“http://www.w3.org/TR/REC-html40”>
<Worksheet ss:Name=“XLReport”>
<Table ss:ExpandedColumnCount=“23” ss:ExpandedRowCount=“1” x:FullColumns=“1”
x:FullRows=“1” ss:DefaultRowHeight=“15”>
<Row ss:Height=“45”>
<Cell><Data ss:Type=“String”>FEEDBACKNUMBER</Data></Cell>
<Cell><Data ss:Type=“String”>CA</Data></Cell>
<Cell><Data ss:Type=“String”>SA</Data></Cell>
<Cell><Data ss:Type=“String”>CUSTOMERNAME</Data></Cell>
<Cell><Data ss:Type=“String”>TYPE</Data></Cell>
<Cell><Data ss:Type=“String”>ASGNTO</Data></Cell>
<Cell><Data ss:Type=“String”>RECVDATE</Data></Cell>
<Cell><Data ss:Type=“String”>DUEDATE</Data></Cell>
<Cell><Data ss:Type=“String”>RESPDATE</Data></Cell>
<Cell><Data ss:Type=“String”>CLOSEDATE</Data></Cell>
<Cell><Data ss:Type=“String”>SENTDATE</Data></Cell>
<Cell><Data ss:Type=“String”>XREFNUM</Data></Cell>
<Cell><Data ss:Type=“String”>DEPTKIND</Data></Cell>
<Cell><Data ss:Type=“String”>SRCDEPT</Data></Cell>
<Cell><Data ss:Type=“String”>CLASS</Data></Cell>
<Cell><Data ss:Type=“String”>CATG</Data></Cell>
<Cell><Data ss:Type=“String”>ESCFLAG</Data></Cell>
<Cell><Data ss:Type=“String”>MEDIA</Data></Cell>
<Cell><Data ss:Type=“String”>PRIORITY</Data></Cell>
<Cell><Data ss:Type=“String”>BILLSRV</Data></Cell>
<Cell><Data ss:Type=“String”>DISP</Data></Cell>
<Cell><Data ss:Type=“String”>ASGNDATE</Data></Cell>
<Cell><Data ss:Type=“String”>a.Cust_Num</Data></Cell>
</Row>
<xsl:for-each select=“xml/rs:data/z:row”>
<Row ss:AutoFitHeight=“0” ss:Height=“16.5”>
<Cell><Data ss:Type=“String”><xsl:value-of select=“@FEEDBACKNUMBER”/></Data></Cell>
<Cell><Data ss:Type=“String”><xsl:value-of select=“@CA”/></Data></Cell>
<Cell><Data ss:Type=“String”><xsl:value-of select=“@SA”/></Data></Cell>
<Cell><Data ss:Type=“String”><xsl:value-of select=“@CUSTOMERNAME”/></Data></Cell>
<Cell><Data ss:Type=“String”><xsl:value-of select=“@TYPE”/></Data></Cell>
<Cell><Data ss:Type=“String”><xsl:value-of select=“@ASGNTO”/></Data></Cell>
<Cell><Data ss:Type=“String”><xsl:value-of select=“@RECVDATE”/></Data></Cell>
<Cell><Data ss:Type=“String”><xsl:value-of select=“@DUEDATE”/></Data></Cell>
<Cell><Data ss:Type=“String”><xsl:value-of select=“@RESPDATE”/></Data></Cell>
<Cell><Data ss:Type=“String”><xsl:value-of select=“@CLOSEDATE”/></Data></Cell>
<Cell><Data ss:Type=“String”><xsl:value-of select=“@SENTDATE”/></Data></Cell>
<Cell><Data ss:Type=“String”><xsl:value-of select=“@XREFNUM”/></Data></Cell>
<Cell><Data ss:Type=“String”><xsl:value-of select=“@DEPTKIND”/></Data></Cell>
<Cell><Data ss:Type=“String”><xsl:value-of select=“@SRCDEPT”/></Data></Cell>
<Cell><Data ss:Type=“String”><xsl:value-of select=“@CLASS”/></Data></Cell>
<Cell><Data ss:Type=“String”><xsl:value-of select=“@CATG”/></Data></Cell>
<Cell><Data ss:Type=“String”><xsl:value-of select=“@ESCFLAG”/></Data></Cell>
<Cell><Data ss:Type=“String”><xsl:value-of select=“@MEDIA”/></Data></Cell>
<Cell><Data ss:Type=“String”><xsl:value-of select=“@PRIORITY”/></Data></Cell>
<Cell><Data ss:Type=“String”><xsl:value-of select=“@BILLSRV”/></Data></Cell>
<Cell><Data ss:Type=“String”><xsl:value-of select=“@DISP”/></Data></Cell>
<Cell><Data ss:Type=“String”><xsl:value-of select=“@ASGNDATE”/></Data></Cell>
<Cell><Data ss:Type=“String”><xsl:value-of select=“@a.Cust_Num”/></Data></Cell>
</Row>
</xsl:for-each>
</Table>
</Worksheet>
</Workbook>
</xsl:template>
</xsl:stylesheet>

asp code

Const sConn = “Connect to DB2 Database”
Const sXSL = “C:\Feedback.xsl”

Response.Buffer = True

'Retrieve an ADO recordset of the Orders Detail table in Northwind
Dim strSQL, rs, nRecords

Set rs = CreateObject("ADODB.Recordset")
rs.Open "strRevSql = "Select ..... ", sConn, 3, 3

'Persist the recordset to a new DOMDocument and store the record count
Dim oXML
Set oXML = CreateObject("Microsoft.XMLDOM")
rs.Save oXML, 1
nRecords = rs.RecordCount
rs.Close

'Load the XSL (the workbook template with XSL directives) into a DOMDocument
Dim oXSL
Set oXSL = CreateObject("Microsoft.XMLDOM")
oXSL.Load sXSL

'Transform the XML using the stylesheet
Dim oResults
Set oResults = CreateObject("Microsoft.XMLDOM")
oXML.transformNodeToObject oXSL, oResults

If oXSL.parseError.errorCode &lt;&gt; 0 Then
   Response.Write "Parse Error: " & oResults.parseError.reason
Else

   'Modify the ss:ExpandedRowCount attribute for the &lt;table&gt; node in the XSL.
    Dim oTable
    Set oTable = oResults.selectSingleNode("Workbook/Worksheet/Table")
    oTable.setAttribute "ss:ExpandedRowCount", nRecords + 2

    'Return the resulting XML Spreadsheet for display in Excel
    Response.ContentType = "application/vnd.ms-excel"
    Response.Charset = "ISO-8859-1"
    Response.Write oResults.XML
    Response.Flush

End If

Query returns the data and If I save it in the local drive as xml file then it saves with all the data. I think my xsl file is wrong.

The ss:ExpandedRowCount attribute for Table element is updated to the total number of rows in the xml file that is saved in the c:\

Thanks in Advance
PK