This XML document below is an invoice with order items.
Various master data must be extracted for each order item.
For the sake of simplicity, we will only consider the <ram:SellerAssignedID>.
Each order item has a header tag <ram:IncludedSupplyChainTradeLineItem>.
My idea is to use the MSXML2.IXMLDOMNodeList object to collect all the header tags (nodes) together. Then I would loop through the items (node length here is 2, because the header tag occurs two times) and extract the <ram:SellerAssignedID>.
Below my VBA code so far.
However, when this code runs through it returns the wrong results.
It makes it seem like only the first item in the NodeList is considered.
I keep getting in the Debug.Print statement only the first <ram:SellerAssignedID> instead of each.
Result:
111111
111111
Expectation:
111111
222222
What am I doing wrong?
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>
Edit: It is still important to note that I need to go through each ram:IncludedSupplyChainTradeLineItem element to verify that all master data has been submitted.
It is not enough to simply collect all the existing master data that can be found in the XML document.
The issue with your code lies in the line within the loop that extracts the ram:SellerAssignedID. When you use SelectSingleNode within the loop, it's looking for the path from the root of the XML document each time. This causes it to search the entire document again instead of searching within the current node (which is each ram:IncludedSupplyChainTradeLineItem). To resolve this, use the context of the current node (DomNode) to extract the ram:SellerAssignedID. Here's the corrected code:
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
In the DomNode.SelectSingleNode("ram:SpecifiedTradeProduct/ram:SellerAssignedID").Text, I'm using the context of the current node DomNode to find ram:SpecifiedTradeProduct and then ram:SellerAssignedID within that context.
This modification should output the expected values: 111111 and 222222, as you desire.