Save SQL XML field type to a xml file

Hi,

I have a table which holds raw xml in a field with type XML. How do I export that XML field to an XML file?

Thanks

Bones

You can do by using CLR based stored procedure. Here is a link.

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

|||

Thanks for the help but unfortunitly this is for an other version on .NET. When I try to compile the .DLL I get the errors for "File.WriteAll" and "SqlContext.GetPipe();"

Both of which are not recognized. Any other ideas?

public static void WriteToFile(String content,String filename)
 {
 SqlPipe sqlP = SqlContext.GetPipe();
 try
 {
 File.WriteAll(filename, content + "\n");
 }
 catch(Exception ex)
 {
 sqlP.Send("Error writing to file : " + ex.Message);
 }
 }

|||

What is your dot net version ? i assume .Net 2.0 with sql 2005. above code will work with .net 2.0/sql 2005.

|||

Hi

I am using .NET 2.0 and SQL server 2005. There where some absolete properties in the post (e.g. GetPipe());

Here is the revised code (untested - but builds):

using System;using System.Data.Sql;using System.Data.SqlTypes;using System.IO;using System.Data.SqlClient;using Microsoft.SqlServer.Server;namespace XSLTCsharp{public class SQLCLRIO {public static void WriteToFile(String content, String filename) { SqlPipe sqlP = SqlContext.Pipe;try { File.WriteAllText(filename, content +"\n"); }catch (Exception ex) { sqlP.Send("Error writing to file : " + ex.Message); } } }}

|||

Here are couple of links that will help you to create a CLR based procedures.

http://www.dotnetfun.com/articles/sql/sql2005/SQL2005CLRSProc.aspx

http://www.c-sharpcorner.com/UploadFile/pk_khuman/ManagedStoredProceduresUsingCSharp02182007232059PM/ManagedStoredProceduresUsingCSharp.aspx

|||

Awesome...very helpfull. I got it to run. I had to do some tweaks with setting up the SQL database, but all is good!

thank a bunch.