Post XML to a URL inside Access

[buratti]

So my new project is to create a DB form that processes credit card payments. I already have a merchant account with Intuit. They have a SDK kit that allows you to create your own application and link credit card processing to your existing merchant account. In short, the process is to essentially send ("POST") a XML document containing all the customers credit card and charge info to a URL (sending the request). Then the URL returns a response (also in XML format) with approvial status, auth. code and etc.

I haven't created anything yet, but in researching first I have found that using code similar to whats below I can send the XML (the code is just copied directly from a different forum and has nothing directly to do with my question).

Dim objXmlHttp As Object
Set objXmlHttp = CreateObject("MSXML2.ServerXMLHTTP")
objXmlHttp.Open "POST", webServicePath, False
objXmlHttp.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
 
Dim Response As String
objXmlHttp.send wrt.output
 
'OK status
If objXmlHttp.Status = 200 Then
    Response = objXmlHttp.responseText
End If
Set objXmlHttp = Nothing

Assuming that this is the correct method to "send" the XML(Intuits website states that you must "POST" to a URL), how would I first go about creating/generating the XML inside Access to later POST to the URL? Would I save it in a variable or object, save it in a external text file, or somehow generate it on the spot??? Also after I receive the response, how would I retrieve the data in that XML to use back in my DB application? This is where I'm stuck and need assistance. Any suggestions???

 

[DJkarl]

So my new project is to create a DB form that processes credit card payments. I already have a merchant account with Intuit. They have a SDK kit that allows you to create your own application and link credit card processing to your existing merchant account. In short, the process is to essentially send ("POST") a XML document containing all the customers credit card and charge info to a URL (sending the request). Then the URL returns a response (also in XML format) with approvial status, auth. code and etc.

I haven't created anything yet, but in researching first I have found that using code similar to whats below I can send the XML (the code is just copied directly from a different forum and has nothing directly to do with my question).

Dim objXmlHttp As Object
Set objXmlHttp = CreateObject("MSXML2.ServerXMLHTTP")
objXmlHttp.Open "POST", webServicePath, False
objXmlHttp.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
 
Dim Response As String
objXmlHttp.send wrt.output
 
'OK status
If objXmlHttp.Status = 200 Then
    Response = objXmlHttp.responseText
End If
Set objXmlHttp = Nothing

Assuming that this is the correct method to "send" the XML(Intuits website states that you must "POST" to a URL), how would I first go about creating/generating the XML inside Access to later POST to the URL? Would I save it in a variable or object, save it in a external text file, or somehow generate it on the spot??? Also after I receive the response, how would I retrieve the data in that XML to use back in my DB application? This is where I'm stuck and need assistance. Any suggestions???

Lots of questions here, and a lot to consider.

1) This looks like a viable way to send/receive your data.
2) The Response = objXmlHttp.responseText is where you are going to get the formatted response, you would need to load that Response into an XML document, or parse it yourself. Here is a link on how to parse xml in VBA http://stackoverflow.com/questions/11305/how-to-parse-xml-in-vba
3) Please tell me you are encrypting the CC data before sending over open http, if not you could be in legal trouble depending on where you're operating at. You might want to look and see if the company offers an https alternative as well.

 

[spikepl]

Commerciaol sites allowing upload/input of XML often have a sandbox account/testground for verifying the XML prior to operational use. Also, they often have some API examples. Even if it is in .NET or something else, the few lines required for upload would likely be enough to give you the bits required. Finally, you are unlikely to be the only one with this issue - did you try to google specifically for things like xml uploads to Intuit, check out their FAQ/Support pages, or simply call their technical support?

 

[epon78]

It's pretty easy to create an xml document - barebones you can just do it yourself as plain text.

It comes down to the details - possibly your data can be saved as xml, or you can transform it to xml. XML is a bit confusing at first though, but once you know the basics you can stumble about. MSXML provides a useful library for dealing with xml data in VBA.

 

[epon78]

Sorry, forgot link to MSXML:
http://msdn.microsoft.com/en-us/library/aa468547.aspx

 

[buratti]

Lots of questions here, and a lot to consider.

1) This looks like a viable way to send/receive your data.
2) The Response = objXmlHttp.responseText is where you are going to get the formatted response, you would need to load that Response into an XML document, or parse it yourself. Here is a link on how to parse xml in VBA http://stackoverflow.com/questions/11305/how-to-parse-xml-in-vba
3) Please tell me you are encrypting the CC data before sending over open http, if not you could be in legal trouble depending on where you're operating at. You might want to look and see if the company offers an https alternative as well.

