Transform XML in txt file with XSL file

**viki1967** · Jan 28, 2011 10:02

Hi everyone, I need your help.

This is the xml output:

Code:

<xml xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" 
 xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" 
 xmlns:rs="urn:schemas-microsoft-com:rowset" 
 xmlns:z="#RowsetSchema">
	<s:Schema id="RowsetSchema">
		<s:ElementType name="row" 
 content="eltOnly" rs:CommandTimeout="30">
			<s:AttributeType name="ID" 
 rs:number="1" rs:nullable="true" rs:writeunknown="true">
				<s:datatype dt:type="int" 
 dt:maxLength="4" rs:precision="10" rs:fixedlength="true"/>
			</s:AttributeType>
			<s:AttributeType name="CODICE" 
 rs:number="2" rs:nullable="true" rs:writeunknown="true">
				<s:datatype 
 dt:type="string" rs:dbtype="str" dt:maxLength="255"/>
			</s:AttributeType>

			<s:extends type="rs:rowbase"/>
		</s:ElementType>
	</s:Schema>
		
<rs:data>
                <z:row ID="1" CODE="23982" />
		<z:row ID="2" CODE="23977" />
		<z:row ID="3" CODE="23976" />
	</rs:data>
</xml>

I need save transform this xml file in the txt file with xsl file:

Code:

<?xml version="1.0"?>
<xsl:stylesheet version="1.0" 
 xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 
xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882"
xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882"
xmlns:rs="urn:schemas-microsoft-com:rowset" 
 xmlns:z="#RowsetSchema">
<xsl:output omit-xml-declaration="yes"/>
<xsl:template match="/">
        <xsl:for-each select="/xml/rs:data/z:row">
                <xsl:for-each select="@*">
                        <xsl:value-of select="."/>      
                        <xsl:text>, </xsl:text>
                </xsl:for-each>
        </xsl:for-each>
</xsl:template>
</xsl:stylesheet>

But I have this output incorrect: 1, 23982, 2, 23977, 3, 23976

The output correct for me this is:

1, 23982
2, 23977
3, 23976

Can you help me?

Thanks in advance.
Viki

**Phidev** · Jan 29, 2011 01:44

Where is your code?

**viki1967** · Jan 29, 2011 03:06

Originally Posted by Phidev

Where is your code?

PHP Code:


<% 



'----------

  Dim xmlSource

  Dim xmlXForm

  Dim strResult

   

  Dim fso , file

  Dim strPath

  Const ForReading = 1

  Const ForWriting = 2

  Const ForAppending = 8

 

  Set xmlSource = CreateObject("MSXML2.DOMDocument.4.0")

  Set xmlXForm = CreateObject("MSXML2.DOMDocument.4.0")

 

  xmlSource.validateOnParse = True

  xmlXForm.validateOnParse = True

  xmlSource.async = False

  xmlXForm.async = False

 

  xmlSource.Load Server.Mappath("_output.xml")

   

  xmlXForm.Load Server.Mappath("_Trasform.xsl")

     

  strResult = xmlSource.transformNode(xmlXForm)

   

  Set fso = CreateObject("Scripting.FileSystemObject")

  strPath = Server.Mappath("_output.txt")

  Set file = fso.opentextfile(strPath, ForWriting, True)

  file.write strResult

  file.Close 

  Set file = Nothing

  

  Set fso = Nothing

  

  Set xmlSource = Nothing

  Set xmlXForm = Nothing

'----------



%>

**jurn** · Jan 29, 2011 15:56

try this xsl

Code:

<?xml version="1.0"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema">
	<xsl:output omit-xml-declaration="yes"/>
	<xsl:template match="/">
		<xsl:for-each select="/xml/rs:data/z:row">
			<xsl:for-each select="@*">
				<xsl:value-of select="."/>
				<xsl:choose>
					<xsl:when test="position()!=last()">
						<xsl:text>,</xsl:text>
					</xsl:when>
					<xsl:otherwise>
						<xsl:text>&#xa;</xsl:text>
					</xsl:otherwise>
				</xsl:choose>
			</xsl:for-each>
		</xsl:for-each>
	</xsl:template>
</xsl:stylesheet>

**viki1967** · Jan 29, 2011 16:08

