XML解析VBA Excel(功能旅行，& MSXML2.DOMDocument) [英] XML parse VBA excel (function trip, & MSXML2.DOMDocument)

问题描述

我需要解析数百个具有相同结构的XML文件，如下所示:

I need to parse hundreds of XML files having all the same structure as follows:

<?xml version="1.0" encoding="UTF-8"?>
  <Concepts>
    <ConceptModel name="food">
      <Filters>
        <Filter type="CC"/>
      </Filters>
      <Queries>
        <Query lang="EN">(cheese, bread, wine)</Query>
        <Query lang="DE">(Käse, Brot, Wein)</Query>
        <Query lang="FR">(fromaige, pain, vin)</Query>
      </Queries>
    </ConceptModel>
  </Concepts>

我已经阅读了以下互联网上的几篇文章和帖子，但无法提出解决方案:

I have read several articles and posts in internet like below but I could not come up with a solution:

• Excel vba解析复杂XML
• 使用VBA解析XML文件

• Excel vba Parse Complex XML
• Parse XML File with VBA

到目前为止，我正在做

Dim oXml As MSXML2.DOMDocument
Set oXml = New MSXML2.DOMDocument
oXml.LoadXML ("C:\folder\folder\name.xml")

Dim Queries As IXMLDOMNodeList
Dim Query As IXMLDOMNode

ThisWorkbook.Sheets(3).Cells(i, 1) = "before loop"

Set Queries = oXml.SelectNodes("/concepts/Queries")

MsgBox "how many Queries " &  Queries.Length

For Each Query In Queries
    ThisWorkbook.Sheets(3).Cells(i, 1) = "Works"
    ThisWorkbook.Sheets(3).Cells(i, 2) = Query.SelectNodes("Query").iTem(0).Text
    i = i + 1
Next

VBA似乎可以理解此代码，但不会读取其中的内容.该循环没有被读取，这意味着(我猜)查询根本没有循环. Msgbox "how many queries"结果为0可以证实这一点.但是实际上有三个查询.有人可以帮我吗?

This code seems to be understood by VBA but it does not read the contents. The loop does not get read, meaning (I guess) that Queries is not looped at all. Which is confirmed by the fact that the Msgbox "how many queries" gives 0 as result. But actually there are three queries. Could someone give me a hand?

第二个问题，我想问

 Dim oXml As MSXML2.DOMDocument

将与

 Dim oXml As MSXML2.DOMDocument60

自从我签入工具/参考"Microsof XML，v6.0"

Since I checked in tools/references "Microsof XML, v6.0"

我认为查询带有标签 可能会引起问题.我添加了以下行:

I thought that the queries having a tag might cause a problem. and I added the follwoing lines:

Dim childs As IXMLDOMNodeList
Set childs = oXml.SelectNodes("/concepts")

MsgBox "childs " & childs.Length

，其结果也为0.我希望3，因为概念有3个子级，即 ConceptModel，Filter 和 Queries .所以，我更加困惑.

which also gives 0 as result. I would expect 3, since concepts has three children, namely ConceptModel, Filter and Queries. So, I am even more puzzled.

推荐答案

尽可能靠近您的OP

我会引起您对一些错误或误解的注意:

I 'd draw your attention to several errors or misunderstandings:

• [1] 无效的.LoadXML语法

.LoadXML("C:\ folder \ folder \ name.xml")和.Load("C:\ folder \ folder \ name.xml")之间有什么区别?

Load需要一个文件路径，然后将文件内容加载到oXML对象中.

Load expects a file path and then loads the file content into the oXML object.

LoadXML不需要文件参数，但是它的实际 XML文本内容必须是格式正确的字符串.

LoadXML doesn't expect a file parameter, but its actual XML text content that has to be a well formed string.

• [2] XML区分大小写，因此需要使用节点的确切文字名称来对其进行寻址: <Query>节点不会被"query" 标识，"ConceptModel" 与"conceptmodel" 不同.

• [2] XML distinguishes between lower and upper case, therefore nodes need to be addressed by their exact literal names: the <Query> node wouldn't be identified by "query", "ConceptModel" isn't the same as "conceptmodel".

第二个问题，我想问 Dim oXml As MSXML2.DOMDocument 将与相同 Dim oXml As MSXML2.DOMDocument60， 自从我签入工具/参考"Microsof XML，v6.0"以来?

As second issue I would like to ask if Dim oXml As MSXML2.DOMDocument would be the same as Dim oXml As MSXML2.DOMDocument60, since I checked in tools/references "Microsof XML, v6.0"?

不，不是. -请注意，前一个声明会默认加载3.0版. 但是，绝对希望获得6.0版(如今任何其他版本都已过时！)

No, it isn't. - Please note that the former declaration would load version 3.0 by default. However it's absolutely preferrable to get the version 6.0 (any other versions are obsolete nowadays!)

当您使用所谓的早期绑定(引用"Microsoft XML，v6.0")时，我将做同样的事情，但是引用的是当前版本6.0:

