如何在VBA中解析XML文件 [英] How to Parse XML file in VBA

问题描述

我具有以下格式的数据，并且在Excel工作表中具有相似的数据.

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>

因此，我的要求是将Excel数据与XML数据进行比较.具体来说，我的任务描述如下:

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

这是我面临的问题:

1. 每个 LegalEntityDataVORow 都包含许多 FoundationDataVORow
2. 每个 EstablishmentDataVORow 都包含许多 RegistrationDataEtbVORow .

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

在我的XML文件中，我有100个< LegalEntityDataVORow> .如何在VBA中运行上述任务?

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

推荐答案

以下代码将在代码下方生成输出，该文件位于输出下方的文件中:

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

代码:

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

输出:

Siemens Corporation
US Corporation

请注意，我必须再次扩展您的XML文件以使其有效.我使用的文件是:

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>

这篇关于如何在VBA中解析XML文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！