Generate XML using C# from SQL Server Data

Question

I have a query in SQL Server which generates an XML Output.

I want to produce the same result using C#. Is it possible??

The query is

select T1_1.HomeID as [@HomeID],
(
  select T1_2.DayID as [@ID],
  ( select T2.RndString+' '+left(T1_3.TimeValue, 5) as '*'
    from TB1 as T1_3
    inner join TB2 as T2 on T1_3.DayID = T2.DayType 
    and T1_3.TimeCode = T2.StringCode
    where T1_2.HomeID = T1_3.HomeID 
    and T1_2.DayID = T1_3.DayID
    order by T2.StringCode
    for xml path('String'), type)
    from TB1 as T1_2
    where T1_2.HomeID = T1_1.HomeID
    group by T1_2.DayID,T1_2.HomeID
    order by T1_2.DayID
    for xml path('Day'), type )
    from TB1 as T1_1
    group by T1_1.HomeID
    order by T1_1.HomeID
    for xml path('Person'), root('Persons')

For further details regarding this refer to my earlier post. Producing XML from Multiple Tables in SQL Server.

I am extremely poor in C#. A beginner of sorts. Do need some help here.

The code i used is...

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Windows.Forms;
    using System.Text;
    using System.Data;
    using System.Data.SqlClient;

    namespace SQL__
    {
        static class Program
        {
            /// <summary>
            /// The main entry point for the application.
            /// </summary>
            [STAThread]
            static void Main()
            {
                // Create a String to hold the database connection string.
                // NOTE: Put in a real database connection string here or runtime won't work
                string sdwConnectionString = @"Data Source=IE1ADTBD5ZL1S\;Initial Catalog=RecommendEngine;Integrated Security=True";

                // Create a connection
                SqlConnection sdwDBConnection = new SqlConnection(sdwConnectionString);

                // Open the connection
                sdwDBConnection.Open();

                // To generate XML File using C# from SQL Server Data

                using (SqlDataAdapter da = new SqlDataAdapter())
                {
                    da.SelectCommand.CommandText = @"select T1_1.HomeID as [@HomeID],
                                                     (
                                                       select T1_2.DayID as [@ID],
                                                              (
                                                               select T2.RndString+' '+left(T1_3.TimeValue, 5) as '*'
                                                               from TB1 as T1_3
                                                                 inner join TB2 as T2
                                                                   on T1_3.DayID = T2.DayType and
                                                                      T1_3.TimeCode = T2.StringCode
                                                               where T1_2.HomeID = T1_3.HomeID and
                                                                     T1_2.DayID = T1_3.DayID
                                                               order by T2.StringCode
                                                               for xml path('String'), type
                                                              )
                                                       from TB1 as T1_2
                                                       where T1_2.HomeID = T1_1.HomeID
                                                       group by T1_2.DayID,
                                                                T1_2.HomeID
                                                       order by T1_2.DayID
                                                       for xml path('Day'), type
                                                      )
                                                     from TB1 as T1_1
                                                     group by T1_1.HomeID
                                                     order by T1_1.HomeID
                                                     for xml path('Person'), root('Persons')";
                    da.SelectCommand.Connection = new SqlConnection("sdwDBConnection");

                    string xml = "";
                    using (DataSet ds = new DataSet())
                    {
                        da.SelectCommand.Connection.Open();
                        da.Fill(ds);
                        da.SelectCommand.Connection.Close();

                        if (ds != null && ds.Tables.Count > 0)
                            xml = ds.GetXml();
                    }
                }

                // Close the connection
                sdwDBConnection.Close();


            }
        }
    }

Mojtaba · Accepted Answer · 2013-05-27 10:30:44Z

3

You can use DataTable.WriteXML() and DataTable.WriteXmlSchema() methods to generate XML for your query.

SqlCommand cmd = new SqlCommand("Your Command", new SqlConnection("Connection String"));
DataTable dt = new DataTable();
new SqlDataAdapter(cmd).Fill(dt);

dt.TableName = "Your Table Name";
dt.WriteXml("File Address");
dt.WriteXmlSchema("Schema File Address");

edited May 27, 2013 at 10:30

answered May 27, 2013 at 8:49

Mojtaba

1,6684 gold badges19 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

SarangArd Over a year ago

I Get an InvalidOperationException on using that piece of code. It reads, "Cannot serialize the DataTable. DataTable name is not set." Can you specify what i am supposed to fill in WriteXml()

Mojtaba Over a year ago

You should set dt.TableName property that I forgot it. Sorry :)

Ted · Accepted Answer · 2013-05-27 08:24:33Z

3

Tried and true:

using (SqlDataAdapter da = new SqlDataAdapter())
{
    da.SelectCommand.CommandText = "you sql command";
    da.SelectCommand.Connection = new SqlConnection("your connection");

    string xml = "";
    using (DataSet ds = new DataSet())
    {
        da.SelectCommand.Connection.Open();
        da.Fill(ds);
        da.SelectCommand.Connection.Close();

        if(ds != null && ds.Tables.Count > 0)    
           xml = ds.GetXml();
    }
}

edited May 27, 2013 at 8:24

answered May 27, 2013 at 7:40

Ted

4,0671 gold badge23 silver badges35 bronze badges

3 Comments

SarangArd Over a year ago

I am getting an Error, "NULLREFERENCEEXCEPTION was Unhandled." Am not quite understanding it.

Ted Over a year ago

I've never used dataadapter with xml output queries. you need to convert you SQL to a normal SQL statememt

SarangArd Over a year ago

let us continue this discussion in chat

Collectives™ on Stack Overflow

Generate XML using C# from SQL Server Data

2 Answers 2

2 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related