XSL file formatting

Miguel61 · August 26, 2011, 10:18am

Hi there.

I have problem with this files xsl, the problem is the output for the field date and number:

2011-05-13T21:50:00 instead of 13/05/2001 21:50:00 (because in mysql the field is dateTime)
.00 instead of 0 (because in mysql the field is decimal 10,2).

Can you help me?
Thanks in advance.

<?xml version="1.0" encoding="ISO-8859-1"?>
<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()">
  				    <!-- xls -->
					<xsl:text>&#x09;</xsl:text>
					<!-- csv -->
					<!--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:value-of select="translate($row/@*[name()=$columnName],'.',',')" /-->
				<xsl:if test="position()!=last()">
				    <!-- xls -->
					<xsl:text>&#x09;</xsl:text>
					<!-- csv -->
					<!--xsl:text>,</xsl:text-->
				</xsl:if>
	    </xsl:for-each>
			<xsl:text>&#xa;</xsl:text>
	  </xsl:for-each>
	</xsl:template>
</xsl:stylesheet>

tamilsuresh · August 26, 2011, 5:31pm

Can you please place current output here. May be few lines, just to understand better about output data based on that we can format date/time.

Miguel61 · August 26, 2011, 9:31pm

Thanks for your answer, I appreciated much.

This is the output in 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="myDates" rs:number="6" rs:nullable="true" rs:writeunknown="true">
				<s:datatype dt:type="dateTime" rs:dbtype="timestamp" dt:maxLength="16" rs:scale="0" rs:precision="19" rs:fixedlength="true"/>
			</s:AttributeType>

			<s:AttributeType name="Net" rs:number="29" 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>

		[B][COLOR="Red"]<z:row myDates="2011-08-22T03:03:52" Net=".00" />[/COLOR][/B]

	</rs:data>
</xml>

This output XML is generate with ASP code and saved in the csv format:


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

<% 

filename = "titty.xml" 

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

%>

<%

sql = "SELECT * FROM tbl_login" 
Set rs = objConn.Execute(sql) 

rs.Save xmlDoc, 1 

xmlDoc.Save Server.MapPath(filename)

rs.Close 
Set rs = Nothing 

%>

