Databases
StackOverflow2013
Postsdbo
POGenerate following XML using C#
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POGenerate following XML using C#
    primarykey
    data
    text
    <p>Is it possible to obtain the following XML File using C#. The data is in SQL Server. </p> <pre><code>&lt;Person HomeID="1"&gt; &lt;Day ID="1"&gt; &lt;String&gt;I get up at 07:00&lt;/String&gt; &lt;String&gt;I have breakfast at 07:30&lt;/String&gt; &lt;String&gt;I go to office at 08:00&lt;/String&gt; &lt;String&gt;I have lunch at 13:00&lt;/String&gt; &lt;String&gt;I come back from office at 17:00&lt;/String&gt; &lt;String&gt;I have dinner at 19:00&lt;/String&gt; &lt;String&gt;I sleep at 21:30&lt;/String&gt; &lt;/Day&gt; &lt;Day ID="2"&gt; &lt;String&gt;I get up at 08:00&lt;/String&gt; &lt;String&gt;I have breakfast at 08:30&lt;/String&gt; &lt;String&gt;I have lunch at 13:00&lt;/String&gt; &lt;String&gt;I have dinner at 20:00&lt;/String&gt; &lt;String&gt;I sleep at 23:00&lt;/String&gt; &lt;/Day&gt; &lt;/Person&gt; &lt;Person HomeID="2"&gt; &lt;Day ID="1"&gt; &lt;String&gt;I get up at 08:00&lt;/String&gt; &lt;String&gt;I have breakfast at 08:30&lt;/String&gt; &lt;String&gt;I go to office at 09:00&lt;/String&gt; &lt;String&gt;I have lunch at 13:00&lt;/String&gt; &lt;String&gt;I come back from office at 18:00&lt;/String&gt; &lt;String&gt;I have dinner at 20:00&lt;/String&gt; &lt;String&gt;I sleep at 22:00&lt;/String&gt; &lt;/Day&gt; &lt;Day ID="2"&gt; &lt;String&gt;I get up at 09:00&lt;/String&gt; &lt;String&gt;I have breakfast at 10:00&lt;/String&gt; &lt;String&gt;I have lunch at 13:00&lt;/String&gt; &lt;String&gt;I have dinner at 19:00&lt;/String&gt; &lt;String&gt;I sleep at 22:30&lt;/String&gt; &lt;/Day&gt; &lt;/Person&gt; </code></pre> <hr> <p>I have two tables, TB1 and TB2.</p> <p>Fields in TB1 are HomeID, DayID, TimeCode, timevalue.</p> <pre><code> HomeID DayID TimeCode timevalue 1 1 1 07:00:00 1 1 2 07:30:00 1 1 3 08:00:00 1 1 4 13:00:00 1 1 5 17:00:00 1 1 6 19:00:00 1 1 7 21:30:00 1 2 1 08:00:00 1 2 2 08:30:00 1 2 3 13:00:00 1 2 4 20:00:00 1 2 5 23:00:00 2 1 1 08:00:00 2 1 2 08:30:00 2 1 3 09:00:00 2 1 4 13:00:00 2 1 5 18:00:00 2 1 6 20:00:00 2 1 7 22:00:00 2 2 1 09:00:00 2 2 2 10:00:00 2 2 3 13:00:00 2 2 4 19:00:00 2 2 5 22:30:00 </code></pre> <p>Fields in TB2 are DayType, StringCode, RndString.</p> <pre><code> DayType StringCode RndString 1 1 I get up at 1 2 I have breakfast at 1 3 I go to office at 1 4 I have lunch at 1 5 I come back from office at 1 6 I have dinner at 1 7 I sleep at 2 1 I get up at 2 2 I have breakfast at 2 3 I have lunch at 2 4 I have dinner at 2 5 I sleep at </code></pre> <p><strong>Note:</strong> TB1.DayID=TB2.DayType and TB1.TimeCode=TB2.StringCode</p> <p>The equivalent SQL Query to generate the desired XML Output 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') </code></pre> <p>My trial at the solution.</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 { // To generate XML File using C# from SQL Server Data // The main entry point for the application. [STAThread] static void Main() { // Create a String to hold the database connection string. string sdwConnectionString = @"Data Source=IE1ADTBD5ZL1S\;Initial Catalog=RecommendEngine;Integrated Security=True"; // Create a connection SqlConnection sdwDBConnection = new SqlConnection(sdwConnectionString); // Open the connection sdwDBConnection.Open(); //Create a String to hold the query. string query = @"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')"; //Create a SqlCommand object and pass the constructor the connection string and the query string. SqlCommand queryCommand = new SqlCommand(query, sdwDBConnection); DataTable dt = new DataTable(); new SqlDataAdapter(queryCommand).Fill(dt); dt.TableName = "TableName"; dt.WriteXml(@"C:/SampleFile.xml"); // Close the connection sdwDBConnection.Close(); } } } </code></pre>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USSarangArd
    1. USSarangArd
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POGenerate following XML using C#
      1. This table or related slice is empty.
      1. VTDownMod
    2. VO
      singulars
      1. POGenerate following XML using C#
      1. USAbbas
      1. VTFavorite
    1. CO-1 for not showing any effort to try it yourself.
      singulars
      1. POGenerate following XML using C#
      1. USDaniel Hilgarth
    2. COHey I did try to solve the issue. But it didn't come out effectively.
      singulars
      1. POGenerate following XML using C#
      1. USSarangArd
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload