SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    XSL file formatting

    Hi there.

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

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


    Can you help me?
    Thanks in advance.
    Code:
    <?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>

  2. #2
    SitePoint Enthusiast
    Join Date
    Aug 2010
    Location
    Perundurai, Tamilnadu, India
    Posts
    72
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  3. #3
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Thanks for your answer, I appreciated much.

    This is the output in 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="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>
    
    		<z:row myDates="2011-08-22T03:03:52" Net=".00" />
    
    	</rs:data>
    </xml>
    This output XML is generate with ASP code and saved in the csv format:
    Code:
    <!-- #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 
     
     oXSL.Load Server.Mappath("Trasform_csv.xsl")  <<< 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:
    Code:
    <z:row myDates="13/05/2001 21:50:00" Net="0.00" />
    And in the CSV file:
    Code:
    13/05/2001 21:50:00, 0

  4. #4
    SitePoint Enthusiast
    Join Date
    Aug 2010
    Location
    Perundurai, Tamilnadu, India
    Posts
    72
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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))" />

  5. #5
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    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:
    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="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:
    Code:
    <?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="concat(substring($myDates, 6,2), '/', substring($myDates, 9,2), '/', substring($myDates, 1,4), ' ', substring($DataInterr, 12))" /> 
    				<!--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>

  6. #6
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    I try this code:
    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:
    Code:
    myDates			Net
    13/05/2011 21.50	//.00
    Why the value field `Net` is //.00 ???


  7. #7
    Programming Team silver trophybronze trophy
    Mittineague's Avatar
    Join Date
    Jul 2005
    Location
    West Springfield, Massachusetts
    Posts
    17,191
    Mentioned
    191 Post(s)
    Tagged
    2 Thread(s)
    They've got to be coming from the $myDates select no?

    Is its syntax correct?
    Maybe the XPATH needs tweaking?

  8. #8
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Try this:

    Output:
    Code:
    myDates			Net
    13/05/2011 21.50	//.00
    Code:
    <?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>

  9. #9
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    This is the solution in the ASP code:

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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •