Transform XML in txt file with XSL file

Hi everyone, I need your help.

This is the xml output:


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


<?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

Where is your 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
'----------

%>

try this xsl

<?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>

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

Now the output is:


1, 23982
2, 23977
3, 23976

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


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

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.


<?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>

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


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

:slight_smile:

Hi, I have new problem with this file xml :frowning:

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

Mysql Table (correct):


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

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!

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

Thanks x your answer. :slight_smile:

This is the asp code generate xml file:

<!-- #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:


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

I’m sorry… I confused the _output.xml.

This is right output:

<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

ok, I’ve changed it to take the column names from the “s:Schema” element

try this :stuck_out_tongue:

<?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>

We really appreciate your help- Many thanks ! :wink:

Hi all. :smiley:

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

<?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>

Output XML:


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

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:


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:


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:

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

in

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

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

with:

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

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

You understand me ?

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

Jurn

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

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

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! :slight_smile:

Jurn