VBA 中的 XML 解析问题：从 NodeList 中提取多个 Node 值-解网

问：

下面的 XML 文档是包含订单项的发票。必须为每个订单项提取各种主数据。为简单起见，我们只考虑 . 每个订单项都有一个标题标签。<ram:SellerAssignedID><ram:IncludedSupplyChainTradeLineItem>

我的想法是使用该对象将所有标头标签（节点）收集在一起。然后，我将遍历这些项目（此处的节点长度为 2，因为标头标签出现两次）并提取 .MSXML2.IXMLDOMNodeList<ram:SellerAssignedID>

到目前为止，在我的VBA代码下面。但是，当此代码运行时，它会返回错误的结果。它使它看起来只考虑 NodeList 中的第一项。我一直只进入 Debug.Print 语句，而不是每个语句。<ram:SellerAssignedID>

结果：

111111
111111

期望：

111111
222222

我做错了什么？

Sub getMetaDataFromXmlFile()
    'Declare variables (early bind)
    Dim xDoc As New MSXML2.DOMDocument60
    Dim xNodes As MSXML2.IXMLDOMNodeList
    Set xDoc = New MSXML2.DOMDocument60
    Dim DomNode As IXMLDOMNode
    'Dim node As IXMLDOMElement
    
    'Setup Namespace
    xDoc.SetProperty "SelectionNamespaces", _
        "xmlns:qdt='urn:un:unece:uncefact:data:standard:QualifiedDataType:100' " & _
        "xmlns:ram='urn:un:unece:uncefact:data:standard:ReusableAggregateBusinessInformationEntity:100' " & _
        "xmlns:udt='urn:un:unece:uncefact:data:standard:UnqualifiedDataType:100' " & _
        "xmlns:rsm='urn:un:unece:uncefact:data:standard:CrossIndustryInvoice:100' "
    
    Dim strXML As String: strXML = "C:\Users\JohnDoe\Desktop\factur-x.xml"
    
    With xDoc
        .async = False
        .validateOnParse = True
        If xDoc.Load(strXML) = False Then
            Debug.Print .parseError.reason, .parseError.ErrorCode
            Exit Sub
        End If
        
        Set xNodes = xDoc.SelectNodes("//ram:IncludedSupplyChainTradeLineItem")
        Debug.Print xNodes.Length '--> 2
        
        For Each DomNode In xNodes
            Debug.Print DomNode.SelectSingleNode("//ram:SellerAssignedID").Text '--> result two times: 111111
        Next
    End With
End Sub

The XML-Doc:

