复杂XML文件的解析器

Question

我在解析从Excel中的API检索的XML文件时遇到问题。我可以成功地检索数据集（如下所示），但我找到的将每个字段格式化为其自身单元格的表格式的解决方案没有奏效，我认为这是因为XML的格式化方式。

每个XML看起来都类似于下面的内容。可能需要独立提取多个消息ID。（这是我遇到的另一个问题。“消息ID”中的空格引发了各种错误。）

下面是我尝试使用的解决方案：如何使用vba解析XML

<?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>

下面是我尝试拉取字段时使用的VBA：

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">

这里的元素标签名称是“消息”，并且有一个名为“id”的属性，值为999999。那里应该有一个空格，不会导致任何解析错误