Getting info out of XML document in VBA

Question

I'm using MSXML2 to load an XML file (a feed from Google Analytics).

I now need to get data out of it, but I can't figure out how.

This is the XML:

<?xml version='1.0' encoding='utf-8'?>
<feed xmlns='http://www.w3.org/2005/Atom' xmlns:dxp='http://schemas.google.com/analytics/2009' xmlns:openSearch='http://a9.com/-/spec/opensearch/1.1/' xmlns:gd='http://schemas.google.com/g/2005' gd:etag='W/"dsdsdsdsdsdsdsd."' gd:kind='analytics#data'>
  <id>http://www.google.com/analytics/feeds/data?ids=ga:2000000&amp;amp;dimensions=ga:campaign&amp;amp;metrics=ga:visits&amp;amp;filters=ga:campaign%3D%3Dcrosspromo&amp;amp;start-date=2010-08-26&amp;amp;end-date=2010-09-09&lt;/id&gt;
  <updated>2010-09-09T05:50:40.705-07:00</updated>
  <title>Google Analytics Data for Profile 2000000</title>
  <link rel='self' type='application/atom+xml' href='https://www.google.com/analytics/feeds/data?max-results=50&amp;amp;end-date=2010-09-09&amp;amp;start-date=2010-08-26&amp;amp;metrics=ga%3Avisits&amp;amp;ids=ga%3A2000000&amp;amp;dimensions=ga%3Acampaign&amp;amp;filters=ga%3Acampaign%3D%3Dcrosspromo' />
  <author>
    <name>Google Analytics</name>
  </author>
  <generator version='1.0'>Google Analytics</generator>
  <openSearch:totalResults>1&lt;
  /openSearch:totalResults&gt;
  <openSearch:startIndex>1</openSearch:startIndex>
  <openSearch:itemsPerPage>50</openSearch:itemsPerPage>
  <dxp:aggregates>
    <dxp:metric confidenceInterval='0.0' name='ga:visits' type='integer' value='5070' />
  </dxp:aggregates>
  <dxp:dataSource>
    <dxp:property name='ga:profileId' value='2000000' />
    <dxp:property name='ga:webPropertyId' value='UA-2000000-1' />
    <dxp:property name='ga:accountName' value='test' />
    <dxp:tableId>ga:2000000</dxp:tableId>
    <dxp:tableName>test</dxp:tableName>
  </dxp:dataSource>
  <dxp:endDate>2010-09-09</dxp:endDate>
  <dxp:startDate>2010-08-26</dxp:startDate>
  <entry gd:etag='W/"CkMEQX47eSp7I2A9Wx5QGUQ."' gd:kind='analytics#datarow'>
    <id>http://www.google.com/analytics/feeds/data?ids=ga:2000000&amp;amp;ga:campaign=crosspromo&amp;amp;filters=ga:campaign%3D%3Dcrosspromo&amp;amp;start-date=2010-08-26&amp;amp;end-date=2010-09-09&lt;/id&gt;
    <updated>2010-09-08T17:00:00.001-07:00</updated>
    <title>ga:campaign=crosspromo</title>
    <link rel='alternate' type='text/html' href='http://www.google.com/analytics' />
    <dxp:dimension name='ga:campaign' value='crosspromo' />
    <dxp:metric confidenceInterval='0.0' name='ga:visits' type='integer' value='5070' />
  </entry></openSearch:totalResults>
</feed>

Then I load the XML and search for the "entry" tag:

Dim objXML As MSXML2.DOMDocument
Set objXML = New MSXML2.DOMDocument
objXML.loadXML strXML
Debug.Print objXML.getElementsByTagName("entry").Item(0).Text

But this outputs:

http://www.google.com/analytics/feeds/data?ids=ga:20000000&ga:campaign=crosspromo&filters=ga:campaign%3D%3Dcrosspromo&start-date=2010-08-26&end-date=2010-09-092010-09-08T17:00:00.001-07:00ga:campaign=crosspromo

Where's the rest of the data? If I try item(1) it fails...

Answer 1

Well Item(1) fails because there is only one "entry" element, and that's at 0.

The .Text property returns all the Text content of the element and all its children. If you look closely, that's what you've got.

If you want something else, you'll have to use the appropriate method off the Item(0).

Check out this question (How to parse XML in VBA).

Answer 2

You have received all the information you asked for. You got the text of each element within the <entry></entry>

lets break it down (this was your output):

First set of text:

http://www.google.com/analytics/feeds/data?ids=ga:20000000&ga:campaign=crosspromo&filters=ga:campaign%3D%3Dcrosspromo&start-date=2010-08-26&end-date=2010-09-09

Second Set of text:

2010-09-08T17:00:00.001-07:00

Third set of text:

ga:campaign=crosspromo

There was nothing else in the entry with output. Therefore you do not see anything else.

Answer 3

Try:

Option Explicit

Public Function LoadXmlDoc() As DOMDocument
    Dim aDoc As DOMDocument
    Set aDoc = New DOMDocument
    aDoc.async = False
    aDoc.Load ("feedfile.xml")
    Set LoadXmlDoc = aDoc
End Function

'' Direct access
Public Sub getEntry()
    Dim aDoc As DOMDocument
    Dim aNode As IXMLDOMNode
    Set aDoc = LoadXmlDoc
    Set aNode = aDoc.SelectSingleNode("//entry")
    Debug.Print aNode.XML
End Sub

'' search for it
Public Sub findEntry()
    Dim aDoc As DOMDocument
    Dim aNode As IXMLDOMNode
    Dim aNodes As IXMLDOMNodeList
    Set aDoc = LoadXmlDoc
    Set aNodes = aDoc.getElementsByTagName("entry")
    For Each aNode In aNodes
        Debug.Print aNode.XML
    Next
End Sub

'' get the entry date
Public Sub getDate()
    Dim aDoc As DOMDocument
    Dim aNode As IXMLDOMNode
    Set aDoc = LoadXmlDoc
    Set aNode = aDoc.SelectSingleNode("//entry/updated")
    Debug.Print aNode.Text
End Sub