Parsing XML response data in VBA

Question

I am calling the stockquote webservice and trying to parse through the data that is returned but nothing I try is working

Set objHTTP = CreateObject("Microsoft.XMLHTTP")
objHTTP.Open "GET", "http://www.webservicex.net/stockquote.asmx/GetQuote?symbol=AAPL", False
objHTTP.send


Dim xDoc As MSXML2.DOMDocument
Dim xDoc2 As MSXML2.IXMLDOMNodeList

Set xDoc = New MSXML2.DOMDocument
        xDoc.LoadXML (objHTTP.responseXML.XML)

I know everything works up to this point and I can look at the Xdoc object in the debugger and see that the xml was loaded.

How do I access the individual nodes after this? the sample xml looks like this "<string xmlns="http://www.webserviceX.NET/"><StockQuotes><Stock><Symbol>AAPL</Symbol><Last>446.5345</Last> <Date>5/30/2013</Date><Time>10:55am</Time><Change>+1.5845</Change></Stock></StockQuotes></string>"

This link may get you started stackoverflow.com/questions/12689352/… — user857521
– user857521, Commented May 30, 2013 at 15:43

Martin Honnen · Accepted Answer · 2013-05-30 16:40:01Z

It seems the response is an XML document with a single root element containing an escaped XML document:

<?xml version="1.0" encoding="utf-8"?>
<string xmlns="http://www.webserviceX.NET/">&lt;StockQuotes&gt;&lt;Stock&gt;&lt;Symbol&gt;AAPL&lt;/Symbol&gt;&lt;Last&gt;448.9501&lt;/Last&gt;&lt;Date&gt;5/30/2013&lt;/Date&gt;&lt;Time&gt;12:17pm&lt;/Time&gt;&lt;Change&gt;+4.0001&lt;/Change&gt;&lt;Open&gt;445.65&lt;/Open&gt;&lt;High&gt;449.77&lt;/High&gt;&lt;Low&gt;444.51&lt;/Low&gt;&lt;Volume&gt;5691335&lt;/Volume&gt;&lt;MktCap&gt;421.4B&lt;/MktCap&gt;&lt;PreviousClose&gt;444.95&lt;/PreviousClose&gt;&lt;PercentageChange&gt;+0.90%&lt;/PercentageChange&gt;&lt;AnnRange&gt;385.10 - 705.07&lt;/AnnRange&gt;&lt;Earns&gt;41.896&lt;/Earns&gt;&lt;P-E&gt;10.62&lt;/P-E&gt;&lt;Name&gt;Apple Inc.&lt;/Name&gt;&lt;/Stock&gt;&lt;/StockQuotes&gt;</string>

So you need to load the first document and then access its content and parse it as XML with a second document:

Dim doc1 As MSXML2.DOMDocument60
Set doc1 = New MSXML2.DOMDocument60
doc1.async = False
If doc1.load("http://www.webservicex.net/stockquote.asmx/GetQuote?symbol=AAPL") Then
  Dim doc2 As MSXML2.DOMDocument60
  Set doc2 = New MSXML2.DOMDocument60
  If doc2.loadXML(doc1.DocumentElement.text) Then
    Dim value
    value = doc2.selectSingleNode("//Last").text
  Else
    'handle doc2.parseError here
  End If
Else
  ' handle doc1.parseError her
End If

Untested but should give you an idea.

Collectives™ on Stack Overflow

Parsing XML response data in VBA

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related