Classic ASP - Stream XML from SP to Browser

[Don Miller]

Could someone please point me to an example of how to directly stream the
XML output from an SP (FOR XML EXPLICIT) to a browser (without saving it as
a file or anything)? Thanks.

This is what I thought would work but I'm missing something (basic or
everything).

Const adCmdStoredProc = &H0004
Const adInteger = 3
Const adParamInput = &H0001
Const adExecuteStream = 1024

Response.Clear
Response.ContentType = "text/xml;charset=utf-8"

Dim objCmd 'As ADODB.Command
Set objCmd = Server.CreateObject("ADODB.Command")
With objCmd
.ActiveConnection = Application("ConnectString")
.CommandType = adCmdStoredProc
.CommandText = "plist_ReturnsXML" 'sp uses FOR XML EXPLICIT
.Parameters.Append .CreateParameter("id", adInteger, adParamInput)
.Parameters("id") = 5
End With

Dim objStream 'As ADODB.Stream
Set objStream = Server.CreateObject("ADODB.Stream")
objStream.Open
With objCmd
.Properties("Output Stream").Value = objStream
.Execute , , adExecuteStream
End With
objStream.Position = 0
Set objCmd = Nothing

Response.Write objStream

 

[Don Miller]

Ah,

Response.Write objStream.ReadText

made it work.

 

[Martin Honnen]

Const adCmdStoredProc = &H0004
Const adInteger = 3
Const adParamInput = &H0001
Const adExecuteStream = 1024

Response.Clear
Response.ContentType = "text/xml;charset=utf-8"

Dim objCmd 'As ADODB.Command
Set objCmd = Server.CreateObject("ADODB.Command")
With objCmd
.ActiveConnection = Application("ConnectString")
.CommandType = adCmdStoredProc
.CommandText = "plist_ReturnsXML" 'sp uses FOR XML EXPLICIT
.Parameters.Append .CreateParameter("id", adInteger, adParamInput)
.Parameters("id") = 5
End With

Dim objStream 'As ADODB.Stream
Set objStream = Server.CreateObject("ADODB.Stream")
objStream.Open
With objCmd
.Properties("Output Stream").Value = objStream

You might not need the ADODB.Stream at all, try whether you can directly
write to the ASP Response object by setting
.Properties("Output Stream").Value = Response
Untested however.

 

[Don Miller]

Thanks, even better! Here's my code (with stuff added to Response to get a
fresh copy every time)

Const adCmdStoredProc = &H0004
Const adInteger = 3
Const adParamInput = &H0001
Const adExecuteStream = 1024

With Response
.Clear
.ExpiresAbsolute = DateAdd("d",-1,date)
.CacheControl = "private"
.AddHeader "PRAGMA","no-cache"
.ContentType = "text/xml;charset=utf-8"
End With

Dim objCmd 'As ADODB.Command
Set objCmd = Server.CreateObject("ADODB.Command")
With objCmd
.ActiveConnection = Application("ConnectString")
.CommandType = adCmdStoredProc
.CommandText = "plist_ReturnsXML"
.Parameters.Append .CreateParameter("id", adInteger, adParamInput)
.Parameters("id") = 5
.Properties("Output Stream").Value = Response
.Execute , , adExecuteStream
End With