Extract XML value from file using VBA

Question

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

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.

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.

Pradeep Kumar · Accepted Answer · 2016-08-11 14:30:04Z

3

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

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'] 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.

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

RESULT :

checkTin
    FR
    98-0242041
checkTinResponse
    countryCode
    tinNumber
    requestDate
    validStructure
    validSyntax

edited Aug 11, 2016 at 14:30

answered Aug 11, 2016 at 14:23

Pradeep Kumar

7,0294 gold badges24 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Michael Simpson Over a year ago

Many thanks. There's no way I'd have made it to that solution. I'll test later tonight at home.

Michael Simpson Over a year ago

I had to change the code to Dim XDoc As MSXML2.DOMDocument and Set XDoc = New MSXML2.DOMDocument, but on the line with Set pElements = XDoc.SelectNodes("//*[local-name()='schema']/*[local-name()='element']") I got an error message of "Unknown method"

Michael Simpson Over a year ago

Found the solution here [link]stackoverflow.com/questions/20617236/… I added the line about SetProperty and then it workd fine. Many thanks for your original answer

Pradeep Kumar Over a year ago

The above code should run fine as it is if you add reference to Microsoft Xml, v6.0, as stated in the answer. You probably added reference to a lower version DLL.

Collectives™ on Stack Overflow

Extract XML value from file using VBA

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related