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();


            }
        }
    }

Answer 1

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");