In-process XSLT transformations
So it only makes sense that if you have XML in the database that evetually you're going to want to do something with it. The combination of XML Indexing and XQuery give us efficient albeit limited transformational functionality in such cases. But what would you do if you wanted to use XSL/T stylesheet instead. SQL Server 2005 doesn't offer an out of the way to do that with T-SQL, but as I say in my talks about SQLCLR, its a reasonable way to get the job done. The first thing you'd do is cruft up a public static method around System.Xml.Xsl.CompiledTransform like this:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Xml;
using System.Xml.Xsl;
using System.IO;
namespace DM.Examples
{
public partial class XmlLibrary
{
[SqlFunction(DataAccess = DataAccessKind.None, IsDeterministic = false, IsPrecise = false, SystemDataAccess = SystemDataAccessKind.None)]
[return: SqlFacet(IsFixedLength = false, IsNullable = true, MaxSize = -1)]
public static SqlXml ApplyTransform(SqlXml Data, SqlXml StyleSheet)
{
// on null return null, just in case.
if (Data.IsNull || StyleSheet.IsNull)
return SqlXml.Null;
// Buffer the transformed xml
MemoryStream ms = new MemoryStream();
XmlWriter xw = XmlWriter.Create(ms);
// Load and transform
XslCompiledTransform ctx = new XslCompiledTransform(false);
ctx.Load(StyleSheet.CreateReader());
ctx.Transform(Data.CreateReader(), xw);
// return the result, assuming XML compliant output
return new SqlXml(ms);
}
}
}
Not really much exiciting there other than the use of the SqlXml type's abilty to create an XmlReader. Once you've deployed that using Visual Studio (or using good old CREATE ASSEMBLY and CREATE FUNCTION), its not very hard to use in a Query:
-- here we load the XSLT into a table to make easier to reuse in future queries. Do this once.
-- storing as BLOB as its just a stylesheet. No need to query, so no need for XML type here.
if (select count(*) from sys.tables where name = 'xslTransforms') = 0
create table dbo.xslTransforms (transformID tinyint identity(1,1) primary key,descr nvarchar(255),stylesheet varbinary(max))
go
truncate table dbo.xslTransforms
insert into dbo.xslTransforms
select 'AdventureWorks Product Table',bulkcolumn from openrowset(bulk 'c:\simple.xslt',single_blob) as p
go
-- Load the style sheet and feed it some data.
declare @x xml
select @x = (select productID as '@dbid',ProductNumber as '@productID',Name as 'name',Color as 'color',ListPrice as 'listPrice',Size as 'Size',SizeUnitMeasureCode as 'sizeCode',style as 'style' from adventureworks.production.product where not(coalesce(discontinuedDate,'2999-12-31') = 1) and FinishedGoodsFlag = 1 for xml path('product'),root('products'),elements xsinil,type)
select dbo.ApplyTransform(@x,(select cast(stylesheet as xml) from dbo.xslTransforms where transformID=1))