Exportar Datos a un Archivo XML en SQL Server

Ultimamente he tenido que crear muchos XML para diferentes interfaces que he venido desarrollando, y para mejorar el desempeño y ademas de llevar un control de los XML que se generan para posibles auditorias de datos pues quise buscar una solucion que me guardara los XML en un archivo fisico y no generarlo por SQL Server y hacer llamados por otro programa...

Me encontre la solucion haciendo un assembly en C# y registrandolo en SQL Server.

Primero Creamos en C# nuestro assembly (Nuevo Proyecto -> Nueva Libreria de Clase)

using System;
using System.Data;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;
using System.IO;

public class ExportoArchivo{

[Microsoft.SqlServer.Server.SqlProcedure]
public static void WriteToFile(String sContenido,String sFileName)
{
try
{
File.WriteAllText(sFileName, sContenido);
}
catch(Exception ex)
{
SqlContext.Pipe.Send("Error escribiendo archivo " + ex.Message);
}
}
}

Luego de Compilar el Ensamblado pues hay que registrarlo en SQL Server.

Aqui me consegui con algunos percances no se si les pasara a uds. pero les relato paso a paso:

Primero hay que asegurarnos de Habilitar CLR

exec sp_configure 'clr enabled',1
reconfigure
go

Luego establecer como trustworthy la base de datos donde van a registrar la assembly esto para poder hacer que el assembly sea de acceso externo

alter database MiBaseDeDatos set trustworthy on
go

Y Por ultimo crear el Ensamblado:

use Northwind
go
create assembly WriteToFile from 'c:\WriteToFile.dll'
with permission_set = external_access

Se debe hacer con permiso de acceso externo ya que guardaremos un archivo fisico (o por lo menos es lo que entendi)

Aqui vino mi primer Problema: El SID de la base de datos estaba asignado a otro usuario y por lo tanto no me dejaba registrar el Assembly, lo que tuve que hacer es correr el siguiente script:

ALTER AUTHORIZATION ON DATABASE::MiBaseDeDatos TO sa

Ahora en SQL Server creamos un simple Stored Procedure pero con un pequeño agregado de diferencia y es que debemos indicarle que va a usar nuestro Ensamblado como cuerpo del Stored Procedure de la siguiente forma

create procedure dbo.writetofile
(
@sXML nvarchar(max),
@sFileName nvarchar(255)
)
as external name NombreAssembly.NombreClass.NombreMetodo

Aqui viene el otro problema que tuve, cuando ejecute este script de SP, pues me arrojaba el error que decia que no se conseguia ese tipo en el "NombreAssembly", y la solucion inmediata que consegui fue que debi haber creado el SP al indicarle el External Name de la siguiente forma:

as external name NombreAssembly.[NombreAssembly.NombreClass].NombreMetodo

Listo, ahora solo me queda configurar como lo desee y ejecutar el SP

declare @xml nvarchar(max)

set @xml = (
select * from MiTabla
for xml auto,elements,root('MiRootElement'))

exec writetofile @xml,'c:\miNombreArchivo.xml'

Espero sirva de ayuda...

Saludos