title: How do I return XML from a Stored Procedure?
body: <p>I created a Stored Procedure that returns XML and I would like to also return that XML in a method I created.</p>
  
  <p>I'm having two issues.  First, after doing some searching, it is not advised to use <code>.ExecuteScalar();</code> because it truncates strings over 2033 characters.</p>
  
  <p>So, I found a function called <code>ExecuteXMlReader()</code>, but in Visual Web Developer 2010 Express that runs on .NET 4.0 (C#) it is throwing the error  <code>"System.Data.SqlClient.SqlCommand' does not contain a definition for 'ExecuteXMlReader' and no extension method 'ExecuteXMlReader' accepting a first argument of type 'System.Data.SqlClient.SqlCommand' could be found"</code></p>
  
  <p>Here is my stored procedure:</p>
  
  <pre><code>CREATE PROCEDURE dbo.GETReport
      (@ReportDate date)
  AS
  SELECT * FROM ReportTbl
  WHERE ReportDate = @ReportDate
  for xml auto, elements
  
  set nocount on;
  
  RETURN
  </code></pre>
  
  <p>Here is my method:</p>
  
  <pre><code>using System.Data;
  using System.Data.SqlClient;
  
  ...
  
          //connect        
          SqlConnection conn = new SqlConnection("Data Source=localhost; User Id=foo; Password=foo; Initial Catalog=Database1");
          conn.Open();
  
          //create command
          SqlCommand cmd = new SqlCommand("dbo.GETReport", conn);
          cmd.Parameters.AddWithValue("@ReportDate", "3/24/2011"); 
          cmd.CommandType = CommandType.StoredProcedure;
  
          DataReader rd = cmd.ExecuteXMlReader(); //this is where error is occuring
          //also, it is throwing an error for DataReader as well saying there is no
          //type of namespace with that name
          rd.Read();
  
          string s = rd.ReadOuterXml(); //also dont know if this is how i should return the XML
  </code></pre>
  
  <p>Second, in addition to the <code>ExecuteXMLReader()</code> issue, I don't know if returning a string is the proper way of returning XML in the first place... Is there another object type I should convert it to??  Or another function I should use??</p>
  
  <p>Thank you in advance!!</p>