One of the most frustrating things to me is seeing developers doing new things, but in "old" ways. A classic example is one you may have seen yourself: Here we have a sophisticated client - side application that runs in Internet Explorer, we use XML Data Islands and XSLT transforms and parameters, templates, and we even use XMLHTTP from the client. And along with this we may have page upon page that needs to get a recordset from SQL Server - sometimes many recordsets, and bring them into the page as XML so we can do all our "new kind of cool stuff" with it. Well, that's great.

But what do we do? We use ADOon the client, make a connection to SQL Server, get the recordsets back to the client page, and then tediously loop through the recordsets with heavy - duty, extra - slow string-manipulation code -- just in order to manually build XML Documents out of them!     -- DUH -- !

You simply don't need to do this. If you are going to use ADO on the client, you can use an ADO Stream query and get the data back AS XML right from the git-go! And if we don't have or don't want to use ADO on the client, then we can use XMLHTTP (you can even go as far back as "Microsoft.XMLHTTP" --works just fine) and SEND the XML Query to a "server listener page" that simply executes it and writes the required XML Document to the response object. The advantage of this is that this one simple server page can serve results to dozens of different client - side pages, each constructing it's own unique "FOR XML" queries.

Let's take a quick look at the basics. Not only is this much faster, it's certainly more extensible, because SQL Server is well - equipped to provide your results as XML right out of the box, and customize it to your liking as well. First I'll show a page that gets an XML - based recordset with ADO on the client. Then I'll show how to modify it with a "server page" and use XMLHTTP instead. You'll see how, by bringing your query back to the client in a data island, you can keep reasonably large sets of data in the client, available for various kinds of manipulation, XPATH queries, and display, with only a single trip to the server! And, you'll be able to cut way down on the amount of unncessary, tedious, string- manipulating client - side code.

The key "nugget of code in the client page will always look like this:

Dim adoConn
Set adoConn = Server.CreateObject("ADODB.Connection")
Dim sConn
sConn= "Provider=SQLOLEDB;Data Source=(local);User ID=sa;Password=;Initial Catalog=zipcodes"
adoConn.ConnectionString = sConn
adoConn.CursorLocation = adUseClient
adoConn.Open
Dim adoCmd
Set adoCmd = Server.CreateObject("ADODB.Command")
Set adoCmd.ActiveConnection = adoConn
Dim sQuery
if Request.Querystring("city") = "" then
Response.Write "Must have ?City=choice on querystring."
Response.end
end if

sQuery = "<ROOT xmlns:sql='urn:schemas-microsoft-com:xml-sql'><sql:query>SELECT * FROM ZIPCODES WHERE CITY LIKE '" & Request.querystring("city") & "%' FOR XML AUTO</sql:query></ROOT>"
Dim adoStreamQuery
Set adoStreamQuery = Server.CreateObject("ADODB.Stream")
adoStreamQuery.Open
adoStreamQuery.WriteText sQuery, adWriteChar
adoStreamQuery.Position = 0
adoCmd.CommandStream = adoStreamQuery
adoCmd.Properties("Output Stream") = Response
Response.write "<XML ID='ZipsDataIsle'>"
adoCmd.Execute , , 1024
Response.write "</XML>"

What we are doing above is using an ADO Stream query in an OpenXML format that SQL Server 2000 understands perfectly. We set the output of our result stream (which will be a string of well-formed XML) to the Response Object and simply write it out to the browser in between opening and closing <XML> tags and Presto, we have a data island. You can view source on the client page and you will see the XML Data Island sitting right there waiting for you to use it. You can put as many data islands on your page as you want in this manner, as long as the ID attribute of each <XML> tag is unique (by the way, these "<XML>" tags are HTML tags). (Don't worry, complete source code is in the download below and it's for the Northwind Database which installs with SQL Server).

Now the second method is where we put the ADO Stream query code in a generic server "listener" page that can accept a custom query from any page you want. All it does is execute the query and return the XML Result to the ASP Response Stream. As long as your query is good, you get back what you want. It can be "FOR XML AUTO", "FOR XML AUTO, ELEMENTS", whatever. If you are not familiar with this, it's all in SQL Server Books Online.

Here is sample code for the server page:

<%
Dim xmlDoc
Set xmlDoc = Server.CreateObject("MSXML2.DOMDocument.3.0")
xmlDoc.async=false
xmlDoc.Load Request
Dim adoConn
Set adoConn = Server.CreateObject("ADODB.Connection")
Dim sConn
sConn= "Provider=SQLOLEDB;Data Source=(local);User ID=sa;Password=;Initial Catalog=zipcodes"
adoConn.ConnectionString = sConn
adoConn.CursorLocation = adUseClient
adoConn.Open
Dim adoCmd
Set adoCmd = Server.CreateObject("ADODB.Command")
Set adoCmd.ActiveConnection = adoConn
Dim sQuery
sQuery = xmlDoc.xml
Dim adoStreamQuery
Set adoStreamQuery = Server.CreateObject("ADODB.Stream")
adoStreamQuery.Open
adoStreamQuery.WriteText sQuery, adWriteChar
adoStreamQuery.Position = 0
adoCmd.CommandStream = adoStreamQuery
adoCmd.Properties("Output Stream") = Response
adoCmd.Execute , , 1024
Set adoConn=Nothing
Set adoStreamQuery=Nothing
Set adoCmd = Nothing
%>

And here is how we would use this from the client to perform the same operation as in the first sample above:

sQuery = "<ROOT xmlns:sql='urn:schemas-microsoft-com:xml-sql'><sql:query>SELECT * FROM ZIPCODES WHERE CITY LIKE '" & Request.querystring("city") & "%' FOR XML AUTO</sql:query></ROOT>"
Dim xmlhttp
set xmlhttp=createObject("MSXML2.XMLHTTP")
xmlhttp.Open "POST", "http://localhost/test/zipcodeserver.asp", false
xmlhttp.Send(sQuery)
Response.write "<XML ID='ZipsDataIsle'>"
Response.Write xmlhttp.responseText
Response.write "</XML>"
Set xmlhttp=Nothing

Same result. More extensible. You can do it as many times in a page as you need to. You can do it in response to events, button clicks, whatever. And you can re-use the server listener page for all kinds of different pages. Note one thing - for testing purposes, I have my listener page in A DIFFERENT IIS APPLICATION DIRECTORY than the client page. You'll probably want to do this in deployment as well.

The code in the download below does some really neat formatting into a scrollable table and uses some XPATH to display a nice "Detail" table below the results table when you click on the name of one of the results. Hope this helps.

Download the code that accompanies this article

Peter Bromberg is an independent consultant specializing in distributed .NET solutionsa Senior Programmer /Analyst at in Orlando and a co-developer of the EggheadCafe.com developer website. He can be reached at pbromberg@yahoo.com