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"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

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

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.

References


Posted by D Chu at 2:14 PM

Labels: ,

0 comments:

Post a Comment

Subscribe to: Post Comments (Atom)