Exporting data to a XML file in SQL Server 2005

Overview

In this article, I'll demonstrate how we can combine the new CLR integration and FOR XML enhancements to easily generate xml files from our relational data. For an introduction to .NET Stored procedures have a look at Introducing CLR Stored Procedures in SQL Server 2005. This article will reference the Orders table in the Northwind database. You can download the Northwind and pubs databases here.

.NET Code

The first thing we need to do is write our .Net code for our Stored Procedure and compile it. In this case, we are going to create a procedure that accepts a filename and some content (using a nvarchar(max) parameter) and simply writes that content to the specified file.

[Visual Basic]

Imports System
Imports System.Data
Imports Microsoft.SqlServer.Server
Imports System.Data.SqlTypes
Imports System.IO

Public Class SQLCLRIO
  <> _
  Public Shared Sub WriteToFile(ByVal content As String, _
                                ByVal filename As String)

      Try
         File.WriteAllText(filename, content)

      Catch ex As Exception
          SqlContext.Pipe.Send("Error writing to file " & ex.Message)
      End Try

  End Sub

End Class

[C#]

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

public class SQLCLRIO{

  [Microsoft.SqlServer.Server.SqlProcedure]
  public static void WriteToFile(String content,String filename)
  {
      try
      {
         File.WriteAllText(filename, content);
      }
      catch(Exception ex)
      {
         SqlContext.Pipe.Send("Error writing to file " + ex.Message);
      }
  }
}

In order for us to use this code in SQL Server 2005 we must first compile it into an assembly. We can then load it into SQL Server and create our TSQL Stored Procedure wrapper for our .Net code. The command below demonstrates how to compile the code into an assembly. For the purposes of this article we will assume that the code has been saved in a source file called c:\WriteToFile.vb or c:\WriteToFile.cs. To compile this open a command prompt and execute the relavent lines to change to the .NET 2.0 directory and compile the code.

[Visual Basic] cd "%SystemRoot%\Microsoft.NET\Framework\v2.0.50727" vbc /target:library c:\WriteToFile.vb [C#] cd "%SystemRoot%\Microsoft.NET\Framework\v2.0.50727" csc /target:library c:\WriteToFile.cs

TSQL Code

Now that we have created our assembly (c:\WriteToFile.dll) we can load it into SQL Server and create our Stored Procedure. We do this by using the new CREATE ASSEMBLY command as below. We need to specify a permission_set of EXTERNAL_ACCESS because the stored procedure needs to access the file system. We need to ensure that execution of user defined CLR code is enabled using sp_configure and we also need to set the database Trustworthy property ON to allow the loading of the EXTERNAL_ACCESS assembly. NOTE : The external access will be under the security context of the SQL Service account

exec sp_configure 'clr enabled',1 reconfigure go alter database Northwind set trustworthy on go use Northwind go create assembly WriteToFile from 'c:\WriteToFile.dll' with permission_set = external_access

Now that we have loaded our assembly, we can create our Stored Procedure. To do this we simply use the standard TSQL CREATE PROCEDURE syntax but instead of defining a TSQL procedure body, we specify an EXTERNAL NAME that points to our .NET Code. We specify the assembly, class and method name in the form .. as shown below.

create procedure dbo.writetofile ( @content nvarchar(max), @filename nvarchar(255) ) as external name WriteToFile.SQLCLRIO.WriteToFile

Now that the Stored Procedure is created, we can use it to write our content (in this case xml) to a file. The sample query will take the entire contents of the Northwind.dbo.Orders table and create a single, well formed xml document from it which we will then write to a xml file using the WriteToFile stored procedure. The query takes advantage of the ability to declare local variables for LOB types such as varchar(max) and nvarchar(max) - these datatypes supercede the text and ntext datatypes - as well as the new root directive of the FOR XML clause that allows you to specify a single top level root element for you xml document, in the code below the top level element will be . This code will create a new file called AllOrders.xml containing all the order header information from the Northwind database. You can view the contents of the generated file by simply opening it in your web browser.

declare @xml nvarchar(max)

set @xml = (
select * from Orders
for xml auto,elements,root('AllOrders'))

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

Source : http://www.sqldbatips.com/showarticle.asp?ID=23