In my previous blog, I described how I used XSLT transformation to convert XML content to HTML content. That one is a very simple example. This technique is not very commonly used but I think it is very useful. As I mentioned that I used the same technique more than 10 years ago. Here I add more on this issue for my personal references.
The previous example is based on an XML file from my SQL Server Profiler tracing result: I want to convert all the interested Column nodes under Event into a HTML table. Last week, I realized that all those events can be further divided into three SQL groups based on SQL calls: Current, Data and TagInfo. It would be nice if I could create three HTML tables to summarize those results. I spent some time to explore more on XSLT transformation. The following are some related transformation elements I used.
XSL Variables
In XSL, variables can be defined for reuse. I need three HTML tables. Each has the same headers and footers. I found that by defining variables, it is a very neat strategy to create my HTML tables with constant herder and footer.
<xslvariable name="header">
<tr bgcolor="#9acd32">
<th>Row index</th>
<th>CPU</th>
<th>Reads</th>
<th>Duration</th>
<th>Writes</th>
</tr>
</xslvariable>
<xslvariable name="footer">
<tr bgcolor="#9acd32">
<th>Total</th>
<th></th>
<th></th>
<th></th>
<th></th>
</tr>
</xslvariable>
Here are variables: header and footer, each corresponding to HTML table header and footer sections.
In the next section, you will see how those variables are used.
One limitation of XSLT variables is that they are immutable. That means you cannot change value after a variable is defined or created. I would like to have some variables to sum all CPU, Reads, Duration values, but I could not figure out how. My solution is to copy my HMLT table results and paste them to Excel to do the calculation.
XSLT If Element
I used XSLT
If element as a predicate for my HMTL table content:
<table border="1">
<xslcopy-of select="$header"></xslcopy-of>
<xslfor-each select="TraceData/Events/Event[@name='SQL:BatchCompleted']">
<xslif test="starts-with(Column[@name='TextData'], 'EXEC sp_PVSQL_Template_MANUAL_Data')">
<tr>
<td>
<xslvalue-of select="position()" />
</td>
<td>
<xslvalue-of select="Column[@name='CPU']" />
</td>
<td>
<xslvalue-of select="Column[@name='Reads']" />
</td>
<td>
<xslvalue-of select="Column[@name='Duration']" />
</td>
<td>
<xslvalue-of select="Column[@name='Writes']" />
</td>
</tr>
</xslif>
</xslfor-each>
<xslcopy-of select="$footer"></xslcopy-of>
</table>
The above codes also show the usage of my XSLT variables for my HTML header and footer sections.
XSLT Functions
In above codes, I used an XSLT function in XSLT
If test:
starts_with(a, b). This is a useful string function to check if string
a starts with
b string, case sensitive. The function of
position() is used to get XML Row index, which is a reference node position in the original XML content.
However, the parameters of the function have to be in the correct case, case sensitive. I tried to use
lower_case() and
match() function. Unfortunately, those functions are only available in XSLT 2.0. The tool of XML Notepad does not support XSLT 2.0, nor I can find updates for the tool.
References
Read More...
Collapse...
Posts
