Consuming Web Service and DOMDocument to show result

Hi.

Thank you for reading my post.

I am new to running Web Services in Excel and using XML so I have tried to google me to the result but I am having some difficulties.

What I basicly want to do is call my Web Service with VBA and print the result out in the cells. First row for header and then the results under.

Here is the code that calls the web service. Some code have been replaced with "removed" for security reasons.

Please Login or Register  to view this content.

The saved XML looks something like this:

Please Login or Register  to view this content.

The XML doesnt exacly format like that. It all comes in one big pile of mess after <SQL_Query_dbhtlResult><NewDataSet> Then the whole result comes in one BIG block and then the end is formated like above.

More like this:

Please Login or Register  to view this content.

So this might be the problem. Because when I try to go through the nodes with the following code:

Please Login or Register  to view this content.

On the first run when i step through the code, it goes inside the for loop and prints out:
version="1.0" encoding="utf-8"
And then when I step further and it goes to the next element it goes inside the for loop and prints out the WHOLE result in one big chunk :/
then it goes out of the for loop and end sub.

This post got very big but I really hope someone can help me with this.
All help is much appreciated.

Thank you for reading

It's because you're looping through child nodes of the xml document, of which there are 2. XML is hierarchchal so you either need to navigate down through the levels each in turn or use xpath to skip to the data you want. This makes sense if you format the xml properly:

HTML Code: 

As you can see, there are 2 child elements, the xml info tag and the soap envelope. The data you want it a child of a child of a child etc Envelope > Body > response ...

Does that make sense?

Thanks for the response.

How do I format the XML properly? I think I understand how the child of child etc works.
But if you look at this picture from the locals window.
The whole result is in one child node so I have really no idea how to go through 1 node at the time so I can look for each field and then only get the result so I can format it properly in a table.

Capture.jpg

As you can see from the length of that child is 50 168 characters, so the whole result is in here.

Originally Posted by Kyle123

It's because you're looping through child nodes of the xml document, of which there are 2. XML is hierarchchal so you either need to navigate down through the levels each in turn or use xpath to skip to the data you want. This makes sense if you format the xml properly:

HTML Code: 

As you can see, there are 2 child elements, the xml info tag and the soap envelope. The data you want it a child of a child of a child etc Envelope > Body > response ...

Does that make sense?

How can I navigate through the layers with xpath?
Can i save the XML file another way? So it devides it up in child nodes?
Capture2.jpg Here is how the XML files look when I save it.

Would really appriciate it someone could give me some example code snippets that works with my example.

Is it none who can help me with this?

The xml is already in child nodes, I suggest you read up a little on how xml works. The format you have it in is fine, you simply need to read it properly. Like this:

Please Login or Register  to view this content.