Many XSLT enthusiasts have been waiting years to show people what you can do in Microsoft Land (.NET, SQL Server, etc) with XSLT 2.0. MSXML still only supports XSLT 1.0. I went to MSDN's Frequently Asked Questions about XSLT page today for an update...
The end.
--ab
Last Updated: 4/27/2013 (Posted)
mdq.XmlTransform will work on any version of SQL Server that supports CLRs. This means it can run on SQL Server 2005 through 2012 (Developer, Express, Enterprise, etc). Setup involves four simple steps: (1) creating the mdq schema, (2) enabling CLR integration, (3) creating the assembly, and then (4) creating mdq.XmlTransform. You can copy/paste the code below into SSMS except for the code in step 2; for that that I provided a link to where you can get the code.
1. Create the mdq schema
See this article for more details about creating schemas. The schema name does not need to be named "mdq" for this function to work (I am using "mdq" because everyone else does).
2. Enable CLR integration
To create and execute CLR functions you need CLR integration enabled. You can enable CLR integration by executing the following code:
See this article for more information about CLR integration.
3. Create the MDQ assembly
The code to create the assembly (Microsoft.MasterDataServices.DataQuality) looks like this (truncated for readability):
For the complete version of the required code: Steps to create [Microsoft.MasterDataServices.DataQuality]
4. Create the CLR Function
mdq.XmlTransform is a Scalar CLR. To create it run the following code:
If you have completed steps 1 through 4 without any errors then you have successfully installed mdq.XmlTransform.
mdq.XmlTransform takes two parameters: @xml and @xslt. The data type for both is XML. The first parameter, @xml can be an XML document, an XML fragment or an atomic value such as a string, date or any type of number. The second parameter, @xslt, must be an XML Transform (AKA XSLT stylesheet) and it must be version 1.0. The @xml parameter is what you want to transform, @xslt is how you want to transform it. XSLT is great for stuffing and splitting strings which means that the both the XML input and XSLT output can be a delimited sequence.
For testing I created a query that uses mdq.XmlTransform to calculate a factorial. @xml will be the number we want to calculate, @xslt is that code that will perform the calculation. To keep things simple I am not discussing performance tuning. The query below can be optimized in several ways but, for now, we just want to make sure that the CLR is working. I will address performance in future posts. mdq.XmlTransform will take the apply @xslt to @xml and return the result.
/************************************************************
Created by: Alan Burstein
Created on: 4/22/2013
How it works:
@xml can be xml, an xml fragment or an
atomic value such
as a string, number or a
delimited sequence.
@xslt needs
to be a well-formed XML style sheet (XSLT).
mdq.XmlTransform will take the apply @xslt to @xml
and return the result.
This will work for numbers up to 170
************************************************************/
DECLARE @xml xml='17'
DECLARE @xslt
xml='
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:template match="/"
name="factorial">
<xsl:param
name="number" select="text()"/>
<xsl:choose>
<xsl:when test="$number <= 1">1</xsl:when>
<xsl:otherwise>
<xsl:variable
name="recursive_result">
<xsl:call-template
name="factorial">
<xsl:with-param
name="number" select="$number - 1"/>
</xsl:call-template>
</xsl:variable>
<xsl:value-of
select="$number * $recursive_result"/>
</xsl:otherwise>
</xsl:choose>
</xsl:template>
</xsl:stylesheet>'
SELECT mdq.XmlTransform(@xml,@xslt)
AS factorial
In this post we reviewed the steps for setting up and using mdq.XmlTransform. Come back soon for more examples of what you can do with this bad dog. Thanks for reading!
--ab
Last Updated: 11/13/2013 (added example)
In my previous post I raised the question of a possible missing CLR on MSDN's Master Data Services CLR Functions page. It turns out that there was a missing CLR (there are actually quite a few new CLRs not listed but this is a topic for another time). The missing guy I'm talking about is, IMHO, the baddest CLR ever. Before I talk about this nasty new CLR let me, first, say a couple things about functional programming; it will matter in a moment. I promise.
Functional Programming (FP) is very power full and elegant stuff. At Wikipedia, functional programming is defined as follows (emphasis mine):
In computer science, functional programming is a programming paradigm that treats computation as the evaluation of mathematical functions and avoids state and mutable data. It emphasizes the application of functions, in contrast to the imperative programming style, which emphasizes changes in state.[1] Functional programming has its roots in lambda calculus, a formal system developed in the 1930s to investigate computability, the Entscheidungsproblem, function definition, function application, and recursion. Many functional programming languages can be viewed as elaborations on the lambda calculus.
In the MSDN article I posted earlier today: Functional Programming vs. Imperative Programming FP is described a little differently (emphasis mine):
The functional programming paradigm was explicitly created to support a pure functional approach to problem solving. Functional programming is a form of declarative programming.
The article continues by discussing Functional Programming Using XSLT.
Many XSLT developers are familiar with the pure functional approach. The most effective way to develop an XSLT style sheet is to treat each template as an isolated, composable transformation.The order of execution is completely de-emphasized. XSLT does not allow side effects (with the exception that escaping mechanisms for executing procedural code can introduce side effects that result in functional impurity).
There are some that still argue that XSLT is not a functional programming language. I disagree. In a November, 2001 article by Dimitre Novatchev titled, The Functional Programming Language XSLT - A proof through examples Novatchev demonstrates beyond any doubt that XSLT is a functional programming language by implementing many major functional programming design patterns. There were 35 functions in total that illustrated the usefulness of higher order functions using XSLT 1.0.
So, why all the blather about functional programming? Because the mysterious magical missing CLR is mdq.XmlTransform, you can read more about it here. Why is this thing "the baddest CLR ever?" mdq.XmlTransform is the baddest CLR ever because it does not just solve a specific problem, it extends your SQL server instance to support a whole new, super-powerful functional programming language. Furthermore, it does so without requiring you to compile any new code for each new function. Now you can implement pure functions in SQL server: functions where tasks can be executed asynchronously and where the order of execution can be completely de-emphasized. All this using a well-tested, reliable and proven open-source language which has been used with great success since last century. Not too Kludgy, eh?
The other bonus is that this bad dog was developed, tested, QA’d and deployed by Microsoft (so you know it's good!). It's so good in fact that, beginning with SQL Server 2008 R2, it ships with Microsoft's new EIM software: Master Data Services and Data Quality Services. This CLR is solid, trustworthy and does more for your SQL server instance than any CLR I have ever seen.
Come back soon for some examples of what you can do with this guy and thanks for reading.
Click to enlarge:
--ab
Last Updated: 4/19/2013 (code cleanup)
Below is a great article about functional programming using Microsoft Products. I don't agree with the endorsement of F# or LINQ over XSLT. It is worth noting, however, that F# and LINQ are Microsoft products whereas XSLT is opensource, not married to any platform and is older and more tested than F# and LINQ combined. I would not want people using XSLT if I were Microsoft either. That said, I am extremely pleased to read about Microsoft's endorsement of functional programming! You will be too when I get back to you about that missing CLR. ;)
Functional Programming vs. Imperative Programming (MSDN 2013)
--ab
Last Updated: 5/1/2013 (formatting)
Master Data Services ships with some nice, new common language runtime functions; click this link for more details. My question is this: take a look at the screenshot below; did MDS ship with only 11 CLRs? ...or did the folks at Microsoft forget to include one when they prepared this list?
Click to zoom:
Check back tomorrow.
--ab
Last Updated: 4/19/2013 (code cleanup)
That's right... according to the W3C XSLT [3.0] according to the working draft, will be a fully-fledged functional programming language. I included a link and here's the text (emph mine):
...XSLT 3.0 also delivers enhancements made to the XPath language and to the standard function library, including the following:
--ab
HTML code used to generate this article:
<h2>XSLT is a functional programming language</h2>
<p>That's right... according to the <a href="http://www.w3.org/">W3C</a> <i>XSLT [3.0] according to the working draft, will be a fully-fledged functional programming language</i>. I included a link and here's the text (emph mine): </p>
<div style="width: 550px; border: solid 1px lightgray; margin-left:20px; font-style:italic">
<p style="margin-left:20px;margin-right:30px;">...XSLT 3.0 also delivers enhancements made to the XPath language and to the standard function library, including the following:</p>
<ul>
<li>Variables can now be bound in XPath using the <b>let</b> expression.</li>
<li>Functions are now first class values, and can be passed as arguments to other (higher-order) functions, <b>making XSLT a fully-fledged functional programming language</b>.</li>
</ul>
</div>
<br/>
<p><b><i>--ab</i></b></p>
<img src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjChGlnlBCHCS920uS2-eMvBzcGdrpC731KZzC26SBWM4yjOy9T7sduli0o3dcl8CfRm9KyRrO4c-abotmSVmna4YdZN5lj8z6CLSHSRmGvB8DN98Pduj61oE0Oa4xzMbLL1QfgZIkOcAs/s251/ninja.jpg"
alt="The new xmlsqlninja logo ('X-M-L'-'See-Quel' 'ninja')"
title="The new xmlsqlninja logo ('X-M-L'-'See-Quel' 'ninja')"
style="height: 100px;" />
<br/><br/>
<h2>Further Reading:</h2>
<ul style="margin-left: 10px;">
<li><a href="http://www.w3.org/TR/xslt-30/" style="color:#0040FF;">XSL Transformations (XSLT) Version 3.0</a><i>(W3C)</i></li>
<li><a href="http://archive.xmlprague.cz/2012/presentations/Whats_new_3.0_XPath_XSLT_XSD_1_1.pdf" style="color:#0040FF;">What's new in 3.0 </a> <i>(Michael Kay)</i></li>
</ul>
<br/>
<p style="margin-bottom:0px;">HTML code used to generate this article:</p>
<style>
div.WordSection1 {border: solid grey 1px; padding: 10px; height: 100px; width:540px; overflow:auto; background-color: #E6E6E6;}
</style>
<div class="WordSection1">λ</div>
<br/>
<p><b>Last Updated:</b> 4/21/2013 (code cleanup, added html code)</p>
Last Updated: 4/21/2013 (code cleanup, added html code)