Your help is greatly appreciated and your xsl working !!!

Now the output is:

Code:

1, 23982
2, 23977
3, 23976

If I have need this output? (header columns...):

Code:

id,	code
1, 	23982
2, 	23977
3, 	23976

**jurn** · Jan 31, 2011 08:16

Here's another sample that looks at the first data row to get the column names. I'm not sure if this is where you expect the names to come from?
hope it helps.

Code:

<?xml version="1.0"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema">
	<xsl:output omit-xml-declaration="yes"/>
	<xsl:template match="/">
		<!-- show a header by looking attributes of the first data row-->
		<xsl:for-each select="/xml/rs:data/z:row[1]/@*">
			<xsl:value-of select="name()"/>
			<xsl:choose>
				<xsl:when test="position()!=last()">
					<xsl:text>,</xsl:text>
				</xsl:when>
				<xsl:otherwise>
					<xsl:text>&#xa;</xsl:text>
				</xsl:otherwise>
			</xsl:choose>
		</xsl:for-each>
		<!-- row data -->
		<xsl:for-each select="/xml/rs:data/z:row">
			<xsl:for-each select="@*">
				<xsl:value-of select="."/>
				<xsl:choose>
					<xsl:when test="position()!=last()">
						<xsl:text>,</xsl:text>
					</xsl:when>
					<xsl:otherwise>
						<xsl:text>&#xa;</xsl:text>
					</xsl:otherwise>
				</xsl:choose>
			</xsl:for-each>
		</xsl:for-each>
	</xsl:template>
</xsl:stylesheet>

**viki1967** · Jan 31, 2011 11:14

Many thanks for your decisive help!
I need now my output:

Code:

id,	code
1, 	23982
2, 	23977
3, 	23976

**viki1967** · Feb 17, 2011 09:51

Hi, I have new problem with this file xml

If I have column value NULL in the mysql table the export is wrong.

Mysql Table (correct):

Code:

ID	DATA_INS	TG	GRUP	NAME
3626	29/11/2010	I	63	MED
3630	29/11/2010	P	70	MARIO                
22505	15/02/2011		6	COND
3630	29/11/2010	P	70	MARIO

Export to xml (wrong):

Code:

ID	DATA_INS	TG	GRUP	NAME
3626	29/11/2010	I	63	MED
3630	29/11/2010	P	70	MARIO                
22505	15/02/2011	6	COND
3630	29/11/2010	P	70	MARIO

The value of next column (GRUP value 6) to move in the previous column when values is NULL (TG)...

Can you help me?
Thanks!

**jurn** · Feb 17, 2011 12:58

what does the xml file look like? (the source xml before the xsl is run).

**viki1967** · Feb 17, 2011 13:10

Originally Posted by jurn

what does the xml file look like? (the source xml before the xsl is run).

Thanks x your answer.

This is the asp code generate xml file:

PHP Code:


<!-- #include virtual="/_inc/_open_conn_mysql.asp" -->



<% 



 Session.Timeout = 200 

 Response.Buffer = True 

  

 filename = "_output.xml"

 

 Set fso = Server.CreateObject("Scripting.FileSystemObject") 

    if fso.FileExists(Server.MapPath("_XML/" & filename & "")) then 

    fso.DeleteFile(Server.MapPath("_XML/" & filename & "")) 

 end if 



 Set fso = Nothing 



 Set xmlDoc = CreateObject("MSXML2.DOMDocument.4.0")

 Set xslDoc = CreateObject("MSXML2.DOMDocument.4.0")



%>



<!-- #include virtual="/_inc/adovbs.inc" -->



<%





   sql = "SELECT * FROM tbl_c" 

            

   Set Rs = CreateObject("ADODB.Recordset")

   Rs.Open SQL, objconn

      

   if not Rs.eof then 

   

   Rs.Save xmlDoc, 1 

   xmlDoc.Save Server.MapPath("_XML/" & filename & "")

   

   end if



   Rs.Close() 

   Set Rs = Nothing 



%>



