Loading malformed xml into MSXML2.DOMDocument

[duluter]

Hi, all.

There is a website I visit that allows users to query a database and retrieve the results in a downloadable xml file. What I am trying to do is save the contents of the xml file to a MSXML2.DOMDocument object in VBA so I can work with the data.

The problem is that the xml generated by the site is not well formed, so it doesn't load into my object correctly and I can't work with it (the object essentially remains empty as far as I can tell). If I go to the website in a browser, manually save the xml file to disk, open it in a text editor, edit it to make it well-formed, and then try to load that into a DomDocument variable, all is well. But I can't figure out how to do all this programmatically within VBA.

If the xml were already well-formed, here's the working code:

Dim xmldoc As MSXML2.DOMDocument
Set xmldoc = New MSXML2.DOMDocument

'Load entire Document before moving on
xmldoc.async = False

'Don't Validate
xmldoc.validateOnParse = False

xmldoc.Load ("http://www.examplesite.com/requestthatreturnssomexml")


What I need is a way to get the xml from the URL into a string variable, edit it, then load that cleaned xml into the xmldoc variable.

How can I grab the xml returned by my URL and put it into a string variable?


Thanks,

Duluter

[JP2112]

Have you tried something like

[vba]Function GetYourXML(url As String) As String

Dim xml As Object
Set xml = CreateObject("MSXML2.XMLHTTP")

xml.Open "POST", url, False
xml.Send

GetYourXML = xml.responsetext

End Function[/vba]

[duluter]

Thank you for the quick suggestion, JP2112. When I try that, I get an "Access is denied" error on the "xml.send" line.

Any thoughts?


Duluter

[JP2112]

Never seen that before. Check your system32 folder for other versions of msxml#.dll and use an alternate.

Are you doing this at work? Maybe there's a firewall preventing data exchange at this level.

[duluter]

Thank you for the extra tip, JP. I know next to nothing about working with XML, so I don't completely follow. Here's what I have:

-Home network, no special firewall that I know of. Maybe someone could point me to another URL that I could test the MSXML2.XMLHTTP method with? Maybe the problem is with the URL/Server I'm trying to access the xml data from?

-I'm using Windows XP, Office 2007. In my system32 folder, I have the following dlls:
msxml2
msxml2r
mxxml3
msxml3r
msxml6
msxml6r
msxml
msxmlr


-In my VBA project, I set the following reference: "Microsoft XML, v3.0". Though I could have chosen others, including:
version 2.0
v2.6
v5.0
v6.0
I don't know why I chose v3.0. I think it's because I don't know what I'm doing. Should I try the others? Or would this not have anything to do with my problem? (EDIT1: I tried v6.0 and still had the same "Access is denied" error, so I'm guessing that's not it.)


Thanks again.

EDIT2: Aren't I going to end up with the same problem even if I do get this "Access is denied" issue worked out? Isn't the program going to try to interpret/parse the xml when it is retrieved from the server? Isn't it going to choke on the malformed xml?

[JP2112]

Since we're declaring As Object, there's no need for the early bound reference to any of the MSXML libraries.

Maybe your URL is malformed? Try calling this function using an inocuous URL, like http://www.google.com/

[VBA]Function GetISGDUrl(url As String) As String
Dim xml As Object
Set xml = CreateObject("MSXML2.XMLHTTP")

xml.Open "POST", "http://is.gd/api.php?longurl=" & url, False
xml.Send

GetISGDUrl = xml.responsetext

End Function[/VBA]

i.e. Debug.Print GetISGDUrl("http://www.google.com/")

If it works, then post the URL you're using so we can test it.

--JP

[duluter]

JP:

OK, using http://www.google.com/ worked. So the problem must be with my URL.

FYI, when I paste my URL into a browser, the request takes a few seconds and then I am prompted by the browser to save the xml file. I don't know if that is information that helps or not.

I'd rather not broadcast the URL because that would identify what line of work I'm in. I'm overly paranoid about online privacy for no good reason! JP, could I email or pm the URL to you through your member profile?

Thanks again--you've been very helpful.

Duluter

[JP2112]

That's probably why. Feel free to pm me with the URL.

[duluter]

I just wanted to let readers of this thread know that JP took a look at my URL and, with JP's permission, here is JP's PM to me:

There's no way to automate this using the technique I showed you. What you could do is set a timer in the macro, then use SendKeys to save the file. Or, see if there's another way the file can be provided to you.

So, thank you to JP. If I make any other breakthroughs on this, I'll post back.


D.