使用VBA从文件中提取XML值 [英] Extract XML value from file using VBA

问题描述

请相信我，我确实已经在Google上进行了搜索和搜索，但是我是使用VBA的XML的新手.我看过的所有示例都使用了所谓的简单" XML，而我的示例似乎更加复杂.首先，这是我的XML的简单摘录(如果我可以设法用块引号将其附加).

Please believe me, I really have googled and searched, but I'm a newbie to XML using VBA. All the examples I've seen use what I would call "simple" XML, and my example (to me) seems more complicated. First of all, here's a simple extract of my XML (if I can manage to append it with block quotes).

<wsdl:definitions xmlns:wsdl="http://schemas.xmlsoap.org/wsdl/" xmlns:apachesoap="http://xml.apache.org/xml-soap" xmlns:impl="urn:ec.europa.eu:taxud:tin:services:checkTin" xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/" xmlns:tns1="urn:ec.europa.eu:taxud:tin:services:checkTin:types" xmlns:wsdlsoap="http://schemas.xmlsoap.org/wsdl/soap/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" targetNamespace="urn:ec.europa.eu:taxud:tin:services:checkTin">
   <wsdl:types>
      <xsd:schema xmlns="urn:ec.europa.eu:taxud:tin:services:checkTin:types" attributeFormDefault="qualified" elementFormDefault="qualified" targetNamespace="urn:ec.europa.eu:taxud:tin:services:checkTin:types">
         <xsd:element name="checkTin">
            <xsd:complexType>
               <xsd:sequence>
                  <xsd:element name="FR" type="xsd:string" />
                  <xsd:element name="98-0242041" type="xsd:string" />
               </xsd:sequence>
            </xsd:complexType>
         </xsd:element>
         <xsd:element name="checkTinResponse">
            <xsd:complexType>
               <xsd:sequence>
                  <xsd:element name="countryCode" type="xsd:string" />
                  <xsd:element name="tinNumber" type="xsd:string" />
                  "
                  <xsd:element name="requestDate" type="xsd:date" />
                  <xsd:element name="validStructure" type="xsd:boolean" />
                  <xsd:element name="validSyntax" type="xsd:boolean" minOccurs="0" />
               </xsd:sequence>
            </xsd:complexType>
         </xsd:element>
        .... multiple elements of <xsd:element name="checkTin"> and <xsd:element >  name="checkTinResponse"> then follow
        .....
      </xsd:schema>
   </wsdl:types>
   <wsdl:message name="checkTinRequest">
      <wsdl:part name="parameters" element="tns1:checkTin" />
   </wsdl:message>
   <wsdl:message name="checkTinResponse">
      <wsdl:part name="parameters" element="tns1:checkTinResponse" />
   </wsdl:message>
   <wsdl:portType name="checkTinPortType">
      <wsdl:operation name="checkTin">
         <wsdl:input name="checkTinRequest" message="impl:checkTinRequest" />
         <wsdl:output name="checkTinResponse" message="impl:checkTinResponse" />
      </wsdl:operation>
   </wsdl:portType>
   <wsdl:binding name="checkTinBinding" type="impl:checkTinPortType">
      <wsdlsoap:binding style="document" transport="http://schemas.xmlsoap.org/soap/http" />
      <wsdl:operation name="checkTin">
         <wsdlsoap:operation soapAction="" />
         <wsdl:input name="checkTinRequest">
            <wsdlsoap:body use="literal" />
         </wsdl:input>
         <wsdl:output name="checkTinResponse">
            <wsdlsoap:body use="literal" />
         </wsdl:output>
      </wsdl:operation>
   </wsdl:binding>
   <wsdl:service name="checkTinService">
      <wsdl:port name="checkTinPort" binding="impl:checkTinBinding">
         <wsdlsoap:address location="https://ec.europa.eu/taxation_customs/tin/services/checkTinService" />
      </wsdl:port>
   </wsdl:service>
</wsdl:definitions>

我找到并测试了以下代码:

I have found and tested the following code:

Public Sub LoadDocument()

Dim XDoc As MSXML2.DOMDocument
Set XDoc = New MSXML2.DOMDocument
XDoc.validateOnParse = False
' The file here is basically the same as the XML code above
If XDoc.Load("E:\Excel\TIN\KVKF440I.txt") Then
   ' The document loaded successfully.
   ' Now do something intersting.
   DisplayNode XDoc.ChildNodes, 0
