title: sql clr c# function to make rest api call
body: <p>I am trying to write a SQL CLR C# function/store procedure to get data from an api server but I'm not getting proper results. Probably I never wrote and never done this. I have searched a lot and found the following code, but this returns data in a full page format where as I want only data in xml format so that I can process it in SQL Server. Also I am passing an url and username password which is Ok, how can I add more parameters to this?</p>
  
  <p>//code</p>
  
  <pre><code>using System;
  using System.Data;
  using System.Data.SqlClient;
  usig System.Data.SqlTypes;
  using System.Collections;
  using System.Globalization;
  
  // For the SQL Server integration
  using Microsoft.SqlServer.Server;
  
  // Other things we need for WebRequest
  using System.Net;
  using System.Text;
  using System.IO;
  
  public partial class Functions
  {
      // Function to return a web URL as a string value.
      [Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read)]
      public static SqlString GET(SqlString uri, SqlString username, SqlString passwd)
      {
          // The SqlPipe is how we send data back to the caller
          SqlPipe pipe = SqlContext.Pipe;
          SqlString document;
  
          // Set up the request, including authentication
          WebRequest req = WebRequest.Create(Convert.ToString(uri));
  
          if (Convert.ToString(username) != null &amp; Convert.ToString(username) != "")
          {
               req.Credentials = new NetworkCredential(Convert.ToString(username), Convert.ToString(passwd));
          }
  
          ((HttpWebRequest)req).UserAgent = "CLR web client on SQL Server";
  
          // Fire off the request and retrieve the response.
          // We'll put the response in the string variable "document".
          WebResponse resp = req.GetResponse();
  
          Stream dataStream = resp.GetResponseStream();
          StreamReader rdr = new StreamReader(dataStream);
          document = (SqlString)rdr.ReadToEnd()
  
          // Close up everything...
          rdr.Close();
          dataStream.Close();
          resp.Close();
  
          // .. and return the output to the caller.
          return (document);
      }
  }
  </code></pre>
  
  <p>This code returns this result:</p>
  
  <pre><code>&lt;html&gt;        &lt;head&gt;&lt;link rel="alternate" type="text/xml" href="/api.asmx?disco" /&gt;        &lt;style type="text/css"&gt;          BODY { color: #000000; background-color: white; font-family: Verdana; margin-left: 0px; margin-top: 0px; }    #content { margin-left: 30px; font-size: .70em; padding-bottom: 2em; }    A:link { color: #336699; font-weight: bold; text-decoration: underline; }    A:visited { color: #6699cc; font-weight: bold; text-decoration: underline; }    A:active { color: #336699; font-weight: bold; text-decoration: underline; }    A:hover { color: cc3300; font-weight: bold; text-decoration: underline; }    P { color: #000000; margin-top: 0px; margin-bottom: 12px; font-family: Verdana; }    pre { background-color: #e5e5cc; padding: 5px; font-family: Courier New; font-size: x-small; margin-top: -5px; border: 1px #f0f0e0 solid; }    td { color: #000000; font-family: Verdana; font-size: .7em; }    h2 { font-size: 1.5em; font-weight: bold; margin-top: 25px; margin-bottom: 10px; border-top: 1px solid #003366; margin-left: -15px; color: #003366; }    h3 { font-size: 1.1em; color: #000000; margin-left: -15px; margin-top: 10px; margin-bottom: 10px; }    ul { margin-top: 10px; margin-left: 20px; }    ol { margin-top: 10px; margin-left: 20px; }    li { margin-top: 10px; color: #000000; }    font.value { color: darkblue; font: bold; }    font.key { color: darkgreen; font: bold; }    font.error { color: darkred; font: bold; }    .heading1 { color: #ffffff; font-family: Tahoma; font-size: 26px; font-weight: normal; background-color: #003366; margin-top: 0px; margin-bottom: 0px; margin-left: -30px; padding-top: 10px; padding-bottom: 3px; padding-left: 15px; width: 105%; }    .button { background-color: #dcdcdc; font-family: Verdana; font-size: 1em; border-top: #cccccc 1px solid; border-bottom: #666666 1px solid; border-left: #cccccc 1px solid; border-right: #666666 1px solid; }    .frmheader { color: #000000; background: #dcdcdc; font-family: Verdana; font-size: .7em; font-weight: normal; border-bottom: 1px solid #dcdcdc; padding-top: 2px; padding-bottom: 2px; }    .frmtext { font-family: Verdana; font-size: .7em; margin-top: 8px; margin-bottom: 0px; margin-left: 32px; }    .frmInput { font-family: Verdana; font-size: 1em; }    .intro { margin-left: -15px; }                   &lt;/style&gt;        &lt;title&gt;   API Web Service  &lt;/title&gt;&lt;/head&gt;      &lt;body&gt;        &lt;div id="content"&gt;          &lt;p class="heading1"&gt;API&lt;/p&gt;&lt;br&gt;                              &lt;span&gt;            &lt;p class="intro"&gt;Click &lt;a href="api.asmx"&gt;here&lt;/a&gt; for a complete list of operations.&lt;/p&gt;
  </code></pre>