Lorenzo Benaglia


Storie di un SQLlaro mannaro...
Archivio Posts
Anno 2010

Anno 2009

Anno 2008

Anno 2007

Anno 2006

Anno 2005
Calendario
ottobre 2022
lmmgvsd
262728293012
3456789
10111213141516
17181920212223
24252627282930
31123456

Come esportare su file un result set in formato XML con SQL Server 2005

Ieri Roberto ha postato su microsoft.public.it.sql la seguente domanda:
"Vorrei sapere quale è il metodo più indolore per salvare l'output di una query XML direttamente su un file xml utilizzando SQL Server 2005 Standard Edition".

Come ho già avuto modo di ripetere in diversi interventi su questo blog, SQL Server 2005 permette di leggere file XML mediante la funzione OPENROWSET ed il BULK rowset provider ma non offre nativamente una funzione per esportare un result set in formato XML.
Fortunatamente l'integrazione del CLR direttamente nel DB Engine ci permette di estendere facilmente le funzionalità del DBMS.
Per rispondere al quesito di Roberto, proporrò una piccola stored procedure scritta in Visual C#.

Come prima cosa lanciamo il SQL Server Business Intelligence Development Studio, definiamo un nuovo progetto database chiamato "SQLCLRUtility" ed aggiungiamo una Stored Procedure chiamata "SaveXMLTofile" con il seguente codice:

using
System;
using
System.Data;
using
System.Data.SqlClient;
using
System.Data.SqlTypes;
using
Microsoft.SqlServer.Server;
using
System.IO;
 
public
partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.
SqlProcedure]
    public static void SaveXMLTofile(SqlXml XMLData, String DestFile, Boolean Append)
    {
        StreamWriter writer = new StreamWriter(DestFile, Append, System.Text.Encoding.UTF8);
        writer.Write(
@"<?xml version=""1.0"" encoding=""utf-8"" ?>");
        writer.Write(XMLData.Value);
        writer.Close();

        SqlContext.Pipe.Send(String.Format("XML text successfully saved to file '{0}'", DestFile));
    }
};

Eseguiamo la build del progetto senza effettuarne il deploy sull'istanza (lo faremo via comandi T-SQL).
Apriamo il SQL Server Management Studio, selezioniamo il bottone New Query ed incollamo il seguente script (modificando oppurtunamente il path dell'assembly SQLCLRUtility.dll):

USE AdventureWorks;
GO

/* Imposto a on la proprietà TRUSTWORTHY */
ALTER DATABASE AdventureWorks SET TRUSTWORTHY ON;
GO

/* Creo l'assembly */
CREATE ASSEMBLY SQLCLRUtility
AUTHORIZATION dbo
FROM 'C:\...\SQLCLRUtility.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS;
GO

/* Creo la stored procedure dbo.up_SaveXMLToFile */
CREATE PROCEDURE dbo.up_SaveXMLToFile(
@XMLData xml,
@DestFile nvarchar(255),
@Append bit = 0
)
AS
EXTERNAL NAME SQLCLRUtility.StoredProcedures.SaveXMLTofile;
GO

/* Test */
DECLARE @XMLData xml;

SET @XMLData = (
    SELECT TOP 10 FirstName, LastName, EmailAddress
    FROM Person.Contact
    FOR XML AUTO, ELEMENTS, ROOT('Contacts')
);

EXEC dbo.up_SaveXMLTofile @XMLData, N'C:\Contacts.xml';
GO

/* Output:

XML text successfully saved to file 'C:\Contacts.xml'

*/

/* Pulizia */
DROP PROCEDURE dbo.up_SaveXMLTofile;
DROP ASSEMBLY SQLCLRUtility;
!!DEL C:\Contacts.xml


Dopo aver importato l'assembly e definito la stored procedure non ci resta che generare lo stream XML e passarlo alla stored procedure up_SaveXMLTofile insieme al nome del file di output.
La variabile @XMLData contiene lo stream XML che vogliamo salvare su file, ottenuto eseguendo la query con la clausola FOR XML.

Come vedete ancora una volta con poche righe di codice siamo riusciti nell'intento di esportare su file un result set in formato XML.
Per chi fosse curioso di conoscere alcune tecniche utilizzate con SQL Server 2000, può dare un'occhiata a questo articolo.
Categoria: SQL Server
giovedì, 31 ago 2006 Ore. 17.02
Statistiche
  • Views Home Page: 893.173
  • Views Posts: 669.360
  • Views Gallerie: 10.715.803
  • n° Posts: 300
  • n° Commenti: 314
Mappa





















Copyright © 2002-2007 - Blogs 2.0
dotNetHell.it | Home Page Blogs
ASP.NET 2.0 Windows 2003