Thanks for the response. i looked over your link and I am pretty good with understanding and learning new techniques but that was greek to me. I will have to read through it a few more times to understand it more. As for encrypting the CC data... Intuit does offer (and the only option BTW) HTTPS

Commerciaol sites allowing upload/input of XML often have a sandbox account/testground for verifying the XML prior to operational use. Also, they often have some API examples. Even if it is in .NET or something else, the few lines required for upload would likely be enough to give you the bits required. Finally, you are unlikely to be the only one with this issue - did you try to google specifically for things like xml uploads to Intuit, check out their FAQ/Support pages, or simply call their technical support?

Yes I am working in a "testing environment". As for the examples... I haven't found anything worth while just yet, but still looking. The documentation they provide is geared toward advanced programmers and generally just state the format of the data to be sent to them. The process of sending it is assumed to already be known by the reader (not in my case though)

It's pretty easy to create an xml document - barebones you can just do it yourself as plain text.

It comes down to the details - possibly your data can be saved as xml, or you can transform it to xml. XML is a bit confusing at first though, but once you know the basics you can stumble about. MSXML provides a useful library for dealing with xml data in VBA.

Thanks, I will read over your link in a little bit.

Other that that, I have gotten a small step closer to figuring it out. I found this nice article here describing how to send an XML file to a URL with VBA. With a few minor modifications I was able to send a testing (external) XML file to the url provided to me and received the correct response I was looking for.

The only issue I have now is how do I modify that XML file (from VBA) each time I need to send a new request? And also the response is in XML formatt, so how do I extract the returned data from the XML "tags" and enter it into my database?

 

[spikepl]

What is the problem in writing the entire XML file from scratch for each request? It's your information just wrapped in some tags. Btw, the free Microsoft XML notepad can perhaps come handy, when checking your XML file.

As to "extracting", you can either parse it properl by traversing all the nodes, using DJKarl's link, or simply use the text functions of VBA to get at the info in the string contained in the objXmlHttp.responseText.

As to your link for sending - nice article. But I would recommend sticking with
"MSXML2.ServerXMLHTTP", because "msxml2.xmlhttp" doesn't time out. If your internet connection is down, or the destination site is unavailable for any reason, then the latter simply hangs your application, and the only way to get out is to kill Access in the Task Manager (CTRL ALT DEL). The remaining code from the link should run just fine when using the MSXML2.ServerXMLHTTP.

 

[buratti]

What is the problem in writing the entire XML file from scratch for each request? It's your information just wrapped in some tags. Btw, the free Microsoft XML notepad can perhaps come handy, when checking your XML file.

As to "extracting", you can either parse it properl by traversing all the nodes, using DJKarl's link, or simply use the text functions of VBA to get at the info in the string contained in the objXmlHttp.responseText.

As to your link for sending - nice article. But I would recommend sticking with
"MSXML2.ServerXMLHTTP", because "msxml2.xmlhttp" doesn't time out. If your internet connection is down, or the destination site is unavailable for any reason, then the latter simply hangs your application, and the only way to get out is to kill Access in the Task Manager (CTRL ALT DEL). The remaining code from the link should run just fine when using the MSXML2.ServerXMLHTTP.

There is nothing wrong with creating the XML from scratch each time, the only problem was that I didn't know how to. I read over DJKarl's link and it got interesting, but then it ended just when they were getting to the good stuff. I need to read over it again and possibly find a follow up or continuation to it find the exact method i can use.
If I were to create the XML each time I give a request, what is the best way of doing so? Would I just hold it in a long string variable, entering bits of data from my database in the appropiate location (nodes)? Or is there some other type of XML "creator" that i may of skipped over when reading throught past articles?

Here comes a dumb question... I hear over and over "you can parse it...". What exactly does that mean?

As for the request method. I will give the other method a try also. i understand your concern about timeout issues. The only thing is that the method in the link I sent worked without a hitch the first time i tried it, but will try the other one too.

 

[DJkarl]

You could use a DOMDocument object to parse the XML.

http://stackoverflow.com/questions/11305/how-to-parse-xml-in-vba

 

[spikepl]

The example you linked to showed a string holding the XML - you can write each line to a file, or create a similar string. It is just plain text, so there's nothing mysterious in that.

As to parsing - that is extracting the information from an XML file. The simple way is simply to grab the string and find what you need using the VBA text functions. Alternativley, parse it properly, by traversing all the nodes. IMHO that would be overkill, if the response sent back from the server is just a line or two.

 

[DJkarl]

