How to parse Xml-File with XPath in multiple Tree? RRS feed

  • Question

  • Hello together,

    with Excel-VBA i have to parse a xml-File with a known structure like the example below to write Data into an Excel-Sheet. Currently I have solved the achievement with a recursively Procedure.

    I have heard, that my solution takes big Space with big Data in an xml-File. So i should use XPath to hold ResponseRecords in a NodesCollection with "xNode.SelectNodes" and read values of ChildNodes directly with xNode.SelectSingleNode.

    Before describing my issue, i'll give you the Xml-Structure:

    <retainedDataMessage xmlns=""> <rdHeaderId></rdHeaderId> <retainedDataHeader> <requestID> <authorisedOrganisationID>018</authorisedOrganisationID> <requestNumber>1234</requestNumber> </requestID> <cSPID>87001</cSPID> <timeStamp>20171225121212+0200</timeStamp> </retainedDataHeader> <retainedDataPayload> <responseMessage> <responseStatus> <responseComplete/> </responseStatus> <responsePayload> <ResponseRecord> <recordNumber>1</recordNumber> <recordPayload> <ProductRecord> <productServiceUsage> <productInformation> <ProductPartyInformation> <productRole> <class-1/> </partyRole> <productNumber>8888888</productNumber> <usageTime> <startTime>20170101131425+0200</startTime> </usageTime> <code1>987654</code1> <code2>123456789</code2> <detailedInformation> <productDetailedInformation>Hello World</productDetailedInformation> </detailedInformation> </ProductPartyInformation> <ProductPartyInformation> ... </ProductPartyInformation> </productInformation> </productServiceUsage> </productRecord> </recordPayload> <additionalInformation> <otherInformation>"This is an example"</otherInformation> </additionalInformation> </ResponseRecord> <ResponseRecord> <recordNumber>2</recordNumber> <recordPayload>

    ... </recordPayload> <additionalInformation> <otherInformation>"This is info to 2"</otherInformation> </additionalInformation> </ResponseRecord> ... </responsePayload> <additionalResponsePayload> <additionalInformation>"This is global information"</additionalInformation> </additionalResponsePayload> <responseNumber>3</responseNumber> </responseMessage> </retainedDataPayload> </retainedDataMessage>

    First of all i get DOMDocument and rootNode with:
    Dim xmlDoc as DOMDocument60
    Dim xmlRoot as MSXML2.IXMLDOMNode

    Set xmlDoc = New DOMDocument
    Set xmlRoot = xmlDoc.DocumentElement()

    Then I get namespace with:
    nameSpace = xmlRoot.NamespaceURI
    and set the namespace for XPath
    xmlDoc.setProperty "SelectionNamespaces", "xmlns:a='" & Namespace & "'"

    Now i want to loop over all responseRecords with:
    xmlPathRecords = "/a:retainedDataMessage/a:..../a:ResponseRecord"
    Set responseRecordNodes = xmlRoot.SelectNodes(xmlPathRecords)
    For each xmlRecordNode in responseRecordNodes

    And then i want to loop through the ChildNodes "ProductPartyInformation" with
    xmlPathParty = xmlPathRecords & "/a:recordPayLoad/a:.../a:ProductPartyInformation"
    Set PartyNodes= xmlRecordNode .SelectNodes(xmlPathParty)
    For each partyNode in PartyNodes

    The issue is, that in the "PartyNodes"-Collection all Nodes from all responseRecords are listed and not only the direct childNodes from current xmlRecordNode.

    Has anyone a solution for my achievement?

    Greetings Jürgen

    Wednesday, December 27, 2017 8:35 AM

All replies

  • Hello juergschweigi,

    You could use SelectSingleNode to selected the first ResponseRecord so that you do not need to iterate through all the ResponseRecord to return ProductPartyInformation.

    Here is the example.

    xmlPathRecords = "/a:retainedDataMessage/a:..../a:ResponseRecord"
    Set xmlRecordNode = xmlRoot.SelectSingleNode(xmlPathRecords)
    xmlPathParty = xmlPathRecords & "/a:recordPayLoad/a:.../a:ProductPartyInformation"
    Set PartyNodes= xmlRecordNode .SelectNodes(xmlPathParty)
    For each partyNode in PartyNodes

    Best Regards,


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact

    • Proposed as answer by Tony---- Wednesday, January 3, 2018 6:19 AM
    Thursday, December 28, 2017 9:51 AM