By Lance Robinson - Technical Evangelist, /n software.

This users guide will go through some of the basic steps that one might take in order send an HTTP request to a SQL Server. The request will instruct the SQL server to return XML, which we will parse and display neatly in a grid. 

There are a variety of reasons to access SQL over HTTP.  Using HTTP as a transport offers similar benefits to traditional web services, and this example can easily be extended to integrate features such as SSL security and digital certificates for example. While there are many reasons to communicate with SQL this way, this article will only focus on the implementations, communication, and data extraction, and not the reasoning.

Requirements

• SQLXML installed on top of SQL Server 2000.
• The WebForm and XMLp components of IP*Works!. (download) 

In this article we will use IP*Works! .NET Edition and VB.NET for demonstration purposes and coding techniques.

Configuration

The first thing we need to do is understand how to make the http request to the SQL server. The best resource for this information is of course the help documentation for SQLXML itself. Using this documentation, we learn that we must run a SQLXML configuration application to configure SQLXML with IIS, creating a virtual directory. After that we'll see that the request is sent just like any other HTTP request, on port 80, with a query string containing the SQL query, ie:

"SELECT CustomerId, CompanyName FROM Customer"

http://localhost/sql?sql=select%20CustomerId,
	%20CompanyName%20from%20Customers%20FOR%20XML%20AUTO

The resulting response is basic unvalidated XML which includes the results of the query. Try typing this in your browser to see the output for yourself (replace http://localhost/sql with your own virtual directory).

WebForm

We'll use the WebForm component (rather than HTTP) because it can easily and automatically URL encode the query string information. In order to perform Basic Authentication, set the User and Password properties of the component, ie:

 Webform1.User = txtUser.Text
 Webform1.Password = txtPassword.Text

First, set the Encoding property of the WebForm component. This property allows you to select between 3 values: Query string (GET), Multipart/Form Data (POST), or Url Encoded (POST). Since the query information for communicating with SQLXML is passed on the query string (within the URL), specify Query String as the encoding type.

Then, add the variables required in the query string using the AddFormVar method (syntax: AddFormVar(varname, varvalue)). As we see from the SQLXML documentation, the name of the variable to set for the SQL query is "sql",  and the value of that variable is the query to be executed. We must then append the statement "FOR XML AUTO;" to instruct the server how to format the data so that we can parse the response (for more info on data formatting see the SQLXML documentation).  Then call the Submit method of the webform control to connect and issue the request.

 Webform1.Encoding = nsoftware.IPWorks.WebformEncodings.encQueryString 
 Webform1.AddFormVar("sql", txtQuery.Text & " FOR XML AUTO;") 
 Webform1.SubmitTo(txtServer.Text) 

Now the request has been received by the server and the server sends back the XML response. In order to catch this response, we can use either the Webform Transfer event, or the TransferredData property. Right now, we will use the TransferredData property. This property is ready and available as soon as the synchronous SubmitTo method is finished.  If there is a problem during execution, a standard trappable exception will fire. 

Parsing

Ok, so we have the XML data - now we will use some method to parse this xml and display it in a grid or listview. I am going to demonstrate using the XMLp component to parse the relevant data and split it into a listview.

We will dynamically create as many listview columns as there are search terms. For example, we are searching for CustomerId and CompanyName - so there will be two columns in the resulting listview - CustomerId and CompanyName.

The XML data that is returned is raw, invalid XML with no parent nodes. For simplicity we'll add our own parent node by simply prepending and appending a root element around the data.

'Input the XML data received to the XMLparser component  
Xmlp1.InputText = "" & Webform1.TransferredData & ""
lvwOutput.Columns.Add("Number", 50, HorizontalAlignment.Left)
 
'Go to the first element and see what type of data we are getting,  
'Set up the appropriate column headers  
Dim i, j As Integer
 
Xmlp1.XPath = "/root/[1]"  '[1] is the first child
For i = 1 To Xmlp1.AttrCount
'For every attribute of this first child, we create a matching listview column
  lvwOutput.Columns.Add(Xmlp1.AttrName(i), (lvwOutput.Width - 50) / _
    Xmlp1.AttrCount, HorizontalAlignment.Left)
  lvwOutput.Columns(lvwOutput.Columns.Count - 1).Text = Xmlp1.AttrName(i)
Next i
 
'Now, go back to the root
Xmlp1.XPath = "/root"
 
'And for every single child of the root, extract the info  
'and add it to the listview
For i = 1 To Xmlp1.XChildren
  Xmlp1.XPath = "/root/[" & CStr(i) & "]"
  lvwOutput.Items.Add(CStr(i))
  For j = 1 To Xmlp1.AttrCount
    lvwOutput.Items(lvwOutput.Items.Count - 1).SubItems.Add(Xmlp1.AttrValue(j))
  Next j
Next i
			

In this article we have reviewed an easy way to communicate with SQL server over HTTP using IP*Works! and SQLXML. This simple example shows how easy it is to use, and how flexible the IP*Works! components are.  For more information about IP*Works! or any other /n software product please visit our homepage at www.nsoftware.com.

We appreciate your feedback.  If you have any questions, comments, or suggestions about this article please contact our support team at kb@nsoftware.com.