As you are using so called early binding (referencing "Microsoft XML, v6.0"), I'll do the same but am referring to the current version 6.0:

Dim oXml As MSXML2.DOMDocument60        ' declare the xml doc object
Set oXml = New MSXML2.DOMDocument60     ' set an instance of it to memory

• [3] 误解了某些XPath表达式

XPath表达式中的斜杠"/"始终引用DocumentElement(此处为<Concepts>)， 您可以改为将.DocumentElement添加到文档对象.起始双斜杠"//xyz"将找到任何"xyz"节点(如果存在).

A starting slash "/" in the XPath expression always refers to the DocumentElement (<Concepts> here), you can add .DocumentElement to your document object instead. A starting double slash "//xyz" would find any "xyz" node if existant.

例如

    oXml.SelectNodes("//Query").Length 

返回与

    oXml.DocumentElement.SelectNodes("//Query").Length   ' or 
    oXml.SelectSingleNode("//Queries").ChildNodes.Length ' or even       
    oXml.SelectNodes("/*/*/*/Query").Length`.

参考XML版本6.0的代码示例

当然，您必须循环几个xml文件，该示例仅使用一个(从第2行开始).

Of course you'd have to loop over several xml files, the example only uses one (starting in row 2).

只是针对格式不正确的xml文件的情况，我添加了详细的错误例程，使您能够识别假定的错误位置. Load和LoadXML都返回一个布尔值(如果正确加载则为True，否则为False).

Just for the case of not well formed xml files I added a detailled error Routine that enables you to identify the presumed error location. Load and LoadXML both return a boolean value (True if loaded correctly, False if not).

Sub xmlTest()

Dim ws   As Worksheet: Set ws = ThisWorkbook.Sheets(3)
Dim oXml As MSXML2.DOMDocument60
Set oXml = New MSXML2.DOMDocument60
With oXml
    .validateOnParse = True
    .setProperty "SelectionLanguage", "XPath"   ' necessary in version 3.0, possibly redundant here
    .async = False

    If Not .Load(ThisWorkbook.Path & "\xml\" & "name.xml") Then
        Dim xPE        As Object    ' Set xPE = CreateObject("MSXML2.IXMLDOMParseError")
        Dim strErrText As String
        Set xPE = .parseError
        With xPE
           strErrText = "Load error " & .ErrorCode & " xml file " & vbCrLf & _
           Replace(.URL, "file:///", "") & vbCrLf & vbCrLf & _
          xPE.reason & _
          "Source Text: " & .srcText & vbCrLf & vbCrLf & _
          "Line No.:    " & .Line & vbCrLf & _
          "Line Pos.: " & .linepos & vbCrLf & _
          "File Pos.:  " & .filepos & vbCrLf & vbCrLf
        End With
        MsgBox strErrText, vbExclamation
        Set xPE = Nothing
        Exit Sub
    End If

    ' Debug.Print "|" & oXml.XML & "|"

    Dim Queries  As IXMLDOMNodeList, Query As IXMLDOMNode
    Dim Searched As String
    Dim i&, ii&
    i = 2       ' start row
  ' start XPath  
    Searched = "ConceptModel/Queries/Query"                     ' search string
    Set Queries = oXml.DocumentElement.SelectNodes(Searched)    ' XPath
  ' 
    ws.Cells(i, 1) = IIf(Queries.Length = 0, "No items", Queries.Length & " items")
    ii = 1
    For Each Query In Queries
        ii = ii + 1
        ws.Cells(i, ii) = Query.Text
    Next

End With

End Sub

其他提示

您可能还对示例如何通过XMLDOM列出所有子节点以及

You also might be interested in an example how to list all child nodes via XMLDOM and to obtain attribute names from XML using VBA.

由于稍后的评论(感谢@barrowc)，我还提供了进一步的提示

I include a further hint due to later comment (thanks to @barrowc )

"使用MSXML v3.0的另一个问题是，默认选择语言是XSLPatterns而不是XPath. 有关MSXML版本之间某些差异的详细信息，请参见此处中讨论了两种选择语言之间的区别."

"A further issue with using MSXML, v3.0 is that the default selection language is XSLPatterns instead of XPath. Details on some of the differences between MSXML versions are here and the differences between the two selection languages are discussed here."

在当前的MSXML2版本6.0中，完全支持XPath 1.0.因此，似乎 XSL模式已由Microsoft在较早的时候实现，基本上可以将其视为XPath W3C标准化之前的XPath表达式的简化子集.

In the current MSXML2 version 6.0 XPath 1.0 is fully supported. So it seems XSL Patterns have been implemented by Microsoft in earlier days, basically it can be regarded as a simplified subset of XPath expressions before W3C standardisation of XPath.

MSXML2版本3.0允许至少通过显式选择语言设置来集成XPath 1.0:

MSXML2 Version 3.0 allows the integration of XPath 1.0 at least by explicit selection language setting:

oXML.setProperty "SelectionLanguage", "XPath"   ' oXML being the DOMDocument object as used in original post  

这篇关于XML解析VBA Excel(功能旅行，& MSXML2.DOMDocument)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！