Setting up mdq.XmlTransform

Intro

Last 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:

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO

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):

CREATE ASSEMBLY [Microsoft.MasterDataServices.DataQuality]
AUTHORIZATION dbo
FROM 0x4D5A90000300000004000000FFFF0000B80000000000000040000000....
WITH PERMISSION_SET = SAFE
GO

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:

CREATE FUNCTION mdq.[XmlTransform](@xml XML, @xslt XML)
RETURNS NVARCHAR(MAX) WITH EXECUTE AS CALLER, RETURNS NULL ON NULL INPUT
AS
EXTERNAL NAME [Microsoft.MasterDataServices.DataQuality].[Microsoft.MasterDataServices.DataQuality.SqlClr].[XmlTransform]
GO

If you have completed steps 1 through 4 without any errors then you have successfully installed mdq.XmlTransform.

Using 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 &lt;= 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

 

Summary

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)