I've been having a play with SQL 2008 R2 and its new Master Data Services (MDS).  I was pleased to see it adds some additional functions when you create a new MDS database.  These include mdq.Split and mdq.RegexReplace which implement handy Regular Expression functions.  Most of these are documented at: http://msdn.microsoft.com/en-us/library/ee633712(SQL.105).aspx

I also discovered a scalar function called mdq.XmlTransform which allows you to use XSLT:

DECLARE @xml XML, @xslt XML

SET @xml = '<aaa>

  <n-bbb>

    <ccc />

    <n-ddd />

  </n-bbb>

  <eee>

    <fff>

      <n-ggg />

    </fff>

    <hhh />

  </eee>

</aaa>'

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

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

  <xsl:template match="*">

    <xsl:copy>

      <xsl:apply-templates />

    </xsl:copy>

  </xsl:template>

  <xsl:template match="*[substring(name(),1,2)=''n-'']">

    <xsl:element name="n:{substring(name(),3)}" namespace="nnn-namespace">

      <xsl:apply-templates />

    </xsl:element>

  </xsl:template>

</xsl:stylesheet>'

SELECT @xml before, CAST( mdq.XmlTransform ( @xml, @xslt ) AS XML ) after

GO

Cool huh?  I’m hoping it’s a bit of an Easter Egg as I can’t find any documentation about it on the net.  I’m guessing it just implements the Transform method of System.Xml.Xsl but it’s still handy nonetheless.

Hopefully you enjoyed our trip as we went beyond relational via XML and XSLT on your SQL Server!