Parser for complex XML file

Question

I'm having trouble parsing an XML file retrieved from an API in Excel. I can successfully retrieve the data set (below), but the solutions I've found to format each field to its own cell into a table format haven't worked, I think because of the way the XML is formatted.

Each XML will look something like what's below. There may be multiple Message IDs that need to be extracted independently. (This was another issue I was having. The space in "Message ID" was throwing all sorts of errors.)

Here's the solution I was trying to work with: How to parse XML using vba

<?xml version="1.0" encoding="utf-8"?>
<methodResponse><item><methodName><![CDATA[legacy.message_stats]]>    </methodName><responseData><message_data>
<message id="9999">
    <message_subject><![CDATA[50% rabatt på alla kort! Skicka ett personligt    julkort i år!]]></message_subject>
    <date_sent>2015-09-13 19:15:48</date_sent>
    <message_notes><![CDATA[50% Off Holiday Cards, SE]]></message_notes>
    <withheld_total>         0</withheld_total>
    <globally_suppressed>         0</globally_suppressed>
    <suppressed_total>         0</suppressed_total>
    <bill_codes><![CDATA[emc_0914_HOLIDAYCARDS_SE]]></bill_codes>
    <sent_total>     15195</sent_total>
    <sent_total_html>     15195</sent_total_html>
    <sent_total_plain>         0</sent_total_plain>
    <sent_rate_total>100.00</sent_rate_total>
    <sent_rate_html>100.00</sent_rate_html>
    <sent_rate_plain>0.00</sent_rate_plain>
    <delivered_total>     15060</delivered_total>
    <delivered_html>     15060</delivered_html>
    <delivered_plain>         0</delivered_plain>
    <delivered_rate_total>99.11</delivered_rate_total>
    <delivered_rate_html>99.11</delivered_rate_html>
    <delivered_rate_plain>0.00</delivered_rate_plain>
    <bounced_total>       135</bounced_total>
    <bounced_html>       135</bounced_html>
    <bounced_plain>         0</bounced_plain>
    <bounced_rate_total>0.89</bounced_rate_total>
    <bounced_rate_html>0.89</bounced_rate_html>
    <bounced_rate_plain>0.00</bounced_rate_plain>
    <invalid_total>        42</invalid_total>
    <invalid_rate_total>0.28</invalid_rate_total>
    <has_dynamic_content>0</has_dynamic_content>
    <has_delivery_report>0</has_delivery_report>
    <link_append_statement/>
    <timezone/>
    <message_name><![CDATA[0914_HOLIDAYCARDS_SE]]></message_name>
    <binding>franklin</binding>
    <ftf_forwarded>0</ftf_forwarded>
    <ftf_signups>0</ftf_signups>
    <ftf_conversion_rate>0.00</ftf_conversion_rate>
    <optout_total>         6</optout_total>
    <optout_rate_total>0.04</optout_rate_total>
    <clicked_total>        90</clicked_total>
    <clicked_unique>        81</clicked_unique>
    <clicked_rate_unique>4.82</clicked_rate_unique>
    <clicked_rate_aps>1.11</clicked_rate_aps>
    <opened_total>      2166</opened_total>
    <opened_unique>      1681</opened_unique>
    <opened_rate_unique>11.16</opened_rate_unique>
    <opened_rate_aps>1.29</opened_rate_aps>
    <campaign_name>2015_09_September_Emails</campaign_name>
    <campaign_id>260147</campaign_id>
    <campaign_type>C</campaign_type>
    <included_groups>
      <segment id="1182637"><![CDATA[i18n_TM_sendlist]]></segment>
    </included_groups>
    <included_smartlists>
      <segment id="1430586"><![CDATA[[i18n] SE/Swedish (sv-SE)]]></segment>
    </included_smartlists>
    <excluded_groups>
    </excluded_groups>
    <excluded_smartlists>
      <segment id="1532985"><![CDATA[Exclusion Segment -- Excluding Yahoo subscribers outside of 90 day engagement window]]></segment>
      <segment id="1428935"><![CDATA[New Customer Holdout]]></segment>
      <segment id="1419789"><![CDATA[OptOuts]]></segment>
      <segment id="1434420"><![CDATA[Reintegration: AOL]]></segment>
      <segment id="1436595"><![CDATA[Reintegration: Cloudmark]]></segment>
    </excluded_smartlists>
    <attributes>
        <attribute name="Discount" id="1942"><![CDATA[Item-Level]]></attribute>
    </attributes>
    <link id="43864062">
            <url><![CDATA[http://www.zazzle.se/about/contactus?pm=EARLYSPECIAL&CMPN=emc_0914_HOLIDAYCARDS_SE_fContactUs&rf=238625503972086358]]></url>
            <url_name><![CDATA[Kontakta Oss]]></url_name>
            <url_display><![CDATA[Kontakta Oss]]></url_display>
            <has_name>1</has_name>
            <clicked_unique_total>0</clicked_unique_total>
            <clicked_total_plain>0</clicked_total_plain>
            <clicked_unique_plain>0</clicked_unique_plain>
            <clicked_total_html>0</clicked_total_html>
            <clicked_unique_html>0</clicked_unique_html>
            <roi_conversions>0</roi_conversions>
            <roi_total>0.00</roi_total>
            <roi_average>0.00</roi_average>
    </link>

Here's the VBA I'm using to try to pull a field:

Sub GetXML()

''Pull Raw XML
Dim mainWorkBook As Workbook
Set mainWorkBook = ActiveWorkbook

Dim xmlInput As String
xmlInput = mainWorkBook.Worksheets("XML").Range("A1").Value

Dim oXmlHttp As MSXML2.XMLHTTP60
Set oXmlHttp = New MSXML2.XMLHTTP60

oXmlHttp.Open "POST", "api.url", False, "UserName", "Password"
oXmlHttp.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
oXmlHttp.setRequestHeader "Connection", "Keep-Alive"
oXmlHttp.setRequestHeader "Accept-Language", "en"

oXmlHttp.send xmlInput

mainWorkBook.Worksheets("Output").Range("A1").Value = oXmlHttp.responseText


''Parse Fields
Dim objXML As MSXML2.DOMDocument

Set objXML = New MSXML2.DOMDocument

If Not objXML.LoadXML(oXmlHttp.responseText) Then
    Err.Raise objXML.parseError.ErrorCode, , objXML.parseError.reason
End If

 Dim point As IXMLDOMNode
Set point = objXML.FirstChild

Debug.Print point.SelectSingleNode("message_subject").Text


End Sub

Answer 1

 <message id="999999">

Here the element tag name is "message" and has an attribute named "id" with a value of 999999. There's supposed to be a space there and it shouldn't cause any parse errors