title: Generate XML using C# from SQL Server Data
body: <p>I have a query in SQL Server which generates an XML Output.</p>
  
  <p>I want to produce the same result using C#. Is it possible??</p>
  
  <p>The query is </p>
  
  <pre><code>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')
  </code></pre>
  
  <p>For further details regarding this refer to my earlier post. <a href="https://stackoverflow.com/questions/16686554/producing-xml-from-multiple-tables-in-sql-server/16689035#16689035">Producing XML from Multiple Tables in SQL Server</a>.</p>
  
  <p>I am extremely poor in C#. A beginner of sorts. Do need some help here.</p>
  
  <p>The code i used is...</p>
  
  <pre><code>    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
          {
              /// &lt;summary&gt;
              /// The main entry point for the application.
              /// &lt;/summary&gt;
              [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 &amp;&amp; ds.Tables.Count &gt; 0)
                              xml = ds.GetXml();
                      }
                  }
  
                  // Close the connection
                  sdwDBConnection.Close();
  
  
              }
          }
      }
  </code></pre>