Wednesday, March 23, 2011

xslt simple tutorial

In order to illustrate how XSLT can work on XML, follow the example below.

  1. Create one file called "test.xml" in notepad and paste the content below.
    2. <?xml version="1.0" encoding="ISO-8859-1"?> <catalog>   <cd>     <title>Empire Burlesque</title>     <artist>Bob Dylan</artist>     <country>USA</country>     <company>Columbia</company>     <price>10.90</price>     <year>1985</year>   </cd> </catalog>
  2. Create a file called "display.xsl"

    3. <?xml version="1.0" encoding="ISO-8859-1"?> <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"> <xsl:template match="/">   <html>   <body>   <h2>My CD Collection</h2>   <table border="1">     <tr bgcolor="#9acd32">       <th>Title</th>       <th>Artist</th>     </tr>     <xsl:for-each select="catalog/cd">     <tr>       <td><xsl:value-of select="title"/></td>       <td><xsl:value-of select="artist"/></td>     </tr>     </xsl:for-each>   </table>   </body>   </html> </xsl:template> </xsl:stylesheet>

  3. Try to open test.xml and you will get the normal xml display
  4. Try to add following line into your xml

    5. <?xml version="1.0" encoding="ISO-8859-1"?> <?xml-stylesheet type="text/xsl" href="display.xsl"?> <catalog>   <cd>     <title>Empire Burlesque</title>     <artist>Bob Dylan

  5. Try to double click on your xml file again and its been transform
Posted by at No comments:

Tuesday, March 22, 2011

Progressive sum

I have been struggling with progressive sum after reading some other people code who is using cursor to achieve this.

Before I provide the code, let me explain what is progressive sum. Progressive sum is usually used by accounting where you need to add or minus the value base on row level rather than give one value of sum. Below would be the example

IDinsertDatevalueProgressive_sum
12011-03-23 12:39:58.3172525
22011-03-23 12:39:58.3201540
32011-03-23 12:39:58.320-1525

As you can see on the row 1, we insert a value 25 and the progressive sum is 24. Once we insert value 15, the system automatically added 25+15 = 40.

Below would be the sample code that I use to produce the result above

create table #tmpAccounting
(
  ID int identity,
  insertDate datetime default getdate(),
  value float
)
insert into #tmpAccounting (value) values (25)
insert into #tmpAccounting (value) values (15)
insert into #tmpAccounting (value) values (-15)

select *, (select sum(value) from #tmpAccounting where insertDate<=a.insertDate and ID<=a.ID) as Progressive_Sum  from #tmpAccounting a order by ID

drop table #tmpAccounting
Posted by at No comments:
Labels:
Subscribe to: Comments (Atom)