Xml/Xslt - SOAP, and usable recordset - Asked By Michael Clinton on 20-Jul-14 12:40 AM

I would like to see example code on SOAP between servers and the returned XML be used as a recordset.  I have code from this site getting the servers to talk to each other, but how do I use the returned data.  It is like I have a request for data from another server.. I get back the data.. and doh!! what do I do now??
The purpose of this all is be able to use the returned XML as a recordset..

...Web Services Server To Server With XMLHttp???
It shows you how to reload the XML formatted recordset for ADO.

Getting XML...
msxml4.dll error '80004005' 
An invalid character was found in text content. 
/soap/test1/servera.asp, line 58 
line 58 is  oADORec.Open oXML

I haven't tested the code with the 4.0 parser but it should work.
Was the xml that was loaded into the oXML object actually the output of serverB ADO recordset?  
You may want to post both sides of the code.

In your servera.asp code.. I changed it to 
ServerB.asp?XMLREQUEST=0
and I still got the same error.
And by the way, thank for the quick responses and thanks for the info that you offer to developers.

I had a previous developer have problems as well.  He had some coding errors.  Go ahead and post both sides of the code.  I've got a meeting to go to but will review it when I get back.

servera.asp code
<%
    Function GetXML()
      Dim oXMLHttp
      Dim oADORec
      Dim sXML
      Dim URL
      Set oXMLHttp = Server.CreateObject("MSXML2.ServerXMLHTTP")
      URL= "http://wclintonm01/soap/test1/ServerB.asp?XMLREQUEST=0"
      oXMLHttp.open "GET", URL, false
      oXMLHttp.send()                   ' Send the request.
      if oXMLHttp.status = 200 Then 
          sXML = oXMLHttp.responseText   ' Retrieve from serverB.
         if ucase(trim(mid(sXML,1,6))) <> "XMLERR" then
            GetXML = LoadXMLRecordSet(sXML)
         else
            GetXML = "Err: " & Trim(mid(sXML,7,40))
	 end if
     else
         GetXML = "Could not get XML data."
     end if
	  set oXMLHttp = nothing
  end function
  Function LoadXMLRecordSet(sXMLStream)
       dim oADORec
       dim oXML
       dim oCol
       dim sH
        sH = "<tr><td colspan=2>RecordSet Results"
        sH = sH & "<br><Br></td></tr>" & vbcrlf
        Set oADORec = Server.CreateObject("ADODB.Recordset")
		set oXML = Server.CreateObject("MSXML2.DOMDocument")
       ' Load the formatted string into an XMLdom object.
        oXML.loadXML sXMLStream   
       ' ADO supports a direct load from an XMLdom object.
         oADORec.Open oXML
        ' Iterate through the recordset to see what we've got.  
        While not oADORec.EOF
             for each oCol in oADORec.Fields
                 sH = sH & "<tr><td>" & oCol.Name & "</td>"
                 sH = sH & "<td>" & oCol.Value & "</td></tr>"
             next 
             sH = sH & "<tr><td colspan=2><br></td></tr>"
             oADORec.MoveNext
        wend
        Set oADORec = nothing
	Set oXML = nothing
        LoadXMLRecordSet = sH
  End function
  ' Let's write the results to the browser.
  response.write "Getting XML...<br><br>"
  response.write "<table>"
  response.write GetXML()
  response.write "</table>"
  response.write ""
