Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

XML-XSL Transformations in SQL Server

By Cem Güler,

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.

Total article views: 2820 | Views in the last 30 days: 39
 
Related Articles
FORUM

CREATE FUNCTION permission denied in database

CREATE FUNCTION permission denied in database

FORUM

create function

create function

FORUM

Auto-Select Database Name

Auto-Select Database Name

FORUM

"function"

"function"

Tags
mds    
t-sql    
xml    
xsl    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones