Reputation: 95
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:
LegalEntityDataVORow
contains many EstablishmentDataVORow
EstablishmentDataVORow
contains many RegistrationDataEtbVORow
.In my XML file I have 100 <LegalEntityDataVORow>
. How do I run the above task in VBA?
Upvotes: 0
Views: 5341
Reputation: 367
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>
Upvotes: 1