<?xml version="1.0" encoding="utf-8"?>
<rsm:CrossIndustryInvoice xmlns:qdt="urn:un:unece:uncefact:data:standard:QualifiedDataType:100" xmlns:ram="urn:un:unece:uncefact:data:standard:ReusableAggregateBusinessInformationEntity:100" xmlns:udt="urn:un:unece:uncefact:data:standard:UnqualifiedDataType:100" xmlns:rsm="urn:un:unece:uncefact:data:standard:CrossIndustryInvoice:100">
    <rsm:SupplyChainTradeTransaction>
        <ram:IncludedSupplyChainTradeLineItem>
            <ram:AssociatedDocumentLineDocument>
                <ram:LineID>1</ram:LineID>
            </ram:AssociatedDocumentLineDocument>
            <ram:SpecifiedTradeProduct>
                <ram:SellerAssignedID>111111</ram:SellerAssignedID>
                <ram:Name>Artikel AAA</ram:Name>
                <ram:Description>Artikel AAA 125 x 0,75 m</ram:Description>
            </ram:SpecifiedTradeProduct>
            <ram:SpecifiedLineTradeAgreement>
                <ram:GrossPriceProductTradePrice>
                    <ram:ChargeAmount>62.4600</ram:ChargeAmount>
                    <ram:BasisQuantity unitCode="NAR">1.0000</ram:BasisQuantity>
                    <ram:AppliedTradeAllowanceCharge>
                        <ram:ChargeIndicator>
                            <udt:Indicator>false</udt:Indicator>
                        </ram:ChargeIndicator>
                        <ram:ActualAmount>10.6182</ram:ActualAmount>
                    </ram:AppliedTradeAllowanceCharge>
                </ram:GrossPriceProductTradePrice>
                <ram:NetPriceProductTradePrice>
                    <ram:ChargeAmount>51.8418</ram:ChargeAmount>
                    <ram:BasisQuantity unitCode="NAR">1.0000</ram:BasisQuantity>
                </ram:NetPriceProductTradePrice>
            </ram:SpecifiedLineTradeAgreement>
            <ram:SpecifiedLineTradeDelivery>
                <ram:BilledQuantity unitCode="NAR">2.0000</ram:BilledQuantity>
            </ram:SpecifiedLineTradeDelivery>
            <ram:SpecifiedLineTradeSettlement>
                <ram:ApplicableTradeTax>
                    <ram:TypeCode>VAT</ram:TypeCode>
                    <ram:CategoryCode>S</ram:CategoryCode>
                    <ram:RateApplicablePercent>19.00</ram:RateApplicablePercent>
                </ram:ApplicableTradeTax>
                <ram:SpecifiedTradeSettlementLineMonetarySummation>
                    <ram:LineTotalAmount>103.68</ram:LineTotalAmount>
                </ram:SpecifiedTradeSettlementLineMonetarySummation>
            </ram:SpecifiedLineTradeSettlement>
        </ram:IncludedSupplyChainTradeLineItem>
        <ram:IncludedSupplyChainTradeLineItem>
            <ram:AssociatedDocumentLineDocument>
                <ram:LineID>2</ram:LineID>
            </ram:AssociatedDocumentLineDocument>
            <ram:SpecifiedTradeProduct>
                <ram:SellerAssignedID>222222</ram:SellerAssignedID>
                <ram:Name>Artikel BBB</ram:Name>
                <ram:Description>Artikel BBB 750 g</ram:Description>
            </ram:SpecifiedTradeProduct>
            <ram:SpecifiedLineTradeAgreement>
                <ram:GrossPriceProductTradePrice>
                    <ram:ChargeAmount>12.2300</ram:ChargeAmount>
                    <ram:BasisQuantity unitCode="C62">1.0000</ram:BasisQuantity>
                    <ram:AppliedTradeAllowanceCharge>
                        <ram:ChargeIndicator>
                            <udt:Indicator>false</udt:Indicator>
                        </ram:ChargeIndicator>
                        <ram:ActualAmount>3.1798</ram:ActualAmount>
                    </ram:AppliedTradeAllowanceCharge>
                </ram:GrossPriceProductTradePrice>
                <ram:NetPriceProductTradePrice>
                    <ram:ChargeAmount>9.0502</ram:ChargeAmount>
                    <ram:BasisQuantity unitCode="C62">1.0000</ram:BasisQuantity>
                </ram:NetPriceProductTradePrice>
            </ram:SpecifiedLineTradeAgreement>
            <ram:SpecifiedLineTradeDelivery>
                <ram:BilledQuantity unitCode="C62">5.0000</ram:BilledQuantity>
            </ram:SpecifiedLineTradeDelivery>
            <ram:SpecifiedLineTradeSettlement>
                <ram:ApplicableTradeTax>
                    <ram:TypeCode>VAT</ram:TypeCode>
                    <ram:CategoryCode>S</ram:CategoryCode>
                    <ram:RateApplicablePercent>19.00</ram:RateApplicablePercent>
                </ram:ApplicableTradeTax>
                <ram:SpecifiedTradeSettlementLineMonetarySummation>
                    <ram:LineTotalAmount>45.25</ram:LineTotalAmount>
                </ram:SpecifiedTradeSettlementLineMonetarySummation>
            </ram:SpecifiedLineTradeSettlement>
        </ram:IncludedSupplyChainTradeLineItem>
    </rsm:SupplyChainTradeTransaction>
