Send Results Of SELECT FOR XML Query To A File

I have posted this on previous forums, but the below code is a VB.net CLR stored proc that will accepts two parameters, a SELECT.... FOR XML query and a file path, and saves the resulting xml result into a file specified by the parameter. After compiling the VB code into a .dll than the T-SQL code to import the assembly, create the CLR proc and test it is included underneath. Hope some will find this useful




Code Snippet
'Requires the Trial or Release version of Visual Studio .NET 2005 Professional (or greater).
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Imports System.Xml

Partial Public Class outputxml
<Microsoft.SqlServer.Server.SqlProcedure()> _
Public Shared Sub outputxml(ByVal XmlData As SqlXml, ByVal Filename As SqlString)
'Create the variables to hold the values that are supplied by the parameters
'input by the stored procedure
Dim xmlDoc As New XmlDocument()
Dim output As SqlPipe = SqlContext.Pipe()
Try
'Load the result set into the XmlDoc Variable and then save the results in the
'path provided by the stored procedure. The values are provided by the
'input parameters of the stored procedure
xmlDoc.LoadXml(XmlData.Value)
xmlDoc.Save(Filename.Value)
Catch ex As Exception
'If an error occurs catch the message and pipe it back to SQL
output.Send(ex.Message.ToString)
End Try
End Sub
End Class

T-SQL CODE BEGINS HERE


/*Alter the database to set trustworthy on in order

to allow the ability to set the assembly to external

access*/

ALTER DATABASE AdventureWorks SET trustworthy ON

--Import the assembly into SQL

CREATE ASSEMBLY outputxml

from 'C:outputxml.dll'

WITH PERMISSION_SET = EXTERNAL_ACCESS

-- Create the proc from the imported dll

CREATE PROCEDURE output

@xmldata XML,

@filename nvarchar(1024)

AS

EXTERNAL NAME outputxml.[outputxml.outputxml]

.outputxml



-- Test managed stored procedure

DECLARE @output xml

SET @output = (SELECT ProductID, Name, ListPrice

FROM Production.Product Product

FOR XML AUTO, ROOT('Catalog'), TYPE)

EXEC dbo.outputxml @output, 'c:Output.xml'

GO