I agree with spikepl, if the response is very simple it would be easier and faster to just extract what you need from the response text, if however it is very long and complicated with multiple embedded structures/nodes, then loading it into a navigator of some kind might be useful.

 

[buratti]

Ok here's an update...

This is what I have:

'HTTP variable
Dim myHTTP As MSXML2.XMLHTTP
 
'HTTP object
Set myHTTP = [COLOR=red]CreateObject("MSXML2.ServerXMLHTTP")[/COLOR]
 
'create dom document variable, stores the xml to send
Dim myDom As MSXML2.DOMDocument
 
'Create the DomDocument Object
Set myDom = CreateObject("MSXML2.DOMDocument")
 
'Load entire Document before moving on
myDom.async = False
 
'xml string variable. replace with location if sending from file or URL
Dim myxml As String
myxml = "F:\Joe\Desktop\temp\Request.xml"
 
'loads the xml. change to .Load for file or url
myDom.Load (myxml)
Debug.Print myDom.XML
 
'open the connection
myHTTP.Open "post", "[URL]https://merchantaccount.ptc.quickbooks.com/j/AppGateway[/URL]", False
 
'send the XML
myHTTP.setRequestHeader "Content-type", "application/x-qbmsxml"
myHTTP.send (myDom.XML)
 
'Display the response
MsgBox myHTTP.responseText

when you suggested to use MSXML2.ServerXMLHTTP I am assumming you meant in the
Set myHTTP = CreateObject("MSXML2.ServerXMLHTTP") part as noted in red above. Am I correct, because setting it in the
Dim myHTTP As MSXML2.serverXMLHTTP only returned errors. The rest of the code works fine afterwards though with one small snitch. The response from the url returns the following error: "Invalid content type: application/x-qbmsxml; Charset=UTF-8", but only when using "ServerXMLHTTP" as opposed to "XMLHTTP" (XMLHTTP returns the expected response). Is there any way to clear the content type before resetting it to "content type: application/x-qbmsxml" (the "Charset=UTF-8" seems to be causing the error and it is not in the original XML document)

Problem 2:
Although the request is small enough to just create at runtime using text functions, for learning purposes I want to figure how to enter/modify nodes using VBA. This is a starting point of what I have: (using the same XML document I am sending to the URL for now just for testing)

Sub parseXML()
 
'create dom document variable, stores the xml
Dim myDom As MSXML2.DOMDocument
 
'Create the DomDocument Object
Set myDom = CreateObject("MSXML2.DOMDocument")
 
'Load entire Document before moving on
myDom.async = False
 
'xml string variable. replace with location if sending from file or URL
Dim myxml As String
myxml = "F:\Joe\Desktop\Random Site\Request.xml"
If myDom.Load(myxml) Then
    ' The document loaded successfully.
    DisplayNode myDom.childNodes, 0
Else
    ' The document failed to load.
End If
End Sub
 
Public Sub DisplayNode(Nodes As MSXML2.IXMLDOMNodeList, _
   Indent As Integer)
   Dim xNode As MSXML2.IXMLDOMNode
   Indent = Indent + 2
   For Each xNode In Nodes
      If xNode.nodeType = NODE_TEXT Then
         Debug.Print Space$(Indent) & xNode.parentNode.nodeName & _
            ":" & xNode.nodeValue
      End If
      If xNode.hasChildNodes Then
         DisplayNode xNode.childNodes, Indent
      End If
   Next xNode
End Sub

So the above code successfully "displays" (or can us it to extract) the data in the XML nodes, but how do i go about changing it? So for example if I were to have a so called blank XML document (not literally blank, but the structure set up but the nodes being empty), after loading it I want to "enter" my data in the appropiate nodes then POST it to the URL. The links suggested helped a little, but I still cant figure out the entering or changing of node data. All the help so far is greatly appreciated, but I think I need just a little more.

 

[spikepl]

I don't have much experience uploading - the code I do have downloads. COncerning the header, did you try to kick-out the UTF-thingie and see if it still works ok?

AS to MSXML2.serverXMLHTTP - if you are not convinced that that is the right one to use, I would suggest you try with MSXML2.XMLHTTP with your com disconnected from the interent and see what happens

There is a workaround if you wish to stick to MSXML2.XMLHTTP - you can set
myHTTP.Open "post", "https://merchantaccount.ptc.quickbooks.com/j/AppGateway", True

which fires it of asyncronously. Then you have to make a loop, so your code doesn't move on until you get a server response. There is an example here: http://www.tech-archive.net/Archive/Excel/microsoft.public.excel.programming/2008-01/msg04045.html