Summary The following snippet explains how to use the ADO stream object to retrieve your FOR XML stored procedures.

So often you read about how to retrieve your FOR XML queries with the new SQL 7/2000 HTTP settings.  But this method exposes your database and does not have tight security.

Using the ADO Stream object is really fast and is hidden in your VB/ASP code

The following code comes from my good friend and co-worker Brandon Driessen who is excellent at using the new FOR XML syntax.

Firstly set up your ADO command object, adding your parameters, etc.

To set up your ADO stream, to return your XML string from SQL 2000, use the following:

Set oStream = New ADODB.Stream
oStream.Charset = windows-1252
oStream.Open
oCommand.Properties(Output Stream) = oStream
               
oCommand.Execute , , adExecuteStream
oStream.Position = 0
sXML = oStream.ReadText
oStream.Close

You use the 'Properties' property to indicate to the Command object that you want the stored procedure to be passed back into a Stream object, instead of a Recordset.

To prevent unusual characters, you will notice that we are setting which encoding the ADO stream should returned for the XML format.

Additional information

Set oStream = New ADODB.Stream oStream.Charset = "windows-1252" oStream.Open oCommand.Properties("Output Stream") = oStream oCommand.Execute , , adExecuteStream oStream.Position = 0 sXML = oStream.ReadText oStream.Close