时间戳

首页 > 解决方案 > 在 Excel 中使用 VBA 进行选择性 XML 节点解析

xml - 在 Excel 中使用 VBA 进行选择性 XML 节点解析

问题描述

我是一个对 VBA 几乎一无所知的大型机资源。

我需要自动化该过程以从 XML 文件中解析一些节点并在 Excel 中显示它们。然后我需要连接到 DB2 并从表中获取字段的值（与 XML 节点相同）并进行比较。

到目前为止，我能够解析 XML，但我的代码只是打印来自每个节点的文本。

它既没有组织也没有选择性。

这是 XML 文件示例：

<?xml version="1.0" encoding="iso-8859-1"?>
<ServiceRequest
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:xsd="http://www.w3.org/2001/XMLSchema" version="2.0">
    <RequestHeader>
        <ServiceName>ProcessAMLMessageService</ServiceName>
        <ApplicationVersion>1.0</ApplicationVersion>
    </RequestHeader>
    <RequestBody>
        <Arguments type="ContractApplicationAMLDetails">
            <ContractApplicationAMLDetails>
                <AMLDataLanguage>E</AMLDataLanguage>
                <AMLApplication>E-FORM</AMLApplication>
                <AMLApplicationVersionNumber>3.0.0</AMLApplicationVersionNumber>
                <LaunchingApplication>EApp</LaunchingApplication>
                <LaunchingApplicationVersionNumber>36.1</LaunchingApplicationVersionNumber>
                <AdvisorNumber>123456</AdvisorNumber>
                <Userid>U12345</Userid>
                <ContractDetails>
                    <ContractNumber>CONT1234</ContractNumber>
                    <SourceData>
                        <AMLAdvisorNumber>98765</AMLAdvisorNumber>
                        <IntendedUseOfAcctCode>IOAC</IntendedUseOfAcctCode>
                        <ThirdPartyInd>N</ThirdPartyInd>
                        <TrustInd/>
                        <EstateInd/>
                        <SourcesOfPayment>
                            <SourceOfPayment>
                                <SequenceNumber>1</SequenceNumber>
                                <PaymentSourceCode>IA</PaymentSourceCode>
                            </SourceOfPayment>
                            <SourceOfPayment>
                                <SequenceNumber>2</SequenceNumber>
                                <PaymentSourceCode>SP</PaymentSourceCode>
                            </SourceOfPayment>
                            <SourceOfPayment>
                                <SequenceNumber>3</SequenceNumber>
                                <PaymentSourceCode>IF</PaymentSourceCode>
                            </SourceOfPayment>
                        </SourcesOfPayment>
                    </SourceData>
                </ContractDetails>
                <Owners>
                    <Owner>
                        <PartyTypeCode>P</PartyTypeCode>
                        <Person>
                            <UniqueIdentifier>UNIQUE-IDENTIFIER-1</UniqueIdentifier>
                            <Surname>USER-NAME</Surname>
                            <FirstName>FIRST-NAME</FirstName>
                            <DateOfBirth>1967-03-01</DateOfBirth>
                            <OccupationOrPrincipalBusiness>Pilot</OccupationOrPrincipalBusiness>
                            <AMLPEPInd>Y</AMLPEPInd>
                            <SelfIdentifiedPEFPInd>N</SelfIdentifiedPEFPInd>
                            <SelfIdentifiedPEDPInd>Y</SelfIdentifiedPEDPInd>
                            <SelfIdentifiedHIOInd>Y</SelfIdentifiedHIOInd>
                            <ForeignTaxResidentInd>N</ForeignTaxResidentInd>
                            <CRSForeignTaxResidentInd>N</CRSForeignTaxResidentInd>
                            <PEDPDetails>
                                <PEDP>
                                    <UniqueIdentifier>UNIQUE-IDENTIFIER-2</UniqueIdentifier>
                                    <Surname>USER-2</Surname></Surname>
                                    <FirstName>FIRST-2</FirstName>
                                    <RelationshipCode>RC</RelationshipCode>
                                    <PositionCode>PC-1</PositionCode>
                                    <CountryText>COUNTRY-1</CountryText>
                                    <PEOrganizationText>COUNTRY-1 Council</PEOrganizationText>
                                </PEDP>
                            </PEDPDetails>
                            <HIODetails>
                                <HIO>
                                    <UniqueIdentifier>HIO UNIQUI ID-1</UniqueIdentifier>
                                    <Surname>ApplicantAHIOw</Surname>
                                    <FirstName>HIOFourE</FirstName>
                                    <RelationshipCode>SI</RelationshipCode>
                                    <PositionText>Political Party Leader</PositionText>
                                    <CountryText>The United Arab Emirates</CountryText>
                                    <PEOrganizationCode>H06</PEOrganizationCode>
                                </HIO>
                                <HIO>
                                    <UniqueIdentifier>HIO UNIQUI ID-2</UniqueIdentifier>
                                    <Surname>ApplicantAHIOx</Surname>
                                    <FirstName>HIOFourF</FirstName>
                                    <RelationshipCode>PA</RelationshipCode>
                                    <PositionText>Director</PositionText>
                                    <CountryText>France</CountryText>
                                    <PEOrganizationText>Chamber of Commerce</PEOrganizationText>
                                    <PEOrganizationCode>XX</PEOrganizationCode>
                                </HIO>
                            </HIODetails>
                            <FATCADetails/>
                        </Person>
                        <Address>
                            <AddressLine1>ADDRESS-1</AddressLine1>
                            <City>CITY-1</City>
                            <ProvinceCode>PC</ProvinceCode>
                            <PostalCode>N2N 2N2</PostalCode>
                            <CountryCode>CA</CountryCode>
                            <CountryText>Canada</CountryText>
                        </Address>
                    </Owner>
                </Owners>
            </ContractApplicationAMLDetails>
        </Arguments>
    </RequestBody>
