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.
<?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:
-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.
References
- W3Schools: XSLT Transformation
- Peter Beverloo blog: List of Chromium Command Line Switches
- Microsoft: XML Notepad Download
I was suppressed to know the web service of Woflram|Alpha, a computable knowledge engine. I knew this one by aan article of AppleInsider about 1/4 Siri searching result from Wolfram|Alpha.
Posts
