Transform XML in SQL Server 2005

In my current project one of the systems is used to manage files. The system is able to handle several different file types. Each file type has its own set of meta data. The meta data describes particular characteristics of each file and is stored as XML in the database. Example of meta data is File Type, Tool, Version and Release Information.

The customer wants to generate a description about the file from the meta data. Each file type has it own set of rules on how to generate the description. For example, the description for files of type Misc shall include File Type, Tool and Version. It shall be easy to change the content of a file description and not require a new release of the system.

An appealing solution is to use XSLT to transform the XML based meta data to a description. By storing the XSLT for each file type in the database, it would be easy to change file description during runtime. Since most information aggregation and querying happens in the database, it would be beneficially if we were able to generate the description in the database and use it different views. So how do we transform XML with XSLT in SQL Server 2005?

To solve this problem, I had to create utilities functions in C#, register the assembly in SQL server and to create a function that map one of the utility functions to SQL.

I created two utility functions in C#. One general purpose function used to transform XML with XSLT and one function that specifically is used to generate a file description.

The transform function takes a XML document and a XSLT style sheet and returns the resulting XML data from applying the style sheet on the document.

public static SqlXml Transform(SqlXml xmlData, SqlXml xsltData)

{

//Initialize

MemoryStream memoryXml = new System.IO.MemoryStream();

XslCompiledTransform xslt = new XslCompiledTransform();

// Load XSL transformation

xslt.Load(xsltData.CreateReader());

//Transform

System.IO.MemoryStream ms = new System.IO.MemoryStream();

xslt.Transform(xmlData.CreateReader(), null, ms);

ms.Seek(0, System.IO.SeekOrigin.Begin);

//Return the transformed value

SqlXml retSqlXml = new SqlXml(ms);

string tmp = retSqlXml.Value;

return (retSqlXml);

}

Since I wanted a string, I also created a specific function returning the description from a XML document. This function assumes that the file description is stored in tag called Description. It requires the XML document and XSLT style sheet, and returns the generated file description.

public static string GenerateDescription(string xmlData, string xsltData)

{

string description = “”;

//If missing input, return an empty string

if(string.IsNullOrEmpty(xmlData) | string.IsNullOrEmpty(xsltData))

{

return “”;

}

try

{

//Initialize

StringReader stringXmlReader = new StringReader(xmlData);

XmlReader xmlReader = new XmlTextReader(stringXmlReader);

SqlXml sqlXmlData = new SqlXml(xmlReader);

StringReader stringXsltReader = new StringReader(xsltData);

XmlReader xsltReader = new XmlTextReader(stringXsltReader);

SqlXml sqlXsltData = new SqlXml(xsltReader);

//Transform

SqlXml result = Transform(sqlXmlData, sqlXsltData);

//Get description

XmlDocument xmlDocument = new XmlDocument();

xmlDocument.LoadXml(result.Value);

description =xmlDocument.SelectSingleNode(“Description”).InnerText;

}

catch (Exception e)

{

//TODO: Determine error handling

description = “Error when generating description!”;

}

return description;

}

The assembly can be registered from SQL Server Management Studio Express or by a script. I used the following script to register my assembly.

CREATE ASSEMBLY [Company.CSSS.SqlServer]

AUTHORIZATION [dbo]

FROM ‘C:\Program Files\Company IT\Central System Supporting System\Company.CSSS.SqlServer.dll’

WITH PERMISSION_SET = SAFE

GO

Thereafter you need to enable the CLR. This can also be made from a script.

EXEC sp_CONFIGURE ‘show advanced options’ , ‘1’;

GO

RECONFIGURE;

GO

EXEC sp_CONFIGURE ‘clr enabled’ , ‘1’

GO

RECONFIGURE;

GO

The script that creates the function that maps the utility function to SQL looks like:

CREATE FUNCTION [dbo].[GetFileDescription](@xmlData [nvarchar](4000), @xsltData [nvarchar](4000))

RETURNS [nvarchar](4000) WITH EXECUTE AS CALLER

AS

EXTERNAL NAME [Company.CSSS.SqlServer].[Company.CSSS.SqlServer.XsltTransformer].[GenerateDescription]

GO

Peter

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.