Sql-server – SQL Database to XML

sql serverxml

I would like to export SQL data to an XML file so it can be read in COBOL. I am working with SQL Express 2008 r2.

Best Answer

I am using export data from query (table ) to XML in SQL when I have needs for autonomous transactions.

Way I do: I wrote a SQL-CLR stored procedure which using C# (code) and XML.Writer for writing data to disk

Here is parts of my code in C# (you need to compile this )

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

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static void NapisiXMLtoFile(string fileName, SqlXml xmlLog )
    {
        try
        {
            if (System.IO.File.Exists(fileName))
            {
                System.IO.File.Delete(fileName);
            }
            File.WriteAllText(fileName, xmlLog.Value.ToString());
        }
        catch (Exception ex)
        {
            SqlContext.Pipe.Send("Error writing to file " + ex.Message);
        }
    }
}

Here is how you register your procedure 

CREATE PROCEDURE [dbo].[clr_write_to_file]
    @fileName [nchar](100),
    @xmlLog [xml]
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [WriteToFile].[UserDefinedFunctions].[NapisiXMLtoFile]
GO

and finally here is how you call your stored procedure to write file to disk

exec dbo.clr_write_to_file @imeFajla,@xmlLOG
Related Question