<%



 Set oDOM = CreateObject("MSXML2.DOMDocument.4.0")

 oDOM.async = False 

 

 oDOM.Load Server.Mappath("_XML/" & filename & "")

  

 Set oXSL = CreateObject("MSXML2.DOMDocument.4.0") 

 oXSL.async = False 

 

 oXSL.Load Server.Mappath("/_inc/Trasform_csv.xsl") 

 

 strTransform = oDOM.transformNode(oXSL) 

 

 Set fso = CreateObject("Scripting.FileSystemObject")

  

 strPath = Server.Mappath("_XML/_output.csv") 

 Session("FileName") = Server.Mappath("_XML/_output.csv") 

   

 Set file = fso.opentextfile(strPath, 2, True)

 file.write strTransform



 file.Close() 

 Set file = Nothing

  

 Set fso = Nothing

                        

 Set oDOM = Nothing 

 Set oXML = Nothing 

 Set oXSL = Nothing 

   

%>



<!-- #include virtual="/_inc/_close_conn_mysql.asp" -->

And this is the _output.xml:

PHP Code:


<xml xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema">

    <s:Schema id="RowsetSchema">

        <s:ElementType name="row" content="eltOnly" rs:CommandTimeout="30">



            <s:AttributeType name="ID" rs:number="1" rs:nullable="true" rs:writeunknown="true">

                <s:datatype dt:type="int" dt:maxLength="4" rs:precision="10" rs:fixedlength="true"/>

            </s:AttributeType>



            <s:AttributeType name="name" rs:number="2" rs:nullable="true" rs:writeunknown="true">

                <s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="255"/>

            </s:AttributeType>



            <s:AttributeType name="email" rs:number="3" rs:nullable="true" rs:writeunknown="true">

                <s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="255"/>

            </s:AttributeType>



            <s:AttributeType name="orig" rs:number="4" rs:nullable="true" rs:writeunknown="true">

                <s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="255"/>

            </s:AttributeType>



            <s:extends type="rs:rowbase"/>

        </s:ElementType>

    </s:Schema>

    <rs:data>

        <z:row ID="1" name="walter" email="...@..." ORIG="0"/>

        <z:row ID="2" name="john" email="...@..." ORIG="1"/>

        <z:row ID="3" name="tony" email="...@..." ORIG="0"/>

    </rs:data>

</xml>

**viki1967** · Feb 17, 2011 14:00

I'm sorry... I confused the _output.xml.

This is right output:

PHP Code:


<xml xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema">

    <s:Schema id="RowsetSchema">

        <s:ElementType name="row" content="eltOnly" rs:CommandTimeout="30">



            <s:AttributeType name="ID" rs:number="1" rs:nullable="true" rs:writeunknown="true">

                <s:datatype dt:type="int" dt:maxLength="4" rs:precision="10" rs:fixedlength="true"/>

            </s:AttributeType>



            <s:AttributeType name="DATA_INS" rs:number="2" rs:nullable="true" rs:writeunknown="true">

                <s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="255"/>

            </s:AttributeType>



            <s:AttributeType name="TG" rs:number="3" rs:nullable="true" rs:writeunknown="true">

                <s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="255"/>

            </s:AttributeType>



            <s:AttributeType name="GRUP" rs:number="4" rs:nullable="true" rs:writeunknown="true">

                <s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="255"/>

            </s:AttributeType>



            <s:AttributeType name="NAME" rs:number="5" rs:nullable="true" rs:writeunknown="true">

                <s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="255"/>

            </s:AttributeType>



            <s:extends type="rs:rowbase"/>

        </s:ElementType>

    </s:Schema>

    <rs:data>

        <z:row ID="3626" DATA_INS="29/11/2010" TG="I" GRUP="63" NAME="MED"/>

        <z:row ID="3630" DATA_INS="29/11/2010" TG="P" GRUP="70" NAME="MARIO"/>

        <z:row ID="3631" DATA_INS="15/02/2011" GRUP="6" NAME="COND"/>

        <z:row ID="3632" DATA_INS="29/11/2010" TG="P" GRUP="70" NAME="MARIO"/>

    </rs:data>

</xml>

In the row ID = 3631 field TG is missing because in the table mysql is value null... :S

**jurn** · Feb 17, 2011 19:25

ok, I've changed it to take the column names from the "s:Schema" element

try this :P

Code:

