title: Xml Raw data returned from SQL Server 2005 not correct when calling from .NET
body: <p>I have a stored procedure that returns XML raw data <code>FOR XML RAW</code> something like the following format:</p>
  
  <pre><code>&lt;row 
      codelistid="1" codelistname="LOCATION" 
      codeid="1557" codename="Hors Ile de France" languageid="1" /&gt;
  </code></pre>
  
  <p>When I run the stored procedure in Management Studio 1765 rows worth of data is returned but when I call the procedure from my C# code it appears to be around half of that 882. It seems that if two rows have the same codeid then only one is returned</p>
  
  <p>I am using the xmlreader to return the XML row by row and then appending each row as an XElement to my results XDocument.</p>
  
  <p>Here is how I am retrieving the data:</p>
  
  <pre><code> using (SqlConnection conn = new SqlConnection(con))
          {
              XDocument results = new XDocument(
             new XElement("results"));
  
              using (SqlCommand cmd = conn.CreateCommand())
              {
                  cmd.CommandType = CommandType.StoredProcedure;
                  cmd.CommandText = "MyStoredProc";
                  conn.Open();
                  var count = 0;
                  using (XmlReader reader = cmd.ExecuteXmlReader())
                  {
  
                      while (reader.Read())
                      {
  
                          results.Root.Add(XElement.Parse(reader.ReadOuterXml()));
                          count += 1;
  
                      }
                  }
  
                  return results;
  
              }
          }
  </code></pre>
  
  <p>If I change the SPROC to output the data rather than XML and read it via SQLDataReader it seems to work fine.</p>
  
  <p>Anyone have any ideas on why this is happening as I would ideally like the database to return the XML?</p>
  
  <p>Thanks in advance.</p>