A generic stylesheet for SQL Server XML queries using XMLDATA

  • Article

In a recent newsgroup posting, Jake Segers asked how to display the results of FOR XML queries generically when the schema is inlined using the XMLDATA option. The XMLDATA option inlines an XDR schema within the result.  He wanted null values in SQL Server, which are represented in XML as the lack of the node's presense, to be represented within an HTML table as an empty cell. Jake's solution used RAW mode for the XML query, so we could guarantee the structure (AUTO mode queries define the structure according to the joined tables in the order they appear in the SELECT list).  He was unable to alter the solution to use EXPLICIT mode queries, so we turned to an XSLT solution to accomodate.

The problem is two-fold. First, you need to use the schema as the basis of what to display, not the data that is present (the data may not be there). Simply setting up match patterns for the attributes in the row elements would not work, as the missing attributes are not accomodated.  Second, the actual namespace may change in subsequent FOR XML queries. This is problematic, since you cannot reliably look for nodes within a given namespace, you must treat the nodes as if the namespace is irrelevant.

Here is what I came up with.

<xsl:stylesheet version ="1.0" exclude-result-prefixes ="data dt" xmlns:xsl="https://www.w3.org/1999/XSL/Transform" xmlns:data="urn:schemas-microsoft-com:xml-data"
xmlns:dt="urn:schemas-microsoft-com:datatypes">

<xsl:output method ="html" />

<xsl:template match =" / " >

<html>

<head/>

<body>

<table>

<xsl:apply-templates select =" /*/data:Schema" />

<xsl:apply-templates select =" /*/*[name()='row'] " />

</table>

</body>

</html>

</xsl:template>

<xsl:template match =" /*/*[name()='row'] " >

<!--

 Variable is used to lookup the actual column names in the
     XDR Schema attributes section

-->

<xsl:variable name ="curNode" select ="current() " />

<tr>

<xsl:for-each select =" /*/data:Schema/data:ElementType/data:attribute" >

<xsl:variable name ="typeName" select =" @type" />

<td>

<xsl:value-of select =" $curNode/attribute::*[local-name() = $typeName] " />

</td>

</xsl:for-each>

</tr>

</xsl:template>

<xsl:template match ="data:Schema" >

<tr>

<xsl:apply-templates select ="data:ElementType/data:attribute" />

</tr>

</xsl:template>

<xsl:template match ="data:attribute" >

<th>

<xsl:value-of select =" @type" />

</th>

</xsl:template>

</xsl:stylesheet>