SQL: Fetching XML From SQL Stored-Procedure Using an ADO Stream in Visual Basic

(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 read

Private 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