Salvare una query SQL 2005 con output XML in un file XML

[Roberto Razzauti]

SQL SERVER 2005 STD.
Vorrei sapere quale è il metodo più indolore per salvare l'output di una
query XML direttamente su un file xml.

La query ad es. è:
SELECT * FROM TEST_XML CategoriaImmobiliare for xml AUTO, ROOT
('CategorieImmobiliari'), ELEMENTS
e ritorna a video un bel ouput xml simile a questo:

<CategorieImmobiliari>
<CategoriaImmobiliare>
<value>1</value>
<text>Appartamento</text>
<Categoria>Immobili-Residenziali</Categoria>
</CategoriaImmobiliare>
<CategoriaImmobiliare>
<value>21</value>
<text>Villa</text>
<Categoria>Immobili-Residenziali</Categoria>
</CategoriaImmobiliare>
.....
</CategorieImmobiliari>

Vorrei che il tutto compresa l'intestazione canonica
<?xml version="1.0" encoding="utf-8" ?>
mi finisse in apposito file xml.

Ho provato con i tool del Businness Intelligence Development, ma mi sono
perso per strada non avendo trovato un wizard facile che mi esportasse anche
una semplice query su file xml.
Grazie

[Lorenzo Benaglia]

Roberto Razzauti wrote:
> SQL SERVER 2005 STD.
> Vorrei sapere quale è il metodo più indolore per salvare l'output di
> una query XML direttamente su un file xml.

Ciao Roberto,

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.
Ad ogni modo puoi scrivere una semplice procedura CLR per ottenere il
risultato desiderato.
Ti allego un esempio in C#.

Apri SQL Server Business Intelligence Development Studio, definisci un nuovo
progetto database chiamato "SQLCLRUtility", aggiungi una Stored Procedure
chiamata "SaveXMLTofile" ed incolla 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));
}
};

Esegui la build del progetto ma non effettuare il deploy (lo faremo via
codice T-SQL).
Apri SQL Server Management Studio, premi il bottone New Query ed incolla 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

Per qualunque informazione aggiuntiva consulta MSDN ed i Books Online.

> Grazie
Prego.

Ciao!

--
Lorenzo Benaglia
Microsoft MVP - SQL Server
http://blogs.dotnethell.it/lorenzo
http://italy.mvps.org

[Roberto Razzauti]

Ho provato e funziona molto bene.
Grazie

"Roberto Razzauti" ha scritto:

[Roberto Razzauti]

Ho provato sul db AdventureWorks e funziona.
Come cambio DB ottengo il seguente errore:

"Operazione CREATE ASSEMBLY per l'assembly 'SQLCLRUtility' non riuscita
perché l'assembly 'SQLCLRUtility' non è autorizzata per PERMISSION_SET =
EXTERNAL_ACCESS. L'assembly è autorizzata nei seguenti casi: il proprietario
del database (DBO) dispone dell'autorizzazione EXTERNAL ACCESS ASSEMBLY e la
proprietà TRUSTWORTHY del database è attivata oppure l'assembly è firmato con
un certificato o una chiave asimmetrica a cui è associato un account di
accesso con l'autorizzazione EXTERNAL ACCESS ASSEMBLY."

Credo che il problema sia il primo e cioè dbo non dispone
dell'autorizzazione EXTERNAL ACCESS ASSEMBLY sul database in questione.
Come faccio ad assegnargliela?

"Lorenzo Benaglia" ha scritto:

[Lorenzo Benaglia]

Roberto Razzauti wrote:
> Ho provato sul db AdventureWorks e funziona.
> Come cambio DB ottengo il seguente errore:
>
> "Operazione CREATE ASSEMBLY per l'assembly 'SQLCLRUtility' non
> riuscita perché l'assembly 'SQLCLRUtility' non è autorizzata per
> PERMISSION_SET = EXTERNAL_ACCESS. L'assembly è autorizzata nei
> seguenti casi: il proprietario del database (DBO) dispone
> dell'autorizzazione EXTERNAL ACCESS ASSEMBLY e la proprietà
> TRUSTWORTHY del database è attivata oppure l'assembly è firmato con
> un certificato o una chiave asimmetrica a cui è associato un account
> di accesso con l'autorizzazione EXTERNAL ACCESS ASSEMBLY."
>
> Credo che il problema sia il primo e cioè dbo non dispone
> dell'autorizzazione EXTERNAL ACCESS ASSEMBLY sul database in
> questione. Come faccio ad assegnargliela?

Chi è il dbowner?
A quale login è mappato?

[Roberto Razzauti]

Ammesso di aver capito la domanda (scusa non sono un dba di professione)....
provo a leggere le info sulle properties del db (tasto dx sull'instanza):
db di test adventureworks: owner = INLDOM\Roberto
db attuale astemaster: owner = INLDOM\Roberto

come evidente l'owner è un utente di dominio quindi è mappato su di esso.
Ho risposto correttamente?

Però su adventureworks l'assembly viene creato, su astemaster no.

"Lorenzo Benaglia" ha scritto:

[Lorenzo Benaglia]

Roberto Razzauti wrote:
> Perň su adventureworks l'assembly viene creato, su astemaster no.

hai eseguito questo comando?

/* Imposto a on la proprietŕ TRUSTWORTHY */
ALTER DATABASE <tuo database> SET TRUSTWORTHY ON;
GO

[Roberto Razzauti]

si:
ecco le prime righe (copiate dall'esempio che mi hai fornito qualche giorno
fa...)

USE ASTEMASTER;

GO
/* Imposto a on la proprietà TRUSTWORTHY */

ALTER DATABASE ASTEMASTER SET TRUSTWORTHY ON;

GO
/* Creo l'assembly */
CREATE ASSEMBLY SQLCLRUtility
AUTHORIZATION dbo

FROM 'C:\SQLCLRUtility.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS
.......

e qui si pianta

se invece gli stessi comandi li applico ad Adventureworks:

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

Questo funziona

"Lorenzo Benaglia" ha scritto:

> Roberto Razzauti wrote:
> > Però su adventureworks l'assembly viene creato, su astemaster no.
>
> hai eseguito questo comando?
>
> /* Imposto a on la proprietà TRUSTWORTHY */