<%

 Set oDOM = CreateObject("MSXML2.DOMDocument.4.0")
 oDOM.async = False 
 
 oDOM.Load Server.Mappath(filename)
  
 Set oXSL = CreateObject("MSXML2.DOMDocument.4.0") 
 oXSL.async = False 
 
 [COLOR="Red"][B]oXSL.Load Server.Mappath("Trasform_csv.xsl") [/B][/COLOR] <<< This is the file xsl #1
 
 strTransform = oDOM.transformNode(oXSL) 
 
 Set fso = CreateObject("Scripting.FileSystemObject")
 strPath = Server.Mappath("testme.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 
  
%>

I need another output in the XML file:

[B][COLOR="SeaGreen"]<z:row myDates="13/05/2001 21:50:00" Net="0.00" />[/COLOR][/B]

And in the CSV file:

[COLOR="SeaGreen"][B]13/05/2001 21:50:00, 0[/B][/COLOR]

tamilsuresh · August 27, 2011, 2:55am

I don’t know any direct way to format the date & time.
But i use substring to split date & time to get my required format.

$date variable contains 2011-08-22T03:03:52

<xsl:value-of select=“concat(substring($date, 6,2), ‘/’, substring($date, 9,2), ‘/’, substring($date, 1,4), ’ ', substring($date, 12))” />

Miguel61 · August 27, 2011, 9:58am

Thanks but not working:

msxml4.dll (0x80004005)
Unable to resolve a reference to the variable or parameter “myDates”. The variable or parameter are not defined or are not in valid.

XML file:

<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="myDates" rs:number="1" rs:nullable="true" rs:writeunknown="true">
				<s:datatype dt:type="dateTime" rs:dbtype="timestamp" dt:maxLength="16" rs:scale="0" rs:precision="19" rs:fixedlength="true"/>
			</s:AttributeType>
			<s:AttributeType name="Net" rs:number="2" 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 myDates="2011-05-13T21:50:00" Net=".00"/>
		<z:row myDates="2011-05-13T21:50:00" Net=".00"/>
		<z:row myDates="2011-05-13T21:50:00" Net="2.00"/>
		<z:row myDates="2011-05-13T21:50:00" Net="3.00"/>
		<z:row myDates="2011-05-13T21:50:00" Net="45.00"/>
		<z:row myDates="2011-05-13T21:50:00" Net="9.00"/>
		<z:row myDates="2011-05-13T21:50:00" Net="88.00"/>
		<z:row myDates="2011-05-13T21:50:00" Net="188.00"/>
		<z:row myDates="2011-05-13T21:50:00" Net="14.00"/>
		<z:row myDates="2011-05-13T21:50:00" Net="9.00"/>
		<z:row myDates="2011-05-13T21:50:00" Net="4.00"/>
		<z:row myDates="2011-05-13T21:50:00" Net="2.00"/>
		<z:row myDates="2011-05-13T21:50:00" Net="5.00"/>
		<z:row myDates="2011-05-13T21:50:00" Net="1.00"/>
		<z:row myDates="2011-05-13T21:50:00" Net=".00"/>
		<z:row myDates="2011-05-13T21:50:00" Net=".00"/>
		<z:row myDates="2011-05-13T21:50:00" Net=".00"/>
		<z:row myDates="2011-05-13T21:50:00" Net=".00"/>
		<z:row myDates="2011-05-13T21:50:00" Net="1.00"/>
		<z:row myDates="2011-05-13T21:50:00" Net="4.00"/>
	</rs:data>
</xml>

XSL file:

<?xml version="1.0" encoding="ISO-8859-1"?>
<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()">
  				    <!-- xls -->
					<xsl:text>&#x09;</xsl:text>
					<!-- csv -->
					<!--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]" />
[COLOR="Red"][B]				<xsl:value-of select="concat(substring($myDates, 6,2), '/', substring($myDates, 9,2), '/', substring($myDates, 1,4), ' ', substring($DataInterr, 12))" />[/B][/COLOR] 
				<!--xsl:value-of select="translate($row/@*[name()=$columnName],'.',',')" /-->
				<xsl:if test="position()!=last()">
				    <!-- xls -->
					<xsl:text>&#x09;</xsl:text>
					<!-- csv -->
					<!--xsl:text>,</xsl:text-->
				</xsl:if>
	    </xsl:for-each>
			<xsl:text>&#xa;</xsl:text>
	  </xsl:for-each>
	</xsl:template>
</xsl:stylesheet>

Miguel61 · September 1, 2011, 4:11pm

This is the solution in the ASP code:


SELECT DATE_FORMAT(myDates, '%d/%m/%Y %H:%i:%s') myDates
         , REPLACE(NET,'.', ',') NET

Miguel61 · August 31, 2011, 7:13pm

I try this code:

                <xsl:variable name="columnName" select="@name"/>
                <xsl:variable name="myDates" select="@name"/>
		<xsl:variable name="Net" select="@name"/>

                <xsl:choose>
                   <xsl:when test="$row/@*[name()=$myDates]">
                      <xsl:value-of select="concat(substring($row/@*[name()=$myDates], 9,2), '/', substring($row/@*[name()=$myDates], 6,2), '/', substring($row/@*[name()=$myDates], 1,4), ' ', substring($row/@*[name()=$myDates], 12))" /> 
                   </xsl:when>
                   <xsl:when test="$row/@*[name()=$Net]">
                      <xsl:value-of select='format-number($row/@*[name()=$Net], "#.00")' />
                   </xsl:when>                
                </xsl:choose>

But I have this output:

myDates			Net
13/05/2011 21.50	//.00

Why the value field Net is //.00 ???

Mittineague · August 31, 2011, 8:13pm

They’ve got to be coming from the $myDates select no?

Is its syntax correct?
Maybe the XPATH needs tweaking?

Miguel61 · August 31, 2011, 8:24pm

Try this:

Output:

myDates			Net
13/05/2011 21.50	//.00

<?xml version="1.0" encoding="ISO-8859-1"?>
<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()">
  				    <!-- xls -->
					<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"/>			    
				<xsl:variable name="myDates" select="@name"/>
				<xsl:choose>
				<xsl:when test="($row/@*[name()=$myDates])">
                                <xsl:value-of select="concat(substring($row/@*[name()=$myDates], 9,2), '/', substring($row/@*[name()=$myDates], 6,2), '/', substring($row/@*[name()=$myDates], 1,4), ' ', substring($row/@*[name()=$myDates], 12))" /> 
				</xsl:when>
			        <xsl:otherwise>
				<xsl:value-of select="translate($row/@*[name()=$columnName],'/','')" />
				</xsl:otherwise>
                                </xsl:choose>
             
				
				
				<xsl:if test="position()!=last()">
				    <!-- xls -->
					<xsl:text>&#x09;</xsl:text>
				</xsl:if>
	    </xsl:for-each>
			<xsl:text>&#xa;</xsl:text>
	  </xsl:for-each>
	</xsl:template>
</xsl:stylesheet>

Topic		Replies	Views
Output 1.partly incorrect HTML & CSS xml	0	754	July 29, 2011
Transform XML in txt file with XSL file HTML & CSS xml	22	7546	March 11, 2011
Adding a date time stamp to an xml using xsl HTML & CSS xml	3	13820	October 8, 2014
Sorting by date in xsl file? HTML & CSS xml	1	1134	December 6, 2011
XSL Concatenate 3 values - having trouble! HTML & CSS xml	1	2562	December 28, 2010

XSL file formatting

Related topics