Else
   ' The document failed to load.
   ' See the previous listing for error information.
   ' The document failed to load.
   Dim strErrText As String
   Dim xPE As MSXML2.IXMLDOMParseError
   ' Obtain the ParseError object
   Set xPE = XDoc.parseError
   With xPE
      strErrText = "Your XML Document failed to load" & _
            "due the following error." & vbCrLf & _
      "Error #: " & .ErrorCode & ": " & xPE.reason & _
      "Line #: " & .Line & vbCrLf & _
      "Line Position: " & .linepos & vbCrLf & _
      "Position In File: " & .filepos & vbCrLf & _
      "Source Text: " & .srcText & vbCrLf & _
      "Document URL: " & .URL
    End With

    MsgBox strErrText, vbExclamation
End If

Set XDoc = Nothing

End Sub

Public Sub DisplayNode(ByRef Nodes As MSXML2.IXMLDOMNodeList, ByVal Indent    As Integer)

   Dim xNode As MSXML2.IXMLDOMNode
   Indent = Indent + 2

   For Each xNode In Nodes
     ' If xNode.NodeType = NODE_TEXT Then
       If xNode.ParentNode.nodeName = "xsd:element" Then
         Debug.Print Space$(Indent) & xNode.ParentNode.nodeName & _
             ":" & xNode.NodeValue
       End If

       If xNode.HasChildNodes Then
         DisplayNode xNode.ChildNodes, Indent
       End If
   Next xNode

End Sub

基本上，我想为每个 element name ="checkTin" 循环(?)并提取子元素 element name = 的值(即，在上面的示例中，我要提取FR和98-0242021).然后我想做同样的事情为相应的做同样的事情 element name ="checkTinResponse" 并提取其中的5个元素属于此的 xsd:element name = .

Basically, I want to loop (?) for each element name="checkTin" and extract the values for the child element name= (ie, in the example above, I want to extract FR and 98-0242021). Then I want to do the same thing do the same thing for the corresponding element name="checkTinResponse" and extract the 5 elements of xsd:element name= that belong to this.

正如我所说，我尝试了很多发现的示例，但是显然我不知道自己在做什么，也不完全理解我得到的结果.例如，我猜测上面测试 xNode.ParentNode.nodeName ="xsd:element" 的复制代码并不是最好的方法.

As I say, I have tried loads of examples I found, but obviously I don't know what I'm doing, nor do I completely understand that results I'm getting. For example, I'm guessing that the copied code above that tests for xNode.ParentNode.nodeName = "xsd:element" is not really the best way to go.

任何建议都将不胜感激.

Any suggestions greatly appreciated.

推荐答案

您可以使用 XPath 表达式来获取所需的元素.

You can use XPath expressions to get the elements you are looking for.

我在下面的代码中使用了2个 XPath 表达式:

I have used 2 XPath expressions in the code below:

• //* [local-name()='schema']/* [local-name()='element']

././/* [local-name()='element']

//* [local-name()='schema']/* [local-name()='element'] 用于获取所有元素在 schema 节点下.然后，我们循环每个元素，并使用 XPath 表达式 ././/* [local-name()='element'] 来获取子元素 elements .

//*[local-name()='schema']/*[local-name()='element'] is used get all the elements under the schema node. Then we loop each element and use the XPath expression ././/*[local-name()='element'] to get the child elements.

注意::在执行该过程之前，请添加对 Microsoft Xml，v6.0 的引用.

NB: Before executing the procedure, add reference to Microsoft Xml, v6.0.

Sub GetElements()
    Dim xmlFileName As String
    Dim XDoc As DOMDocument60
    Dim pElements As IXMLDOMNodeList, pElement As IXMLDOMNode
    Dim chElements As IXMLDOMNodeList, chElement As IXMLDOMNode

    xmlFileName = "C:\Temp\test.xml"       ''-- set filename appropriately
    Set XDoc = New DOMDocument60
    XDoc.validateOnParse = False
    If XDoc.Load(xmlFileName) Then
       ''-- The document loaded successfully.
       Set pElements = XDoc.SelectNodes("//*[local-name()='schema']/*[local-name()='element']")
       For Each pElement In pElements
            ''-- print the parent node
            Debug.Print pElement.Attributes.getNamedItem("name").NodeValue
            Set chElements = pElement.SelectNodes("././/*[local-name()='element']")
            For Each chElement In chElements
                ''-- print the child nodes
                Debug.Print vbTab & chElement.Attributes.getNamedItem("name").NodeValue
            Next
       Next
    Else
       ''-- The document failed to load.
        MsgBox Err.Number & ":" & Err.Description, vbExclamation, "Error"
    End If

    Set XDoc = Nothing

End Sub

结果:

checkTin
    FR
    98-0242041
checkTinResponse
    countryCode
    tinNumber
    requestDate
    validStructure
    validSyntax

这篇关于使用VBA从文件中提取XML值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！