asked on

Passing an XML file to a stored procedure - Follow Up question

Hello again -

This is and add onto my previous post: https://www.experts-exchange.com/questions/21089175/Passing-an-XML-file-to-a-stored-procedure.html#11826991

Is there any way to take the XML file that I am receiving from the third party and directly use in the stored procedure to insert the credit without saving the XML file on the server? Here is what I have so far, but I am receiving 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.

--------------------------------------------------------------------------------

XML document must have a top level element. Error processing resource 'https://m2lcallcenter.com/creditRequest.asp'.

<%
Dim objSrvHTTP
Dim objXMLDocument
set objSrvHTTP = Server.CreateObject ("MSXML2.ServerXMLHTTP")
set objXMLDocument = Server.CreateObject ("Msxml2.DOMDocument.4.0")

objXMLDocument.async= false
objXMLDocument.resolveExternals = false
objXMLDocument.loadXML strXML

objSrvHTTP.open "POST","https://secure.fncs.com/credit_test/standard/mismo", false
objSrvHTTP.send objXMLDocument
Response.ContentType = "text/xml"

' Response.Write objSrvHTTP.responseXML.xml

' Additional part ... grab the file and run the proc

Const URL = "https://secure.fncs.com/credit_test/standard/mismo"
Dim HTTPRequest
Dim XmlResponse
Dim cmd

Set HTTPRequest = Server.CreateObject("Msxml2.ServerXMLHTTP")
HTTPRequest.open "GET", URL, false
HTTPRequest.send

If HTTPRequest.Status <> 200 Then
Response.Write HTTPRequest.Status
Else
XmlResponse = HTTPRequest.ResponseText
Set cmd = Server.CreateObject("ADODB.Command")
cmd.ActiveConnection = MM_M2LCallcenter_STRING
cmd.CommandText = "sp_CreditLiability"
cmd.CommandType = 4
cmd.Parameters.Append cmd.CreateParameter("@XMLDoc", 201, 1, 2147483647, XmlResponse)
cmd.Execute,,128
Set cmd = Nothing
End If
Set HTTPRequest = Nothing

I tried replacing the Const URL = "https://secure.fncs.com/credit_test/standard/mismo"
with ConstURL = responseXML
didn't work.

I would really rather not save the XML files on the server....is that something that I will need to do??

Thank you again!

Susan

suzywal

ASKER

To clarify, I changed the:

Const URL = "https://m2lcallcenter.com/response.xml"
Dim HTTPRequest
Dim XmlResponse
Dim cmd

Set HTTPRequest = Server.CreateObject("Msxml2.ServerXMLHTTP")
HTTPRequest.open "GET", URL, false
HTTPRequest.send

To

Const URL = "https://secure.fncs.com/credit_test/standard/mismo"
Dim HTTPRequest
Dim XmlResponse
Dim cmd

Set HTTPRequest = Server.CreateObject("Msxml2.ServerXMLHTTP")
HTTPRequest.open "GET", URL, false
HTTPRequest.send

When I successfully received the response file from the https POST, I uploaded it to the server to test the stored procedure. Now I would like to have it automatically grab the response.xml file from the third party and load it into the database.

I am trying to explain as best as I possibly can.....acperkins, thanks for your patience!

Susan

SoftEng007

if you're using the sp_xmlfile_preparedocument
proc in your credit proc then you pass a filename.
if your using acperkins method..then you are passing a text stream into to the proc and it should work.
is it possible that your stripping of the root node before sending on to the server?

Anthony Perkins

I am still not following you: You first do a POST and receive back an Xml document and then turn around and do a GET. You should only need to do one or the other, I suspect a POST. In other words, it should be as simple as this:

<%
Dim objSrvHTTP
Dim objXMLDocument
set objSrvHTTP = Server.CreateObject ("MSXML2.ServerXMLHTTP")
set objXMLDocument = Server.CreateObject ("Msxml2.DOMDocument.4.0")

objXMLDocument.async= false
objXMLDocument.resolveExternals = false
objXMLDocument.loadXML strXML

objSrvHTTP.open "POST","https://secure.fncs.com/credit_test/standard/mismo", false
objSrvHTTP.setRequestHeader "Content-type", "text/xml"
objSrvHTTP.send objXMLDocument

If objSrvHTTP.Status <> 200 Then
Response.Write objSrvHTTP.Status
Else
XmlResponse = objSrvHTTP.ResponseText
Set cmd = Server.CreateObject("ADODB.Command")
cmd.ActiveConnection = MM_M2LCallcenter_STRING
cmd.CommandText = "sp_CreditLiability"
cmd.CommandType = 4
cmd.Parameters.Append cmd.CreateParameter("@XMLDoc", 201, 1, 2147483647, XmlResponse)
cmd.Execute,,128
Set cmd = Nothing
End If
Set HTTPRequest = Nothing

But let's take it one step at a time, this error: "XML document must have a top level element." means that the Xml document you receive is not well-formed.

Let's address that first.

Also, this thread would be better served (have more response) in the ASP Topic Area.

ASKER CERTIFIED SOLUTION

Anthony Perkins

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Anthony Perkins

P.S. I suspect your error "XML document must have a top level element." is coming from the fact that strXML may be empty.

suzywal

ASKER

Got it to work!!! You have been SO much help. I can't thank you enough!

Do you have any recommendations for ASP/XML books? I obviously need some help!

I Have gone some of the Wrox ASP books (ASP for beginners and ASP Databases)....

Thank you Again!

Susan

Anthony Perkins

>>I Have gone some of the Wrox ASP books (ASP for beginners and ASP Databases)....<<
Those are in general pretty good.

As far as SQL Server 2000 and Xml is concerned the one I reference is WROX's "SQL Server 2000 XML" by Paul J. Burke et al. It is fairly old, but has a good couple of chapters on FOR XML and OPENXML