(not complete)
This code example returns XML and parameter values from a SQL Server Stored Procedure.
It passes values in, returns some via paramaters, and receives an ADO Stream of XML.
The next step is to create your ADO command object with parameters etc. And supply the command object with an the inited ADO Stream for the results.
Set cmdCommand = New ADODB.Command
cmdCommand.CommandText = "sp_myproc"
cmdCommand.CommandType = adCmdStoredProc
cmdCommand.Parameters.Append cmdCommand.CreateParameter("@ret", adInteger, adParamReturnValue)
cmdCommand.Parameters.Append cmdCommand.CreateParameter("@data1", adInteger, adParamInput, , lngData1)
' init stream
Set cmdCommand.ActiveConnection = mobjConn
Set objStream = StartXMLStream("data")
cmdCommand.Properties("Output Stream").Value = objStream
' execute stored-procedure
cmdCommand.Execute , , adCmdStoredProc + adExecuteStream
' add end-tag to the stream
FinishXMLStream objStream, "data"
Helper Functions
SQL Server does not return XML contained within a document element, so you must create your own surrounding document element. These helper functions create a stream with an XML open tag, and add a close tag to them.
' create a new stream, and write an open tag to it
Private Function StartXMLStream(ByVal strRootName As String) As ADODB.Stream
Dim objStream As ADODB.Stream
' init output stream
Set objStream = New ADODB.Stream
objStream.Charset = "UTF-8"
objStream.Open
objStream.WriteText "<" & strRootName & ">", adWriteChar
' return
Set StartXMLStream = objStream
End Function
' append a close tag to the stream, and rewind it ready to readPrivate Sub FinishXMLStream(ByVal objStream As ADODB.Stream, ByVal strRootName As String)
objStream.Position = objStream.Size
objStream.WriteText "</" & strRootName & ">", adWriteChar
objStream.Position = 0 ' rewind stream ready to read
End Sub
This function converts a stream containing XML into an XML DOM.
Private Function XMLStreamToDOM(ByVal objStream As ADODB.Stream) As MSXML2.DOMDocument
Dim objXML As MSXML2.DOMDocument
' rewind the stream
objStream.Position = 0
' create DOM and load stream
Set objXML = New MSXML2.DOMDocument
If objXML.Load(objStream) Then
' return the DOM
Set XMLStreamToDOM = objXML
Else
Err.Raise 1, "XMLStreamToDOM", "Bad XML from Stream"
End If
End Function