</ServiceRequest>

这是我想出的代码：

Public Sub Convert_XML_To_Excel_Through_VBA()
    'Code from Officetricks.com
    'Add referece from Menu: "Tools -> References -> Microsoft XML Vn.0"
    Dim iRow As Integer, iCol As Integer
    Dim xmlDoc As MSXML2.DOMDocument, xmlRoot As MSXML2.IXMLDOMNode
    Dim xmlNodes As MSXML2.IXMLDOMNode, xmlData As MSXML2.IXMLDOMNode
    Set xmlDoc = New MSXML2.DOMDocument
    
    'Load & Wait till complete XML Data is loaded
    xmlDoc.async = False
    xmlDoc.validateOnParse = False
    xmlDoc.Load ("C:\Users\XML_FIle.xml")
    
    'XML Loaded. Now Read Elements One by One into XML DOM Objects
    Set xmlRoot = xmlDoc.DocumentElement
    Set xmlNodes = xmlRoot.FirstChild
    
    'Read XML Data and Load into Excel Sheet by each Node and Chile Node
    iRow = 0
    For Each xmlNodes In xmlRoot.ChildNodes
        iRow = iRow + 1
        iCol = 0
        
        For Each xmlData In xmlNodes.ChildNodes
            iCol = iCol + 1
            ThisWorkbook.Sheets(1).Cells(1, iCol) = xmlData.BaseName
            ThisWorkbook.Sheets(1).Cells(iRow, iCol) = xmlData.Text
        Next xmlData
    Next xmlNodes
End Sub

标签： xmlvbaexcel

解决方案

您可以通过以下方式选择元素：

1) xpath (nodes2) 例如

Set nodes2 = xmlDoc.SelectNodes("//Userid")

2) 并在 DOM 结构（节点）中使用标签，例如

Set nodes = xmlDoc.getElementsByTagName("Person")

我很惊讶您的文档被解析，因为以下内容不正确：

<Surname>USER-2</Surname></Surname>

它应该是

<Surname>USER-2</Surname>

VBA：

Option Explicit
Public Sub Convert_XML_To_Excel_Through_VBA()
    Dim xmlDoc As MSXML2.DOMDocument60, xmlRoot As MSXML2.IXMLDOMNode
    Set xmlDoc = New MSXML2.DOMDocument60

    xmlDoc.async = False
    xmlDoc.validateOnParse = False
    xmlDoc.Load "C:\Users\XML_FIle.xml"
    Set xmlRoot = xmlDoc.DocumentElement

    Dim nodes As Object, nodes2, i  As Long
    Set nodes = xmlDoc.getElementsByTagName("Person"): Set nodes2 = xmlDoc.SelectNodes("//Userid")

    For i = 0 To nodes.Length - 1
        Debug.Print nodes(i).Text
        Debug.Print nodes2(i).Text
    Next i
End Sub

XML xpath 查询：

xml路径查询

回复：你的更新

这是选择这些并从中列出的示例

Option Explicit
Public Sub Convert_XML_To_Excel_Through_VBA()
    Dim xmlDoc As MSXML2.DOMDocument60, xmlRoot As MSXML2.IXMLDOMNode
    Set xmlDoc = New MSXML2.DOMDocument60

    xmlDoc.async = False
    xmlDoc.validateOnParse = False
    xmlDoc.Load "C:\Users\XML_FIle.xml"
    Set xmlRoot = xmlDoc.DocumentElement

    Dim userIds As Object, AMLAdvisorNumbers As Object, UniqueIdentifiers As Object, RelationshipCodes As Object, i As Long
    Set userIds = xmlDoc.SelectNodes("//Userid")
    Set AMLAdvisorNumbers = xmlDoc.SelectNodes("//AMLAdvisorNumber")
    Set UniqueIdentifiers = xmlDoc.SelectNodes("//AMLAdvisorNumber")
    Set RelationshipCodes = xmlDoc.SelectNodes("//RelationshipCode")

    For i = 0 To userIds.Length - 1
        Debug.Print userIds(i).Text
        Debug.Print AMLAdvisorNumbers(i).Text
        Debug.Print UniqueIdentifiers(i).Text
        Debug.Print RelationshipCodes(i).Text
    Next i
End Sub

它们的长度并不完全相同，因此您不希望使用上面显示的循环来清空所有内容：

Debug.Print userIds.Length
Debug.Print AMLAdvisorNumbers.Length
Debug.Print UniqueIdentifiers.Length
Debug.Print RelationshipCodes.Length

上面显示除了最后一个都是长度1，而最后一个是长度3。

推荐阅读