Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
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
add comment

1 Answer

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

Way I do that , Is that I made SQL-CLR stored procedure which using C# (code) and XML.Writer for writing data to disk

heere 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 sproc to write file to disk

exec dbo.clr_write_to_file @imeFajla,@xmlLOG
share|improve this answer
    
Here is another useful link msdn.microsoft.com/en-us/library/w2kae45k.aspx –  adopilot May 9 '12 at 14:10
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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