La versione 2005 di SQL Server integra al suo interno il Common Language Runtime (CLR) del framework .NET. Questa caratteristica permette agli sviluppatori di database di creare oggetti SQL Server sia con T-SQL, sia con i linguaggi supportati dal framework .NET (C#,VB.NET,etc..).
In questo articolo illustreremo come eseguire trasformazioni XSLT su dati XML in SQL Server 2005 tramite una CLR User-Defined Function scritta in C#.
Il codice managed in C#
Dimenticando per un attimo il linguaggio T-SQL, vediamo il codice C# per eseguire una trasformazione XSLT:
Listato 1. Codice C# per trasformare dati XML con XSLT
using System;
using System.IO;
using System.Xml;
using System.Xml.Xsl;
using System.Data.SqlTypes;
public class XSLTUtils
{
///
/// Trasforma dati XML tramite fogli XSLT da SQL Server 2005
///
public static SqlXml Transform( SqlXml inputDataXML, SqlXml inputTransformXML )
{
MemoryStream memoryXml = new System.IO.MemoryStream();
XslCompiledTransform xslt = new XslCompiledTransform();
XmlReader output = null;
xslt.Load( inputTransformXML.CreateReader() );
//Costruzione del nuovo output XML
XmlTextWriter outputWriter = new XmlTextWriter( memoryXml, System.Text.Encoding.Default );
xslt.Transform( inputDataXML.CreateReader(), null, outputWriter, null);
memoryXml.Seek( 0, System.IO.SeekOrigin.Begin );
output = new XmlTextReader( memoryXml );
return new SqlXml( output );
}
}
Il metodo statico Transform (è obbligatorio marcare con static le funzioni per il SQLCLR!) è contenuto nella classe pubblica XSLTUtils e richiede due parametri di input: i dati XML da trasformare e quelli del foglio XSLT (sempre in formato XML) che deve guidare la trasformazione. Quest'ultima viene effettuata sfruttando l'oggetto del framework .NET XslCompiledTransform, e produce come risultato un nuovo output XML.
Tutti i dati XML elaborati da Transform (sia i parametri di input sia il risultato finale) sono rappresentati dal tipo dato SqlXml. Esso risiede nel namespace System.Data.SqlTypes dell'assembly System.data.dll e contiene le interfacce per il tipo dati XML di SQL Server.
Salviamo il codice del listato 1 in un file dal nome "XSLTutils.cs" e compiliamo. Per farlo ci sono due strade: usare Visual Studio 2005 o il compilatore C# da linea di comando. Per questioni di semplicità consiglio di optare per la seconda scelta.
Compilare con csc da DOS
csc /t:library /out:XSLTUtils.dll xslt.cs
Il risultato della compilazione è l'assembly XSLTUtils.dll.
Il deploy sul Server SQL
Il passo successivo da compiere è il deploy su SQL Server dell'assembly appena creato. Questa operazione
consente di "caricare" la dll (i bytes di codice) sul database affinchè possa essere disponibile per il SQLCLR. Prima di procedere è bene ricordare due cose:
- Per default l'integrazione con il CLR in SQL Server 2005 è disabilitata. Per attivarla dobbiamo prima eseguire le seguenti istruzioni T-SQL :
sp_configure "clr enabled", 1; go; reconfigure; go
- Nel codice C# la sintassi è case sensitive, pertanto è necessario fare attenzione a scrivere correttamente i nomi di classi, assemblies, etc.
Il deploy dell'assembly è realizzato utilizzando l'istruzione DDL di T-SQL: CREATE ASSEMBLY.
Listato 2. Caricamento dell'assembly sul database SQL
use Html
GO;
create assembly XSLTUtils from 'C:svnXSLTUtilsXSLTUtils.dll'
WITH PERMISSION_SET=SAFE;
GO;
Durante la creazione dell'assembly in SQL Server 2005 è necessario specificare il tipo di permission da applicare.
Le possibili impostazioni sono: Safe, External_Access ed Unsafe. Per avere maggiore dettagli sulla sicurezza e il CLR in SQL Server potete consultare questo link.
Una volta caricato l'assembly esso compare tra quelli disponibili nel database HTML. Possiamo quindi procedere alla mappatura tra la funzione C# XSLTUtils.Transform e la nuova funzione SQL (UDF) ExecuteXsltTransform.
Da notare che il tipo dati dei parametri della SQL UDF è cambiato, ora la funzione accetta esclusivamente il tipo dati XML-untyped di SQL Server.
Listato 3. Mappatura della funzione C# con la SQL UDF
use Html
GO;
create function ExecuteXsltTransform( @xmlData xml, @xsltData xml )
returns xml as external name XSLTUtils.XSLTUtils.Transform;
GO;
Durante la creazione/mappatura della SQLCLR UDF, alla classe XSLTUtils.Transform viene assegnato un "external name" che adotta la seguente sintassi:
EXTERNAL NAME [nome_assembly].[nome_classe].[nome_metodo]
Ora finalmente possiamo usare la nostra funzione SQLCLR negli scritps T-SQL!
L'UDF negli scripts SQL
Per richiamare la funzione ci comportiamo come per le normali UDF scalari passando il codice XML e XSLT come stringhe oppure come file esterni:
SELECT [dbo].[ExecuteXsltTransform]('...xml...','...xslt...')
Vediamo alcuni esempi:
Listato 5. Trasformazione di dati XML passando stringhe
DECLARE @Xml Xml; SELECT @Xml=[dbo].[ExecuteXsltTransform] (
'<?xml version="1.0"?><foo>Trasformare dati XML via XSLT con SQL Server 2005</foo>',
'<?xml version="1.0"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
<xsl:template match="foo">
<print><xsl:value-of select="."/></print>
</xsl:template>
</xsl:stylesheet>'
SELECT @Xml; --Ecco il risultato della trasformazione!!!
Listato 6. Trasformazione di dati XML con dati caricati da file esterni
DECLARE @xml xml
DECLARE @xslt xml
SELECT @xml = BulkColumn from openrowset( Bulk 'C:Ordini.xml', SINGLE_BLOB ) as x
SELECT @xslt = BulkColumn from openrowset( Bulk 'C:Report1.xsl', SINGLE_BLOB ) as x
SELECT dbo.ExecuteXsltTransform( @xml, @xslt );
Conclusione
L'integrazione del CLR .NET in SQL Server 2005, se utilizzata con cognizione di causa e attenzione, rappresenta uno strumento in più per gli amministratori di database e per gli sviluppatori nella creazione di procedure con logiche di business complesse.
