IntroLast week I published an article about the mdq.XmlTransform Scalar CLR function that ships with SQL Server Master Data Services. In this post I will provide instructions for setting it up. The process is fast and painless (less than one minute provided you have the necessary credentials). If you are new to CLRs or XML transforms I suggest testing this on your own PC or in a Sandbox environment. This post won’t be very technical but you should have a basic understanding of SQL Server functions, the XML data type and Common Language Runtime (CLR) functions.
Setting up mdq.XmlTransform
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.
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!
Last Updated: 11/13/2013 (added example)