<?xml version="1.0"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema">
	<xsl:output omit-xml-declaration="yes"/>
	<xsl:template match="/">
		<!-- show a header by looking Schema element -->
		<xsl:for-each select="/xml/s:Schema/s:ElementType/s:AttributeType">
			<xsl:value-of select="@name"/>
			<xsl:choose>
				<xsl:when test="position()!=last()">
					<xsl:text>,</xsl:text>
				</xsl:when>
				<xsl:otherwise>
					<xsl:text>&#xa;</xsl:text>
				</xsl:otherwise>
			</xsl:choose>
		</xsl:for-each>
		<!-- row data -->
		<xsl:for-each select="/xml/rs:data/z:row">
      <xsl:variable name="row" select="."/>
      <!-- for each column -->
			<xsl:for-each select="/xml/s:Schema/s:ElementType/s:AttributeType">
			   <!-- display each column if it exists -->
			  <xsl:variable name="columnName" select="@name"/>
				<!-- go through all columns and display something that matches-->
				<xsl:value-of select="$row/@*[name()=$columnName]"/>
				<xsl:if test="position()!=last()">
					<xsl:text>,</xsl:text>
				</xsl:if>
	    </xsl:for-each>
			<xsl:text>&#xa;</xsl:text>
	  </xsl:for-each>
	</xsl:template>
</xsl:stylesheet>

**viki1967** · Feb 18, 2011 01:53

We really appreciate your help- Many thanks !

**viki1967** · Feb 21, 2011 09:05

Hi all.

I have another problem with this code:

1) In my table of mysql I have this value in the field P: 6.6 (decimal 10,2)
2) In xml output file I have this value: P = "6.60"
3) In the output excel (xls) I see in the field value 0,291666666666667

Can you help me?
Thanks

Code:

<?xml version="1.0"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema">
	<xsl:output omit-xml-declaration="yes"/>
	<xsl:template match="/">
		<!-- show a header by looking Schema element -->
		<xsl:for-each select="/xml/s:Schema/s:ElementType/s:AttributeType">
			<xsl:value-of select="@name"/>
			<xsl:choose>
				<xsl:when test="position()!=last()">
					<xsl:text>&#x09;</xsl:text>
				</xsl:when>
				<xsl:otherwise>
					<xsl:text>&#xa;</xsl:text>
				</xsl:otherwise>
			</xsl:choose>
		</xsl:for-each>
		<!-- row data -->
		<xsl:for-each select="/xml/rs:data/z:row">
      <xsl:variable name="row" select="."/>
      <!-- for each column -->
			<xsl:for-each select="/xml/s:Schema/s:ElementType/s:AttributeType">
			   <!-- display each column if it exists -->
			  <xsl:variable name="columnName" select="@name"/>
				<!-- go through all columns and display something that matches-->
				<xsl:value-of select="$row/@*[name()=$columnName]"/>
				<xsl:if test="position()!=last()">
					<xsl:text>&#x09;</xsl:text>
				</xsl:if>
	    </xsl:for-each>
			<xsl:text>&#xa;</xsl:text>
	  </xsl:for-each>
	</xsl:template>
</xsl:stylesheet>

**viki1967** · Feb 21, 2011 09:38

Output XML:

Code:

<xml xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema">
	<s:Schema id="RowsetSchema">
		<s:ElementType name="row" content="eltOnly" rs:CommandTimeout="30">
			<s:AttributeType name="xID" rs:number="1" rs:writeunknown="true">
				<s:datatype dt:type="int" dt:maxLength="4" rs:precision="10" rs:fixedlength="true" rs:maybenull="false"/>
			</s:AttributeType>

			<s:AttributeType name="DATA_INS" rs:number="2" rs:nullable="true" rs:writeunknown="true">
				<s:datatype dt:type="date" dt:maxLength="6" rs:fixedlength="true"/>
			</s:AttributeType>

			<s:AttributeType name="P" rs:number="3" rs:nullable="true" rs:writeunknown="true">
				<s:datatype dt:type="number" rs:dbtype="numeric" dt:maxLength="19" rs:scale="2" rs:precision="10" rs:fixedlength="true"/>
			</s:AttributeType>
		
			<s:extends type="rs:rowbase"/>
		</s:ElementType>
	</s:Schema>
	<rs:data>
           <z:row xID="20375" DATA_INS="2011-02-15" P="6.60"/>
	</rs:data>
