This post provides my implementation of returning recordsets with SOAP messaging. A more complete article is Returning ADO Recordsets with SOAP Messaging by Chris Dengler.

Recordset to be retrieved

A DLL on the web server took certain parameters and returned a recordset containg employee information. My web service offered the facility to retrieve this information in the form of a simple XML representation.

Schema Generated by the Wizard

The SDL wizard generated the following schema for the response with the return type defaulted to string:

  <element name='GetEmployeeResponse'>
    <type>
      <element name='return' type='dt:string'/>
    </type>
  </element>

Edited Schema

I adjusted the schema to my own structure so that the SOAPPackager on the client end knew that it was receiving XML rather than the string:

  <element name='GetEmployeeResponse'>
    <type>
      <element name='return' type='GetEmployeeStruct'/>
      <element name='pvarResults' type='dt:string'/>
      <element name='pvarErrors' type='dt:string'/>
    </type>
  </element>
  <element name='GetEmployeeStruct'>
    <type>
      <element name='RECORD' type='GetEmployeeRecStruct'/>
    </type>
  </element>
  <element name='GetEmployeeRecStruct'>
    <type>
      <element name='intEmpID' type='dt:integer'/>
      <element name='strUserName' type='dt:integer'/>
      <element name='strFirstName' type='dt:string'/>
      <element name='strSurname' type='dt:string'/>
      <element name='strEmail' type='dt:string'/>
     </type>
  </element>

Adjusted ASP Function

The ASP function was then adjusted to pass an ADO recordset into the DLL call as opposed to a string, and a simple XML representation of that database is returned by the web service.

  '____________________________________________________________________
  ' function used by the listener
  Public Function GetEmployee (ByVal plngUserID)
    Dim objGetEmployee
    Set objGetEmployee = Server.CreateObject("MyPackage.MyClass")
    Dim objRS		' record set object to pass to function
    ' create disconnected recordset
    Set objRS = Server.CreateObject("ADODB.Recordset")
    ' call function - function will return objRS as a disconnected recordset
    Call objGetEmployee.GetEmployee(plngUserID, objRS)
    ' convert the recordset into XML format and send back to client
    GetEmployee = GetXMLFromRS(objRS)
    ' clean up objects
    Set objRS = Nothing
    Set objGetEmployee = NOTHING
  End Function
  '____________________________________________________________________
  ' function used to convert the recordset to XML
  Function GetXMLFromRS(ByVal objRS)
    ' declare variables
    Dim strXML					' output XML string
    Dim objField				' fields in the record set
    Dim strFieldName		' name of the field
    Dim strFieldValue		' value of the current field in the current record
    ' create outer element RECORDSET
    strXML = "<RECORDSET>" & vbCrLf
    ' loop through the recordset, adding each record and values to string
    Do While NOT objRS.EOF
      ' add new RECORD element for each record
      strXML = strXML & vbTab & "<RECORD>" & vbCrLf
      ' loop through the fields and add each with value to xml string
      For Each objField In objRS.Fields
        strFieldName = objField.Name
        strFieldValue = CDATAIt(objRS(strFieldName))
        strXML = strXML & vbTab & vbTab & "<" & strFieldName & ">" _
        & strFieldValue & "</" & strFieldName & ">" & vbCrLf
      Next
      ' close RECORD element
      strXML = strXML & vbTab & "</RECORD>" & vbCrLf
      objRS.MoveNext
    Loop
    ' close outer element RECORDSET and return XML string
    strXML = strXML & "</RECORDSET>" & vbCrLf
    GetXMLFromRS = strXML
    ' GetXMLFromRS = "Test"
  End Function

  '____________________________________________________________________
  ' function to add cdata tags to XML information
  Function CDATAIt(ByVal strXMLVal)
	  CDATAIt = "<![CDATA[" & strXMLVal & "]]>"
  End Function

Client-side

The result returned is straight-forward XML, for example:

<return>
  <RECORDSET>
    <RECORD>
      <EmployeeID><![CDATA[4]]></EmployeeID>
      <UserName><![CDATA[msalmon]]></UserName>
      <FirstName><![CDATA[Matt]]></FirstName>
      <LastName><![CDATA[Salmon]]></LastName>
      <Email><![CDATA[[email protected]]]></Email>
    </RECORD>
    <RECORD>
      <EmployeeID><![CDATA[4]]></EmployeeID>
      <UserName><![CDATA[example]]></UserName>
      <FirstName><![CDATA[Example]]></FirstName>
      <LastName><![CDATA[Test]]></LastName>
      <Email><![CDATA[[email protected]]]></Email>
    </RECORD>
  </RECORDSET>
</return>