通过VB6读取多个XML文件 [英] Reading multiple XML Files via VB6

问题描述

我必须将多个巨大的xml数据文件导入Excel。我不能使用简单的 loadXML（）函数，因为Excel没有足够的RAM可用。 （一些xml文件是〜100mb）

现在我真的尝试了很多...但是真的不能让它发生。
示例XML文件：

 < OMDS xmlns =urn：omds20xmlns：xsi =http： /www.w3.org/2001/XMLSchema-instancexsi：schemaLocation =urn：omds20 ./omds24-00.xsd\"> 
< PAKET VUNr =1MaklerID =2PaketZpktErstell =xPaketZpktLetztErstell =y> 
< PROVISION ProvisionsID =123Polizzennr =321Vermnr =5BuchDat =2013-02-27/> 
< PROVISION ProvisionsID =456Polizzennr =321Vermnr =5BuchDat =2013-02-27/> 
< PROVISION ProvisionsID =789Polizzennr =321Vermnr =5BuchDat =2013-02-27/> 
< / PAKET> 
< / OMDS> 
  

所以我在VBA中是这样的：

  Sub ParseXmlDocument（）
 Dim doc As New MSXML2.DOMDocument 
 Dim success As Boolean 
 
 success = doc.Load（App.Path&\test.xml）
如果success = False然后
 MsgBox doc.parseError.reason 
 Else 
 Dim nodeList As MSXML2.IXMLDOMNodeList 
 
设置nodeList = doc.selectNodes（/ OMDS / PAKET / PROVISION）
 
如果不是nodeList，则为
 Dim node As MSXML2.IXMLDOMNode 
 Dim idAs String 
 Dim值As String 
 
对于每个节点在nodeList 
 id = node.selectSingleNode（ProvisionsID）。文本
下一个节点
如果
结束If 
 End Sub 
  

之后，我只想在 MsgBox 中打印ID，但由于 nodeList 总是显示为空我不能让它发生。

希望有人可以帮助我。

---

感谢GSerg，我能够解决问题。这里解决方案

  Sub ParseXmlDocument（）
 Dim doc As New MSXML2.DOMDocument 
 Dim success As Boolean 
 
与doc 
 .async = False 
 .setPropertySelectionLanguage，XPath
 .setPropertySelectionNamespaces，xmlns ：t ='urn：omds20'
结束
 
 success = doc.Load（C：\ ... \demo.xml）
 
如果success = False然后
 MsgBox doc.parseError.reason 
 Else 
 
 Dim nodeList As MSXML2.IXMLDOMNodeList 
设置nodeList = doc.SelectNodes（/ t ：OMDS / t：PAKET / t：PROVISION）
 
如果不是nodeList是没有
 Dim节点作为MSXML2.IXMLDOMNode 
 Dim id As String 
 Dim值As String 
 
对于每个节点在nodeList 
 
 
 id = node.SelectSingleNode（@ ProvisionsID）。文本
下一个节点
如果
结束If 
 End Sub 
  

解决方案

你的酸味e XML包含命名空间，但您的xPath查询不会。因此，xPath将寻找具有空名称空间的节点，而您没有任何。

为了修复它，您需要在xPath查询中提供命名空间。这样做的方法基于使用的XML库有所不同。对于MSXML，您需要在 SelectionNamespaces 属性上设置 DOMDocument 对象以包含您的命名空间前缀：

  doc.setProperty（SelectionNamespaces，xmlns：t ='urn：omds20'）
  

然后更改您的查询以使用该前缀：

 设置nodeList = doc.selectNodes / t：OMDS / t：PAKET / t：PROVISION）
  

I gotta import multiple huge xml data files into Excel. I cannot use the simple loadXML() function since Excel doesn't have enough RAM available. (some of the xml files are ~100mb)

Now I've really tried out a lot... But couldn't really make it happen at all. Example XML File:

<OMDS xmlns="urn:omds20" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="urn:omds20 ./omds24-00.xsd">
   <PAKET VUNr="1" MaklerID="2" PaketZpktErstell="x" PaketZpktLetztErstell="y">
      <PROVISION ProvisionsID="123" Polizzennr="321" Vermnr="5" BuchDat="2013-02-27"/>
      <PROVISION ProvisionsID="456" Polizzennr="321" Vermnr="5" BuchDat="2013-02-27"/>
      <PROVISION ProvisionsID="789" Polizzennr="321" Vermnr="5" BuchDat="2013-02-27"/>
   </PAKET>
</OMDS>

So what I have in VBA is something like that:

Sub ParseXmlDocument()
 Dim doc As New MSXML2.DOMDocument
 Dim success As Boolean

 success = doc.Load(App.Path & "\test.xml")
 If success = False Then
    MsgBox doc.parseError.reason
 Else
   Dim nodeList As MSXML2.IXMLDOMNodeList

   Set nodeList = doc.selectNodes("/OMDS/PAKET/PROVISION")

   If Not nodeList Is Nothing Then
     Dim node As MSXML2.IXMLDOMNode
     Dim idAs String
     Dim value As String

     For Each node In nodeList
        id= node.selectSingleNode("ProvisionsID").Text
     Next node
   End If
  End If
End Sub

After that I'm just trying to Print the ID within a MsgBox, but since the nodeList always appears to be empty, I can't make it happen.

Hope someone can help me out.

---

Thanks to GSerg i was able to solve the problem. Here the Solution

Sub ParseXmlDocument()
    Dim doc As New MSXML2.DOMDocument
    Dim success As Boolean

  With doc
    .async = False
    .setProperty "SelectionLanguage", "XPath"
    .setProperty "SelectionNamespaces", "xmlns:t='urn:omds20'"
  End With

  success = doc.Load("C:\...\demo.xml")

 If success = False Then
    MsgBox doc.parseError.reason
 Else

 Dim nodeList As MSXML2.IXMLDOMNodeList
 Set nodeList = doc.SelectNodes("/t:OMDS/t:PAKET/t:PROVISION")

   If Not nodeList Is Nothing Then
     Dim node As MSXML2.IXMLDOMNode
     Dim id As String
     Dim value As String

   For Each node In nodeList


    id = node.SelectSingleNode("@ProvisionsID").Text
   Next node
  End If
 End If
End Sub

解决方案

Your source XML contains namespaces, but your xPath query does not. So the xPath will be looking for nodes with empty namespace, and you don't have any.

In order to fix it you need to provide a namespace in your xPath query. Ways to do that differ based on the XML library used. For MSXML, you need to set the SelectionNamespaces property on the DOMDocument object to include your namespace with a prefix:

doc.setProperty("SelectionNamespaces", "xmlns:t='urn:omds20'")

And then change your query to use that prefix:

Set nodeList = doc.selectNodes("/t:OMDS/t:PAKET/t:PROVISION")

这篇关于通过VB6读取多个XML文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！