Performing XSLT Transforms on XML Data Stored in SQL Server 2005
A common task when dealing with XML data is to apply an XSLT style-sheet to the raw XML data in order to display it better. In a previous post to this blog, I showed how to append a processing instruction to your XML data in order to get IE to do the transformation of your XML data. This approach required that you know the location of the XSLT transform file and also that you were looking at your data through a file and not extracted directly from the server. Using the integration of the CLR components with SQL Server 2005, we can perform this same transformation within the server process itself and output plain HTML that will be viewable by any browser.
Firstly, we need to write the code that will apply the XSLT transformation. Following is the code I used to accomplish this:
using System;
using System.IO;
using System.Xml;
using System.Xml.Xsl;
using System.Data.SqlTypes;
public class XSLTTransform
{
///
/// Performs XSLT transforms on XML data stored in SQL Server 2005
///
public static SqlXml Transform( SqlXml inputDataXML, SqlXml inputTransformXML )
{
MemoryStream memoryXml = new System.IO.MemoryStream();
XslCompiledTransform xslt = new XslCompiledTransform();
XmlReader output = null;
xslt.Load( inputTransformXML.CreateReader() );
// Output the newly constructed XML
XmlTextWriter outputWriter = new XmlTextWriter( memoryXml, System.Text.Encoding.Default );
xslt.Transform( inputDataXML.CreateReader(), null, outputWriter, null);
memoryXml.Seek( 0, System.IO.SeekOrigin.Begin );
output = new XmlTextReader( memoryXml );
return new SqlXml( output );
}
}
Notice the return type of this function, as well as the types of the various input parameters i.e. the type SqlXml. This type resides in the System.Data.SqlTypes namespace, which is contained in the System.data.dll assembly that ships with SQL Server 2005 and is used to represent data stored in the XML data type of SQL Server 2005 within the CLR. The two important actions that we need to accomplish with this type are to create a generic XmlReader instance (which is done by calling the CreateReader() method for this type) and to serialize a generic XmlReader instance back into a SqlXml instance (which is done using a constructor). The rest of this code is just generic code for performing XSLT transforms.
In order to compile this code into an acceptable CLR assembly, make sure you use the version of the CLR that shipped with the SQL Server 2005 version you have installed. Once compiled, you add the assembly into the SQL Server process space using the following DDL:
create assembly XsltTransform
from 'C:/XsltTransform.dll'
In this case, I assume that the assembly is stored in the XsltTransform DLL and that this DLL is present in the root directory on the C drive. Once you have created the assembly in the SQL Server 2005 process, you need to map the static function it contains into the SQL UDF. This is accomplished using the following DDL:
create function ApplyXsltTransform( @inputXML xml, @inputTransform xml )
returns xml
as external name XSLTTransform.XSLTTransform.Transform
In this case, we are creating a new SQL UDF which takes two, untyped XML data type instances as it's parameters and returns a further XML data type. You could also add a schema collection declaration to these parameters if you wished to enforce type checking on your input/output XML data. The 'external name' in this case is constructed using the following naming convention:
<assembly name>.<class name>.<static method name>
At this point, you are now ready to perform your XSLT transformation! As an example of how you might use this UDF, the following T-SQL statement with take the data stored in the XML file at CustomerData.xml and use the XSLT transform stored in the file CustomerDataDisplay.xsl to create an HTML version of this data:
declare @xml xml
declare @xslt xml
select @xml = BulkColumn from openrowset( Bulk 'C:\CustomerData.xml', SINGLE_BLOB ) as x
select @xslt = BulkColumn from openrowset( Bulk 'C:\CustomerDataDisplay.xsl', SINGLE_BLOB ) as x
select dbo.ApplyXsltTransform( @xml, @xslt )
Of course, you do not need to read your XML or XSLT data from files and can easily pass in any XML data type instance to this function in order to transform the XML data. Since the return type of this function is also an XML data type instance, it can be used to re-insert the transformed data, or even to define a computed column/view over your XML.