How to Parse XML file in VBA

Question

I have data in the below format and have similar data in Excel sheet.

<LegalEntityDataVO>
   <LegalEntityDataVORow>
      <Name>Siemens Corporation</Name>
      <LegalEntityIdentifier>010</LegalEntityIdentifier>
      <EstablishmentData>
         <EstablishmentDataVORow>
            <MainEstablishmentFlag>Y</MainEstablishmentFlag>
            <Name>Siemens Corporation</Name>
            <GeographyCode>US</GeographyCode>
            <RegistrationDataEtb>
               <RegistrationDataEtbVORow>
                  <SourceTable>XLE_ETB_PROFILES</SourceTable>
                  <Name>United States Income Tax</Name>
               </RegistrationDataEtbVORow>
               <RegistrationDataEtbVORow>
                  <SourceTable>XLE_ETB_PROFILES</SourceTable>
                  <Name>United States Federal Tax</Name>
               </RegistrationDataEtbVORow>
            </RegistrationDataEtb>
         </EstablishmentDataVORow>
      </EstablishmentData>
      <EstablishmentData>
         <EstablishmentDataVORow>
            <MainEstablishmentFlag>Y</MainEstablishmentFlag>
            <Name>US Corporation</Name>
            <GeographyCode>US</GeographyCode>
            <RegistrationDataEtb>
               <RegistrationDataEtbVORow>
                  <SourceTable>XLE_ETB_PAYBLES</SourceTable>
                  <Name>United States Service Tax</Name>
               </RegistrationDataEtbVORow>
               <RegistrationDataEtbVORow>
                  <SourceTable>XLE_ETB_PAYBLES</SourceTable>
                  <Name>United States Oil Tax</Name>
               </RegistrationDataEtbVORow>
            </RegistrationDataEtb>
         </EstablishmentDataVORow>
      </EstablishmentData>
    </LegalEntityDataVORow>
<LegalEntityDataVO>

So my requirement is to compare the Excel data with XML data. Specifically, my task is described below:

If **LegalEntityIdentifier** value in Excel = **LegalEntityIdentifier** value in xml then

(   
If(**MainEstablishmentFlag** value in Excel = **MainEstablishmentFlag** value in Xml then 

    (
        Compare **Name** in Excel  with **Name** in XML
    )
)


**LegalEntityIdentifier** childnode of LegalEntityDataVORow

**MainEstablishmentFlag** childnode of EstablishmentDataVORow

**Name** childnode of RegistrationDataEtbVORow

Here are the problems I face:

1. Every LegalEntityDataVORow contains many EstablishmentDataVORow
2. Every EstablishmentDataVORow contains many RegistrationDataEtbVORow.

In my XML file I have 100 <LegalEntityDataVORow>. How do I run the above task in VBA?

Answer 1

The following code will generate the output below the Code for the file below the output:

Code:

Sub parse_data()

    Dim strXmlFileName As String: strXmlFileName = "Drive:\Path\Filename.xml"
    Dim docXmlDocument As New MSXML2.DOMDocument60

    Dim wsDataToCompare As Worksheet: Set wsDataToCompare = ActiveWorkbook.Sheets("DataToCompare")
    Dim strLegalEntityIdentifierToCompare As String: strLegalEntityIdentifierToCompare = wsDataToCompare.Cells(1, 1).Value
    Dim strMainEstablishmentFlagToCompare As String: strMainEstablishmentFlagToCompare = wsDataToCompare.Cells(2, 1).Value

    Dim ndeEntityData As IXMLDOMNode
    Dim ndeEntityDataChild As IXMLDOMNode
    Dim ndeEstablishmentData As IXMLDOMNode

    Dim strNameToExtract As String

    docXmlDocument.Load strXmlFileName

    For Each ndeEntityData In docXmlDocument.DocumentElement.ChildNodes

        If ndeEntityData.SelectSingleNode("LegalEntityIdentifier").Text = strLegalEntityIdentifierToCompare Then

            For Each ndeEntityDataChild In ndeEntityData.ChildNodes

                If ndeEntityDataChild.BaseName = "EstablishmentData" Then

                    If ndeEntityDataChild.SelectSingleNode("EstablishmentDataVORow/MainEstablishmentFlag").Text = strMainEstablishmentFlagToCompare Then

                        strNameToExtract = ndeEntityDataChild.SelectSingleNode("EstablishmentDataVORow/Name").Text
                        Debug.Print strNameToExtract

                    End If

                End If

            Next ndeEntityDataChild

        End If

    Next ndeEntityData

End Sub

Output:

Siemens Corporation
US Corporation

Note that I had to expand your XML-File again to make it valid. The file I used is:

<?xml version="1.0" encoding="UTF-8"?>
<LegalEntityDataVO>
   <LegalEntityDataVORow>
      <Name>Siemens Corporation</Name>
      <LegalEntityIdentifier>010</LegalEntityIdentifier>
      <EstablishmentData>
         <EstablishmentDataVORow>
            <MainEstablishmentFlag>Y</MainEstablishmentFlag>
            <Name>Siemens Corporation</Name>
            <GeographyCode>US</GeographyCode>
            <RegistrationDataEtb>
               <RegistrationDataEtbVORow>
                  <SourceTable>XLE_ETB_PROFILES</SourceTable>
                  <Name>United States Income Tax</Name>
               </RegistrationDataEtbVORow>
               <RegistrationDataEtbVORow>
                  <SourceTable>XLE_ETB_PROFILES</SourceTable>
                  <Name>United States Federal Tax</Name>
               </RegistrationDataEtbVORow>
            </RegistrationDataEtb>
         </EstablishmentDataVORow>
      </EstablishmentData>
      <EstablishmentData>
         <EstablishmentDataVORow>
            <MainEstablishmentFlag>Y</MainEstablishmentFlag>
            <Name>US Corporation</Name>
            <GeographyCode>US</GeographyCode>
            <RegistrationDataEtb>
               <RegistrationDataEtbVORow>
                  <SourceTable>XLE_ETB_PAYBLES</SourceTable>
                  <Name>United States Service Tax</Name>
               </RegistrationDataEtbVORow>
               <RegistrationDataEtbVORow>
                  <SourceTable>XLE_ETB_PAYBLES</SourceTable>
                  <Name>United States Oil Tax</Name>
               </RegistrationDataEtbVORow>
            </RegistrationDataEtb>
         </EstablishmentDataVORow>
      </EstablishmentData>
    </LegalEntityDataVORow>
</LegalEntityDataVO>