XML-XSL Transformations in SQL Server

With the release of Master Data Services (MDS) for SQL Server 2008R2, it is now possible to perform XML transformations on the database server. Even though, it may sound as a bad idea to perform such operations on the database level instead of clientside, one can certainly find useful cases for this functionality.

A database configured with MDS, simply contains some CLR assemblies and UDF functions that make use of these assemblies.

Enabling the XmlTransformation for your own database

If you need only XML transformation functionality, then what you need to do is create a new test database configured by MDS and simply copy the necessary assembly and the UDF function.

  1. While in the SQL Server Management Studio,
    1. Expand <testdbname>/Programmability/Assemblies
    2. Right click on Microsoft.MasterDataServices.DataQuality and select Script Assembly As/Create To/New Query Editor Window
      USE []
      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
    4. Change authorization to [db_datareader]
    5. And execute
    6. Now, expand <testdbname>/Programmability/Functions/Scalar-valued Functions
    7. Right click on mdq.XmlTransform DataQuality and select Script Function As/Create To/New Query Editor Window
      USE []
      GO
      /****** Object:  UserDefinedFunction [mdq].[XmlTransform]
      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
      
    8. Change USE clause to use your own DB name
    9. Change [mdq].[XmlTransform] to [dbo].[XmlTransform]
    10. And execute
    11. Voila! We are ready to use the XmlTransform UDF.

Testing the XmlTransformation

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 better selected from an existing invoice table by using FOR XML clause.

DECLARE @xml xml = '
<Invoice>
      <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>
'

And the following xslt string

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 '
before transformation
---------------------------
'
PRINT convert(nvarchar(max), @xml)
PRINT '
after transformation
---------------------------
'
PRINT dbo.XmlTransform(@xml, @xslt)
GO

The result is


before transformation
---------------------------
<Invoice><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>

 
after transformation
---------------------------
<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>

Pretty easy and runs in 13 milliseconds.

Enjoy,

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s