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..

And you read the... - Asked By Robbe Morris on 13-Aug-01 01:09 PM

...Web Services Server To Server With XMLHttp???

It shows you how to reload the XML formatted recordset for ADO.

Error - Asked By Michael Clinton on 13-Aug-01 01:22 PM

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

Hmmm... - Asked By Robbe Morris on 13-Aug-01 01:34 PM

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.
Even if I put Option 0 I get same error - Asked By Michael Clinton on 13-Aug-01 01:45 PM
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.
Sure thing... - Asked By Robbe Morris on 13-Aug-01 01:55 PM
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.
OK here is my current code - Asked By Michael Clinton on 13-Aug-01 02:01 PM
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"
That's what I thought... - Asked By Robbe Morris on 13-Aug-01 02:07 PM
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 assume SQL 2000?? - Asked By Michael Clinton on 13-Aug-01 02:08 PM
I have SQL 7 right now.. and I tried it.. it didn't like it.
Huh? - Asked By Robbe Morris on 13-Aug-01 02:09 PM
.
I ran the code using XMLREQUEST=1 - Asked By Michael Clinton on 20-Jul-14 12:41 AM
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

Just run serverB.asp - Asked By Robbe Morris on 13-Aug-01 02:17 PM
And look at the XML output.

Maybe there is a problem with the returned XML.
That is the problem.. - Asked By Michael Clinton on 13-Aug-01 02:26 PM
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     ÿ   ÿ        ÿÿ
Hmmm... - Asked By Robbe Morris on 13-Aug-01 02:28 PM
I don't have the 4.0 parser.  Let me see if one of our partners does.  Perhaps he has seen a similar bug.
Check your QueryString - Asked By Peter Bromberg on 13-Aug-01 03:00 PM
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
PS - When using MSXML 4.0 - Asked By Peter Bromberg on 13-Aug-01 03:04 PM
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"