Sunday, February 26, 2012

Using XSLT and XPath to Transform XML

I remember that I did this kind transformation about 10 years ago, by using XSLT and XPath to transform an XML file to a HTML file. Last week I was working on tracing files generated by SQL Server Profiler tool. The tracing information can be saved as XML file as well. This XML file contains information in a structure like a database table: a long list of events, each event like a row of data with multiple columns.

What I was interested are columns of CPU, Reads, Writes and Duration. I need to convert those information into a HTML format so that I can copy and paste them to Excel. I could use PowerShell, Javascript, write .Net program to do the job, but I decided to recall my previous skills. This has been great experience for me. The following are the main steps.

XML File

The XML file is the source of data.  In my example case, I used SQL Server Profiler tool to get data communication from an application to a SQL server. All those information are recorded as data of events.

The SQL Server Profile provides a way to save the result to XML file:

I opened the XML file in notepad. The columns of CPU, Reads, Writes, and Durations are in a node of Event with attribute name="SQL:BatchCompleted". The path of Event is TracedData\Events\Event. Under the Event node, there is collection of Column nodes.  Each Column has an attribute name as its identity, and my interested Column nodes are name="CPU", "Reads", "Writes" and "Durations".

Using XSLT and XPath

Last time I learned XLST and XPath were at W3Schools.  The information are still there. XLST is the basic tool to do a transformation. Within the XLST, XPath is used to search for or match XML nodes.

My purpose is to extract some XML nodes and present their values into a HTML table format. This is my XSL file:

<?xml version="1.0" encoding="ISO-8859-1"?>
<xsl:stylesheet version="1.0"

<xsl:template match="/">
    <form method="post" action="edittool.asp">
    <h2>SQL Server Profiler Result</h2>
    <table border="1">
      <tr bgcolor="#9acd32">
      <xsl:for-each select="TraceData/Events/Event[@name='SQL:BatchCompleted']">
          <xsl:value-of select="Column[@name='CPU']"/>
          <xsl:value-of select="Column[@name='Reads']"/>
          <xsl:value-of select="Column[@name='Duration']"/>
          <xsl:value-of select="Column[@name='Writes']"/>

To get all the nodes of Event with attribute name="SQL:BatchCompleted", I use XSL:for-each select syntax to loop the collection. Then I use XSL:value-of to get Column node's value. To specify CPU Column, I used XPath Predicate [@attributename...] syntax. For example, Column[@name='CPU'] is to specify Column node with attribute name with 'CPU' value.

Using Browser to View Results

What I like to do the transformation is simply using a browser, dragging XML file to a browser to display results as HTML content. I don't like to put XML and XSLT to a web server to get the same result.

Add a line to XML to specify XLST transformation at the beginning:

<?xml version="1.0" encoding="utf-16"?>
<?xml-stylesheet type="text/xsl" href="cdcatalog.xsl"?>

This line will add a link of an XSL style sheet to an XML document.

To my surprise, I saw no result in Chrome. The content was blank! By enabling Inspect Element form browser's context menu, I found that actually this was a security issue:

Soon I found that this could be resolved by disabling web-security. I created a shortcut of Chrome and added command line argument of -disable-web-security.

You have to close all the current Chrome instances before launching this one. With this command option, my XML was then transformed and displayed in Chrome browser.

10 years ago, I was using IE to display the transformation. IE still works fine today. I think this makes sense. The URL of the XML file is actually local file: file:///E:.... The link in XML to a XSL file should be able accessible locally in browser.

XML NotePad Tool

I have a favorite tool for viewing XML file: XML Notepad. During the time I was using browser to view transformed XML file, I found that XML Notepad has a build in function to do that.

In the second tab, XML Output, I realized that it let use to choose a XSL file to transform the XML file.

The another advantage is that there is no need to embed a link to XSL file.



Friday, February 17, 2012

Some Views Cannot be Matertialized

