如何用Excel VBA读取含转义XML节点的XML文件？

问题说明 #

使用Excel VBA读取XML文件时，<strReport>标签内的转义XML内容（如<NewDataSet>这类转义后的标签）被识别为纯文本，无法当作独立子节点遍历，需要解析这部分内容并查找目标节点。

解决方案 #

核心思路是将<strReport>中的转义文本反转义，再加载为新的XML DOM对象，就能像处理普通XML一样遍历节点：

• 定位到目标<strReport>节点，提取其文本内容
• 对文本做反转义处理：把<转为<，>转为>，还原成标准XML格式
• 创建新的MSXML2.DOMDocument60对象，加载反转义后的字符串
• 用常规方法遍历这个新XML的节点即可

完整VBA代码示例 #

Sub ParseEscapedXML()
    Dim xmlFileName As String
    xmlFileName = "你的XML文件路径" '替换为实际文件路径
    
    Dim mainXML As New MSXML2.DOMDocument60
    Dim escapedXML As New MSXML2.DOMDocument60
    Dim strReportNode As MSXML2.IXMLDOMNode
    Dim escapedContent As String
    
    '加载主XML文件
    mainXML.async = False
    mainXML.validateOnParse = False
    If Not mainXML.Load(xmlFileName) Then
        MsgBox "主XML加载失败: " & mainXML.parseError.reason
        Exit Sub
    End If
    
    '用XPath快速定位strReport节点，替代逐层遍历
    Set strReportNode = mainXML.SelectSingleNode("//strReport")
    If strReportNode Is Nothing Then
        MsgBox "未找到strReport节点"
        Exit Sub
    End If
    
    '提取转义内容并反转义
    escapedContent = strReportNode.Text
    escapedContent = Replace(escapedContent, "&amp;lt;", "<")
    escapedContent = Replace(escapedContent, "&amp;gt;", ">")
    
    '加载反转义后的XML字符串
    escapedXML.async = False
    escapedXML.validateOnParse = False
    If Not escapedXML.LoadXML(escapedContent) Then
        MsgBox "转义XML加载失败: " & escapedXML.parseError.reason
        Exit Sub
    End If
    
    '示例：读取目标节点值
    Dim bookTypeNode As MSXML2.IXMLDOMNode
    Dim salesNode As MSXML2.IXMLDOMNode
    
    Set bookTypeNode = escapedXML.SelectSingleNode("//BookType")
    If Not bookTypeNode Is Nothing Then
        Debug.Print "BookType: " & bookTypeNode.Text
    End If
    
    Set salesNode = escapedXML.SelectSingleNode("//Sales")
    If Not salesNode Is Nothing Then
        Debug.Print "Sales: " & salesNode.Text
    End If
    
    '示例：遍历所有BookData节点
    Dim bookDataNodes As MSXML2.IXMLDOMNodeList
    Dim bookDataNode As MSXML2.IXMLDOMNode
    
    Set bookDataNodes = escapedXML.SelectNodes("//BookData")
    For Each bookDataNode In bookDataNodes
        Debug.Print "BookX: " & bookDataNode.SelectSingleNode("BookX").Text
        Debug.Print "BookY: " & bookDataNode.SelectSingleNode("BookY").Text
    Next
End Sub

额外说明 #

1. 优先用XPath（SelectSingleNode/SelectNodes）替代多层ChildNodes遍历，代码更简洁高效。
2. 反转义要注意顺序：先处理<这类双重转义字符，再还原XML标签。
3. 加载字符串型XML用LoadXML方法，加载文件用Load方法，不要混淆。

内容的提问来源于stack exchange，提问作者Salam