With the release of Master Data Services (MDS) for SQL Server 2008R2, it is now possible to perform XML transformations on the database server. By the use of FOR XML clause and an XSL variable, you can create HTML invoices, or modify your FOR XML outputs and return the result to the client. Even though, it may sound as a bad idea to perform such operations on the database level, one can certainly find useful cases for this functionality.

A database which is configured with MDS contains some extra CLR assemblies and UDF functions that lets you perform additional operations. Most interesting ones are regex functions, similarity function, and of course the XmlTransform UDF inside Microsoft.MasterDataServices.DataQuality assembly.

In this article, I will try to demonstrate a how we can generate a simple Invoice HTML result by the use of XmlTransform UDF without enabling the whole MDS functionality in our database. We will only borrow the required assembly and UDF from an already MDS configured database.

In case you need more information on how to install MDS and create an MDS database, you can check out the following links:

How to: Install Master Data Services

How to: Create a Master Data Services Database

Enabling the XmlTransformation for your own database

As I mentioned earlier, we only need XML transformation functionality, so what we need to do is to create a new test database configured by MDS and simply copy the necessary assembly and the UDF function to our database.

Assuming that you have installed MDS and created a MDS database named [MDSconfigured_dbname], then, we first need to create Microsoft.MasterDataServices.DataQuality assembly in our own database, to do this :

1. While in the SQL Server Management Studio, go to database which is configured by MDS, and expand to <MDSconfigured_dbname>/Programmability/Assemblies
2. Right click on Microsoft.MasterDataServices.DataQuality and select Script Assembly As/Create To/New Query Editor Window USE [MDSconfigured_dbname]

   GO

   ------- Object:  SqlAssembly [Microsoft.MasterDataServices.DataQuality]

   CREATE ASSEMBLY [Microsoft.MasterDataServices.DataQuality]

   AUTHORIZATION [mds_schema_user]

   FROM 0x4D5A90000300000004000000FFFF0000B8000....

   WITH PERMISSION_SET = SAFE

   GO

3. Change USE clause to use your own DB name, [your_dbname]
4. Change authorization to [db_datareader]
5. And execute

Now, we need to create the function XmlTransform, to do this : 

1. Go to database which is configured by MDS, and expand <MDSconfigured_dbname>/Programmability/Functions/Scalar-valued Functions
2. Right click on mdq.XmlTransform function and select Script Function As/Create To/New Query Editor Window

   USE [MDSconfigured_dbname]
   GO
   
   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

3. Change USE clause to use your own DB name, [your_dbname]
4. Change the function name from [mdq].[XmlTransform] to [dbo].[XmlTransform]
5. And execute

Voila! We are ready to use the XmlTransform function.

Preparing an Invoice HTML with XmlTransform function

Let us try to create an HTML table for the items in an invoice. Assume that we have the following invoice XML, either passed as a nvarchar/xml parameter or selected from an existing invoice table by using FOR XML clause.

DECLARE @xml xml = '
<Invoice ID=''5'' Date=''01.01.2011''>
      <InvoiceItem ProdCode=''123400-9'' ProdName=''XX Ultimate'' Quantity=''1'' UnitPrice=''10.40'' VAT=''0.18'' /> 
      <InvoiceItem ProdCode=''10001-41'' ProdName=''YY Basic '' Quantity=''4'' UnitPrice=''0.80'' VAT=''0.18'' /> 
      <InvoiceItem ProdCode=''4920-293'' ProdName=''ZZ Set'' Quantity=''2'' UnitPrice=''5.00'' VAT=''0.18'' /> 
</Invoice> 
'

DECLARE

@xslt xml =

'

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="2.0">

<xsl:output omit-xml-declaration="yes" />

<xsl:template match="/Invoice">

 <table>

  <xsl:apply-templates select="InvoiceItem" />

 </table>

</xsl:template>

<xsl:template match="InvoiceItem">

  <tr>

    <td><xsl:value-of select="current()/@ProdCode" /></td>   

    <td><xsl:value-of select="current()/@ProdName" /></td>

    <td><xsl:value-of select="current()/@Quantity" /></td>

    <td><xsl:value-of select="current()/@UnitPrice" /></td>

    <td><xsl:value-of select="current()/@VAT" /></td>

    <td><xsl:value-of select="current()/@UnitPrice*@Quantity" /></td>

  </tr>

</xsl:template>

</xsl:stylesheet>

'

Then the transformation can be written as

PRINT dbo.XmlTransform(@xml, @xslt)

GO

The result is

<table>

 <tr>

   <td>123400-9</td>

   <td>XX Ultimate</td>

   <td>1</td>

   <td>10.40</td>

   <td>0.18</td>

   <td>10.4</td>

 </tr>

 <tr>

   <td>10001-41</td>

   <td>YY Basic</td>

   <td>4</td>

   <td>0.80</td>

   <td>0.18</td>

   <td>3.2</td>

 </tr>

 <tr>

   <td>4920-293</td>

   <td>ZZ Set</td>

   <td>2</td>

   <td>5.00</td>

   <td>0.18</td>

   <td>10</td>

 </tr>

</table>

Summary

This is pretty easy, and it runs in 13 milliseconds. Even though you can write your own XML Transformation function with CLR, why re-invent the wheel if it is already there. Two XML variables and a simple call to the function, you can quickly get whatever result you wish to get, either an HTML or a modified XML result.