In the past weeks, I was asked to work on improving SQL server performance for a client application.  Basically, the application have some SQL template settings for retrieving data from SQL server databases.  I did this kind job last year. What I did was using materialized views, i.e., views cache static data if they are not changed.

However, this time I could not make my updated views as materialized ones. After further analysis, I realize that this is not SQL's fault. If a view has some dynamic components, such as columns, the view just cannot cache data. In this sense, the view cannot be materialized.

For the following SQL SELECT is one example:

SELECT ID, CAST(@StartDate + ' ' + @StartTime AS DATETIME) AS DT, VALUE
FROM MyTable;

Since the column DT is dynamically built by two variables, SQL server just cannot cache data.

My assignment actually is much more complicated than this case. The strategy I worked out was to stand back first. I tried to understand the application SQL templates. What kind of data are queried? Based on my understanding, I tried to create a set of database tables to meet the requirement.  Then I added triggers to source data tables to populate data from the source to the new tables. As a result, the views as SQL templates were much easy to build.

What I learned from this project is that you cannot always implement one strategy to solve all the problems, even though the implementation strategy has been successfully used before. If it is too hard to use the same strategy, you have to stand back, re-think the issue in a wide prospective view, and try to take alternative path. This may actually lead to find another good solution.


Sunday, February 12, 2012

Web service: Wolfram|Alpha

I was suppressed to know the web service of Wolfram|Alpha, a computable knowledge engine. I knew this one by aan article of AppleInsider about 1/4 Siri searching result from Wolfram|Alpha.

Unlike the search by Google, the results are collection of web pages or resources based on a query's relevance or top hits. The inventor Stephen Wolfram said that Wolfram|Alpha is not a search engine. Instead, it is a computable knowledge engine which provides results from its back-end computer system, Methematica, with curated data.

After the initial discovery, I was fascinated with this idea of combining math, computer program, data and systematic experiments. For me this is a new idea, but Stephen has spent his 30 year research on this.  At the time I found this service, Wolfram|Alpha was in the trial stage and soon two days ago the Pro is out.

Based on the information, the structure of this web service is composed of four basic blocks:

Since the results are computed and presented as a list of graphics, tables, and explanations, there are no source information. In this sense, this web service is not a replacement for Google server, but a quite nice complement.  I have tried to search for some code solutions but with no actually any results. I hope the future version may provide nice solutions as well.



Saturday, February 04, 2012

How to create a login page using ASP.NET MVC 3 Razor

I am back to MVC project. I used MVC for web application long time ago when it was still in Microsoft Patterns & Practice development. Now it is part of .Net 3.5 and 4.0 framework and all the classes and templates are available in Visual Studio 2011.

I need to add a Login page to my project. The way I did in my ASP.Net is quite different from the way in MVC. Since MVC has been changed a lot in terms of structure and framework, I have to learn MVC again. The MVC templates created by Visual Studio 2010 do not have Login feature. Fortunately, I found this tutorial on Youtube in short time:

The tutorial example is what I want.  It seems very easy to add Login page to MVC project.

First, add restriction in web.config file with Location element:

   <location path="">
            <deny users="?"/>

The above will disable access to all the pages in the site.  Then add authentication element for login page in web.config:

<authentication mode="Forms">
  <forms loginUrl="~/Login/Login.aspx" timeout="120" />

In order to ensure the authentication in other web pages, a cooky is added before redirect to other forms on server side, as an indication been authenticated (part 3):

FormsAuthendication.SetAuthCookies(key, persistent);

In the place where log out is implemented, the following code is used to clear the cookie (part 4):


That's all the basics.

In addition to those steps, the tutorial explains master page in MVC, which is in the Views->Shared folder. Different master pages can be specified (part 4). One interesting thing in MVC is that cs and html are all in one source code file as cshtml extension.

The tutorial also explains some very basic concepts in MVC:
  • Router structure in Global.assax (part 1)
  • Model for a view (part 5)
  • Get and Post actions mapping to View's methods