</xml>

**jurn** · Feb 21, 2011 13:07

hi viki,

I don't understand what excel has to do with this. After transforming with the xsl you have a tab delimited file...
what did you do with it?

xID DATA_INS P
20375 2011-02-15 6.60

Jurn

**viki1967** · Feb 21, 2011 13:52

Originally Posted by jurn

hi viki,

I don't understand what excel has to do with this. After transforming with the xsl you have a tab delimited file...
what did you do with it?

xID DATA_INS P
20375 2011-02-15 6.60

Jurn

Hi Jurn, thanks x your reply and welcome back.

Your XSL code working well when export to csv file (reply #12) delimited values with "," (comma).

Output csv file:

PHP Code:


xID, DATA_INS, P

 20375, 2011-02-15, 6.60

For open the csv file I use the Microsoft Excel and import csv file with wizard procedure.
When import values of csv file in excel software the value 6.60 is transformed in 0,291666666666667.

Output in excel:

PHP Code:


xID        DATA_INS    P

20375    2011-02-15    0,291666666666667

Now I need with XSL code export through xls file and I change your code in this lines:

PHP Code:


<xsl:text>,</xsl:text>

in

PHP Code:


<xsl:text>&#x09;</xsl:text>

I have output xls but with this problem:
In the output excel (xls) I see in the field value P 0,291666666666667 instead of 6,6.

I believe that in excel the value 6.60 is interpreted as time...

I find one solution but I'm not sure that is the right.

I change this line:

PHP Code:


<xsl:value-of select="$row/@*[name()=$columnName]"/>

with:

PHP Code:


<xsl:value-of select="translate($row/@*[name()=$columnName,'.',',')]" />

And in excel output I see in field P 6,6, right value.

You understand me ?

**jurn** · Feb 22, 2011 13:37

hmm I'm not too familiar with excel, but I have 2007 installed it will ask you about the delimiter (tab in your case) and data type of each column (e.g. text / number / date) as you import the text file.
I imported it from Data -> Get External Data -> From Text and it works ok for me

Jurn

**viki1967** · Feb 24, 2011 12:48

Originally Posted by jurn

hmm I'm not too familiar with excel, but I have 2007 installed it will ask you about the delimiter (tab in your case) and data type of each column (e.g. text / number / date) as you import the text file.
I imported it from Data -> Get External Data -> From Text and it works ok for me

Jurn

I'm happy for you, but I continue with problem...
What You think of the solution?

PHP Code:


<xsl:value-of select="translate($row/@*[name()=$columnName,'.',',')]" />

**jurn** · Feb 25, 2011 03:40

hi viki,
I do not like it, because it is changing the data when it should not need to. It sounds like a workaround.
I would test data (in any column) that contains the '.' character still works as expected.

BUT, if it works for you and your users, that's good enough for me!

Jurn

**viki1967** · Feb 25, 2011 11:36

Originally Posted by jurn

hi viki,
I do not like it, because it is changing the data when it should not need to. It sounds like a workaround.
I would test data (in any column) that contains the '.' character still works as expected.

BUT, if it works for you and your users, that's good enough for me!

Jurn

Hi Jurn, I can send my files?

**jurn** · Feb 26, 2011 09:11

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

**pk18** · Mar 11, 2011 10:56

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

sl="http://www.w3.org/TR/WD-xsl">
<xsl:template match="/">
<xsl

i name="xml">version="1.0"</xsl

i>
<Workbook xmlns="urn:schemas-microsoft-com

ffice:spreadsheet"
xmlns

="urn:schemas-microsoft-com

ffice

ffice"
xmlns

="urn:schemas-microsoft-com

ffice:excel"
xmlns:ss="urn:schemas-microsoft-com

ffice: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

efaultRowHeight="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 <> 0 Then
Response.Write "Parse Error: " & oResults.parseError.reason
Else

'Modify the ss:ExpandedRowCount attribute for the <table> 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

User Tag List

Thread: Transform XML in txt file with XSL file

Thread Tools

Display

Transform XML in txt file with XSL file

Bookmarks

Bookmarks

Posting Permissions