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.

  • 3
    I guess you have the COBOL part well covered since you are asking on DBA so you should probably start by looking at XML Support in Microsoft SQL Server 2005 and FOR XML. – Mikael Eriksson May 9 '12 at 11:24
  • checked but do I have to write a query (using a FOR XML statement) to export the data from sql server 2008 r2 to a XML -doc? – Verleyen Bert May 9 '12 at 13:03
  • Bear in mind this will also multiply the size of your DB. Xml is flexible but extremely inefficient for storage. When I export a table to XML normally it is 3-4 times the size of the data in SQL Server. – JNK May 9 '12 at 14:23
  • Btw Bert verleyen is the one that I asked the question for, so he knows it better than me :) – user1009013 May 9 '12 at 20:45
  • no, that was a friend ,he asked my question instead ,because it's the first time I 've seen and using this (forum) blog. – Verleyen Bert May 11 '12 at 9:05

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
    public static void NapisiXMLtoFile(string fileName, SqlXml xmlLog )
            if (System.IO.File.Exists(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]
EXTERNAL NAME [WriteToFile].[UserDefinedFunctions].[NapisiXMLtoFile]

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

exec dbo.clr_write_to_file @imeFajla,@xmlLOG

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.