title: How to get data from SQL Server database in Xml format and from that Xml fill DataSet
body: <p>I am trying to get data from SQL Server in xml format and from that xml fill a dataset.</p>
  
  <p>Here is my query: </p>
  
  <pre><code>SELECT * 
  FROM TblAcademicYear 
  FOR XML RAW('AcademicYear'), ELEMENTS;
  </code></pre>
  
  <p>This query give me following output :</p>
  
  <pre><code> &lt;AcademicYear&gt;
      &lt;AcademicYearId&gt;3&lt;/AcademicYearId&gt;
      &lt;AcademicYearName&gt;دو ‌ہزار ‌پندرہ&lt;/AcademicYearName&gt;
      &lt;StartingYear&gt;2015-01-01&lt;/StartingYear&gt;
     &lt;EndingYear&gt;2015-12-31&lt;/EndingYear&gt;
     &lt;Comments&gt;دو ‌ہزار ‌پندرہ ‌کا ‌تعلیم ‌سال&lt;/Comments&gt;
    &lt;RCO&gt;2014-07-02&lt;/RCO&gt;
    &lt;UserID&gt;2&lt;/UserID&gt;
  &lt;/AcademicYear&gt;
  </code></pre>
  
  <p>And my C# code is :</p>
  
  <pre><code>SqlConnection conn = new SqlConnection(ConnectionString);
  
  SqlCommand cmd = new SqlCommand();
  System.Xml.XmlReader xmlreader;
  
  try
  {
      cmd.Connection = conn;
      conn.Open();
      cmd.CommandText = _Query;
      xmlreader = cmd.ExecuteXmlReader();
      conn.Close();
  
      DataSet ds = new DataSet();
      dt.Columns.Add("AcademicYearId", typeof(string));
      dt.Columns.Add("AcademicYearName", typeof(string));
      dt.Columns.Add("StartingYear", typeof(string));
      dt.Columns.Add("EndingYear", typeof(string));
      dt.Columns.Add("Comments", typeof(string));
      dt.Columns.Add("RCO", typeof(string));
      dt.Columns.Add("UserID", typeof(string));
  
      ds.Tables.Add(dt);
  
      ds.ReadXml(xmlreader);
  }
  catch (Exception ex)
  {
      throw ex;
  }
  finally
  {
      if (conn.State != ConnectionState.Closed)
      {
         conn.Close();
      }
  }
  </code></pre>
  
  <p>and the above code run perfectly but it does not give me the data </p>
  
  <p>Any help would be appreciated .....</p>