%>
serverb.asp code
<%
  dim moADOCon
  dim moADORec
  dim msADOConStr
 ' Your database connection string.
   msADOConStr = Application("DSN")
  Sub ReturnXML()
	dim sXML
	dim sXMLType
  ' Your system's object for indicating content type (if applicable).
	Response.ContentType="text/xml"                    
   ' Your system's object to handle query strings.
        sXMLType = Trim(Request.QueryString("XMLREQUEST"))  
	Select Case sXMLType
         Case "0"  ' Return back straight XML.
               ' Use your database tool or business logic to 
               ' create an XML stream.  Then write your XML 
               ' stream output just as you would sending it
               ' to the browser.  In ASP, it would look like this:
               sXML = sXML & "<?xml version='1.0'?>"
               sXML = sXML & "<VENDORS>"
               sXML = sXML & "  <VENDOR>"
               sXML = sXML & "    <ID>1</ID>"
               sXML = sXML & "    <LNAME>Company 1</LNAME>"
               sXML = sXML & "  </VENDOR>"
               sXML = sXML & "  <VENDOR>"
               sXML = sXML & "    <ID>2</ID>"
               sXML = sXML & "    <LNAME>Company 2</LNAME>"
               sXML = sXML & "  </VENDOR>"
               sXML = sXML & "</VENDORS>"
               Response.write sXML
         Case "1"  ' Return back XML formatted specifically for ADO
               Set moADOCon = Server.CreateObject("ADODB.Connection")
               Set moADORec = Server.CreateObject("ADODB.Recordset")
	       moADOCon.open msADOConStr
               Set moADORec.ActiveConnection = moADOCon
               ' ADO supports writing directly to the ASP Response
	       ' object from the ADO recordset.
               moADORec.Open "select * from test9"

The XMLRequest=0 returns back standard XML.  ADO cannot load this directly.  Try XMLRequest=1 and make sure your serverB code is returning the recordset properly.  ADO can only load an XML string that originated from a Recordset.Save method
   moADORec.Save Response,adPersistXML 
This the same mistake the other guy made.  Take a look at the differences in the XML returned back from the XMLRequest=1 XML string.  You'll see a lot of custom tags specifically for ADO.

I have SQL 7 right now.. and I tried it.. it didn't like it.

.

I am connecting to pubs database and table authors
"select * from authors"
and I got
Getting XML...
msxml4.dll error '80004005'
An invalid character was found in text content.
/soap/test1/servera.asp, line 57
but if I directly run page I get this error
The XML page cannot be displayed
Cannot view XML input using style sheet. Please correct the error and then click the Refresh button, or try again later.
--------------------------------------------------------------------------------
An Invalid character was found in text content. Line 1, Position 15

And look at the XML output.
Maybe there is a problem with the returned XML.

I am running this on 2K workstation and SQL 7.
if I run serverb.asp?
http://wclintonm01/soap/test1/ServerB.asp?XMLREQUEST=1
I get this..hehe

TG!     ¶’ò?²Ï# ª _þX
        ! Ò­cöëÏ°ã ª ?    	 	       ÿÿÿÿJ  ¾"µÈó\Î­å ª Dw=       †      "       I        Á<Ž¶ëmЍö ª _þX  ' €
 
  a u _ i d     ÿ   ÿ        ÿÿ- €
   a u _ l n a m e  (   ÿ   ÿ        ÿÿ- €
   a u _ f n a m e     ÿ   ÿ        ÿÿ' €
   p h o n e     ÿ   ÿ        ÿÿ+ €
   a d d r e s s  (   ÿ   ÿ   h     ÿÿ% €
   c i t y     ÿ   ÿ   h     ÿÿ' €
   s t a t e     ÿ   ÿ   x     ÿÿ# €
   z i p     ÿ   ÿ   x     ÿÿ- €
 	  c o n t r a c t     ÿ   ÿ        ÿÿ

I don't have the 4.0 parser.  Let me see if one of our partners does.  Perhaps he has seen a similar bug.

Make sure you have ?XMLREQUEST=1 on your querystring The code example you posted has =0
Should be:
URL= "http://localhost/ServerB.asp?XMLREQUEST=1" 
Also, make sure if you don't have a reference to the ADO TypeLibrary that you DEFINE adPersistXML so at the top of the serverb.asp page:
const adPersistXML=1

Also, if you are using MSXML 4.0 be aware that this parser (and all versions that follow) DOES NOT support version-independent ProgIDs. So any instantiation of a coClass from the parser will need to be version - specific, e.g.:
Dim xmlhttp
Set xmlhttp = Server.CreateObject("MSXML2.XMLHTTP.4.0")
Also, you CANNOT mix version-specific with non-version-specific ProgIds. The parser does not support "Replace Mode"