This article is part one of a two part series to try and show some of the versatility that CLR provides us. Part one will focus on creating a user-defined CLR function to use a XSLT file to transform a XML string and e-mail it using sp_send_dbMail. Part two will focus on using our new function in real world applications.
XML (Extensible Markup Language) is a great way to share information. We can take our T-SQL queries and have the output formatted in XML, using statements like for xml auto and for xml explicit. However, it is not particularly easy to read. To display XML in a user friendly manner we can use a XSLT (Extensible Stylesheet Language Transformations) file. We are going to create a user-defined function that takes a XML string and the path of a XSLT file and returns the translated XHTML. We will then use the stored procedure, sp_send_dbMail to e-mail the file to ourselves. There is a link at the end of this article that shows you how to setup Database Mail.
The CLR:
We start by creating a new SQL-CLR C# Project in Visual Studio. Next we add a new User-Defined Function and call it fctXMLTransfer. Then we copy the following code into our file.
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.IO;
using System.Text;
using System.Xml;
using System.Xml.Xsl;
using Microsoft.SqlServer.Server;
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString fctXMLTransfer(string sXml, string sXsltPath)
{
XmlDocument xd = new XmlDocument();
XslCompiledTransform xs = new XslCompiledTransform();
xd.LoadXml(sXml);
xs.Load(sXsltPath);
StringBuilder sb = new StringBuilder();
TextWriter tw = new StringWriter(sb);
xs.Transform(xd, null, tw);
return new SqlString(sb.ToString());
}
};
This code is pretty simple and straight forward. We take our XML string and load it into an XmlDocument. We then load the XSLT file from the path that was sent in and perform the transformation, writing it out with the text writer to the string builder. Returning the newly created XHTML text (neither the string nor file is modified).
Since we are accessing a file, we need to go to the project’s properties page, the Database tab and set the permission level to external. We might also need to run
alter database Your_DB set trustworthy on
Deploying the project will setup the necessary assemblies in our database, which moves use to the XML and XSLT.
The XML and XSLT:
Since part one is mainly about setting up our tools, we will not use elaborate XML or XSLT examples. Instead we will take the common example of an XML string containing the first and last name of individuals and a XSLT file that displays them, sorted by first name.
Our XML string:
declare @xml varchar(500)
select @xml = '<?xml version="1.0" ?>
<People>
<individual>
<firstName>John</firstName>
<lastName>Doe</lastName>
</individual>
<individual>
<firstName>Jane</firstName>
<lastName>Doe</lastName>
</individual>
</People>'
Our XSLT file is as follows:
<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns="http://www.w3.org/1999/xhtml">
<xsl:output method="html"/>
<xsl:template match="/People">
<html>
<head>
<title>
XML and XSLT Example with CLR
</title>
</head>
<body>
<span style="font-size: large">
People
</span>
<br />
<ul>
<xsl:apply-templates select="individual">
<xsl:sort select="firstName" />
</xsl:apply-templates>
</ul>
</body>
</html>
</xsl:template>
<xsl:template match="individual">
<li>
<xsl:value-of select="firstName"/>
<xsl:text> </xsl:text>
<xsl:value-of select="lastName"/>
</li>
</xsl:template>
</xsl:stylesheet>
Save the XSLT file (.xsl) to your local drive, as our XslCompiledTransform variable expects a path. Once the file has been saved we are ready to test.
The T-SQL:
-- Declare our variables
declare
@xml varchar(1000)
,@eMail varchar(max)
,@eMailAddress varchar(100)
-- Set our variables
select @xml = '<?xml version="1.0" ?>
<People>
<individual>
<firstName>John</firstName>
<lastName>Doe</lastName>
</individual>
<individual>
<firstName>Jane</firstName>
<lastName>Doe</lastName>
</individual>
</People>'
, @eMailAddress = 'recipient@myrecipient.com'
-- Call our function to transform the XML to XHTML, sending in the XML string and XSLT file path
select @eMail = dbo.fctXMLTransfer(@xml,'C:\XSLT.xsl')
-- e-Mail the XHTML returned from our function to somebody
exec msdb..sp_send_dbmail 'Test_Mail', @body = @eMail, @recipients = @eMailAddress, @body_format = 'html'
There we have it, a simple user-defined CLR function that can take useful data and display it in a meaning manner. In part two, we will take the XML output of actual queries and transform them into meaningful reports that management can use.
An article on setting up Database Mail