</rsm:CrossIndustryInvoice>

编辑：仍然需要注意的是，我需要遍历每个 ram：IncludedSupplyChainTradeLineItem 元素，以验证是否已提交所有主数据。

仅仅收集可在 XML 文档中找到的所有现有主数据是不够的。

VBA XPath XML 分析 MSXML

代码的问题出在循环中提取 ram：SellerAssignedID 的行上。在循环中使用 SelectSingleNode 时，它每次都会从 XML 文档的根目录中查找路径。这会导致它再次搜索整个文档，而不是在当前节点（即每个 ram：IncludedSupplyChainTradeLineItem）中搜索。若要解决此问题，请使用当前节点（DomNode）的上下文提取 ram：SellerAssignedID。以下是更正后的代码：

Sub getMetaDataFromXmlFile()
    'Declare variables (early bind)
    Dim xDoc As New MSXML2.DOMDocument60
    Dim xNodes As MSXML2.IXMLDOMNodeList
    Set xDoc = New MSXML2.DOMDocument60
    Dim DomNode As IXMLDOMNode

    'Setup Namespace
    xDoc.SetProperty "SelectionNamespaces", _
        "xmlns:qdt='urn:un:unece:uncefact:data:standard:QualifiedDataType:100' " & _
        "xmlns:ram='urn:un:unece:uncefact:data:standard:ReusableAggregateBusinessInformationEntity:100' " & _
        "xmlns:udt='urn:un:unece:uncefact:data:standard:UnqualifiedDataType:100' " & _
    Dim strXML As String: strXML = "C:\Users\JohnDoe\Desktop\factur-x.xml"

    With xDoc
        .async = False
        .validateOnParse = True
        If xDoc.Load(strXML) = False Then
            Debug.Print .parseError.reason, .parseError.ErrorCode
            Exit Sub
        End If
        
        Set xNodes = xDoc.SelectNodes("//ram:IncludedSupplyChainTradeLineItem")
        Debug.Print xNodes.Length '--> 2
        
        For Each DomNode In xNodes
            Debug.Print DomNode.SelectSingleNode("ram:SpecifiedTradeProduct/ram:SellerAssignedID").Text
        Next
    End With
End Sub

在 DomNode.SelectSingleNode（“ram：SpecifiedTradeProduct/ram：SellerAssignedID”）中。文本，我正在使用当前节点 DomNode 的上下文来查找 ram：SpecifiedTradeProduct，然后在该上下文中查找 ram：SellerAssignedID。

此修改应根据需要输出预期值：111111 和 222222。

它有效 - 谢谢。我试着理解你的方法。在开头做双斜杠 -- DomNode.SelectSingleNode（“//ram：SellerAssignedID”）。文本 -- 与我的错误有关吗？另外：在我到达所需节点之前，不放出任何其他子节点（在本例中为 <ram：SpecifiedTradeProduct>）似乎很重要？

1赞 Martin Honnen 10/29/2023

@smartini，是的，如果你使用，你找第一个在完整的文档中属于。要仅搜索的后代，请使用例如或例如。DomNode.SelectSingleNode("//ram:SellerAssignedID")SellerAssignedIDDomNodeDomNodeDomNode.SelectSingleNode("descendant::ram:SellerAssignedID")DomNode.SelectSingleNode(".//ram:SellerAssignedID")

0赞 smartini 10/29/2023

@MartinHonnen，太好了 - 谢谢你的赞美。

上一个：Oracle 哈希函数

下一个：调用 jira.issueviews：issue-xml 前的身份验证

VBA 中的 XML 解析问题：从 NodeList 中提取多个 Node 值

Trouble with XML parsing in VBA: Extracting multiple Node values from NodeList

评论

评论