title: Call rest web service from SQL Server 2008 using stored procedure
body: <p>I have an xml created from a SQL query through a stored procedure. I need to call a REST web service with that xml and get a response. I am using SQL Server 2008. Is there a way to do it from the stored procedure?</p>
  
  <p>I keep seeing links to use SQL CLR to access the web service. In those links, I need to create a SQL Server project / .NET framework. I do not see those templates / options in my Visual Studio 2008 or SQL Server Management Studio. </p>
  
  <p>Is there a way to call this https rest web service directly from the stored procedure?</p>
  
  <p>===============</p>
  
  <p>I am using the SQL CLR to do this. But somehow the xml is not getting passed right. I get a 500 error. Could you please see if there is anything wrong with this code.</p>
  
  <pre><code>public static void SampleWSPost(SqlString weburl, out SqlString returnval)
      {
          string url = Convert.ToString(weburl);
          string feedData = string.Empty;
          ASCIIEncoding encoding = new ASCIIEncoding();
          try
          {
              HttpWebRequest request = null;
              HttpWebResponse response = null;
              Stream stream = null;
              StreamReader streamReader = null;
  
  
              XmlTextReader reader = new XmlTextReader("D:\\CXmls\\crmXml.xml");
              XmlDocument xmlDoc = new XmlDocument();
              xmlDoc.Load(reader);
              reader.Close();
  
              request = (HttpWebRequest)WebRequest.Create(url);
              CredentialCache mycache = new CredentialCache();
              mycache.Add(new Uri(url), "Basic", new NetworkCredential("username", "password"));   
              request.Credentials = mycache;
              request.Method = "POST";
              request.ContentType = "text/xml";
  
              string data = xmlDoc.InnerXml;
              byte[] bytedata = Encoding.UTF8.GetBytes(data);
              Stream newStream = request.GetRequestStream();
              newStream.Write(bytedata, 0, bytedata.Length);
              newStream.Close();
  
              response = (HttpWebResponse)request.GetResponse();
              stream = response.GetResponseStream();
              streamReader = new StreamReader(stream);
              feedData = streamReader.ReadToEnd();
              response.Close();
              stream.Dispose();
              streamReader.Dispose();
  
          }
  
          catch (Exception ex)
          {
              SqlContext.Pipe.Send(ex.Message.ToString());
          }
          returnval = feedData;
      }
  </code></pre>