Reputation: 63

Xpath selectnodes using VBA

I'm trying to pull sku values out of some XML,

I can't iterate through the XML as I normally do, because due to the way the XML is returned from Ebay, if there is more than on SKU in the order, only the first SKU will be parsed. Therefore, I need a way of trapping those orders which have more than one SKU on the the order (which is simple enough, becuase such orders don't have a hyphen in the OrderID field), but then a command to iterate through the skus for such OrderIDs trapped. Here's the XML (personal data changed)

<?xml version="1.0"?>
<GetOrdersResponse
xmlns="urn:ebay:apis:eBLBaseComponents">
<Timestamp>2016-10-08T17:53:47.349Z</Timestamp>
<Ack>Success</Ack>
<Version>987</Version>
<Build>E987_INTL_APIXO_18127637_R1</Build>
<PaginationResult>
    <TotalNumberOfPages>1</TotalNumberOfPages>
    <TotalNumberOfEntries>1</TotalNumberOfEntries>
</PaginationResult>
<HasMoreOrders>false</HasMoreOrders>
<OrderArray>
    <Order>
        <OrderID>214583631017</OrderID>
        <OrderStatus>Completed</OrderStatus>
        <AdjustmentAmount currencyID="GBP">0.0</AdjustmentAmount>
        <AmountPaid currencyID="GBP">23.76</AmountPaid>
        <AmountSaved currencyID="GBP">0.0</AmountSaved>
        <CheckoutStatus>
            <eBayPaymentStatus>NoPaymentFailure</eBayPaymentStatus>
            <LastModifiedTime>2016-10-08T14:24:38.000Z</LastModifiedTime>
            <PaymentMethod>PayPal</PaymentMethod>
            <Status>Complete</Status>
                 <IntegratedMerchantCreditCardEnabled>false</IntegratedMerchantCreditCardEnabled>
        </CheckoutStatus>
        <ShippingDetails>
            <SalesTax>
                <SalesTaxPercent>0.0</SalesTaxPercent>
                <SalesTaxState></SalesTaxState>
                <ShippingIncludedInTax>false</ShippingIncludedInTax>
                <SalesTaxAmount currencyID="GBP">0.0</SalesTaxAmount>
            </SalesTax>
            <InternationalShippingServiceOption>
                <ShippingService>UK_RoyalMailAirmailInternational</ShippingService>
                <ShippingServiceCost currencyID="GBP">5.78</ShippingServiceCost>
                <ShippingServicePriority>1</ShippingServicePriority>
            </InternationalShippingServiceOption>
            <SellingManagerSalesRecordNumber>20937</SellingManagerSalesRecordNumber>
            <GetItFast>false</GetItFast>
        </ShippingDetails>
        <CreatingUserRole>Buyer</CreatingUserRole>
        <CreatedTime>2016-10-08T14:22:45.000Z</CreatedTime>
        <PaymentMethods>CCAccepted</PaymentMethods>
        <PaymentMethods>PayPal</PaymentMethods>
        <SellerEmail>[email protected]</SellerEmail>
        <ShippingAddress>
            <Name>Doy.ssl Garbarina Francesca</Name>
            <Street1>Via Goossman,23</Street1>
            <Street2></Street2>
            <CityName>Rome</CityName>
            <StateOrProvince>MI</StateOrProvince>
            <Country>IT</Country>
            <CountryName>Italy</CountryName>
            <Phone>320713385</Phone>
            <PostalCode>22119</PostalCode>
            <AddressID>1997656621018</AddressID>
            <AddressOwner>eBay</AddressOwner>
            <ExternalAddressID></ExternalAddressID>
        </ShippingAddress>
        <ShippingServiceSelected>
            <ShippingService>UK_RoyalMailAirmailInternational</ShippingService>
            <ShippingServiceCost currencyID="GBP">5.78</ShippingServiceCost>
        </ShippingServiceSelected>
        <Subtotal currencyID="GBP">17.98</Subtotal>
        <Total currencyID="GBP">23.76</Total>
        <TransactionArray>
            <Transaction>
                <Buyer>
                    <Email>[email protected]</Email>
                    <UserFirstName>Ted Alfy</UserFirstName>
                    <UserLastName>La Guff</UserLastName>
                </Buyer>
                <ShippingDetails>
                    <SellingManagerSalesRecordNumber>21935</SellingManagerSalesRecordNumber>
                </ShippingDetails>
                <CreatedDate>2016-10-08T14:22:45.000Z</CreatedDate>
                <Item>
                    <ItemID>252071330119</ItemID>
                    <Site>UK</Site>
                    <Title>T Shirt </Title>
                    <SKU>ts-001</SKU>
                    <ConditionID>1000</ConditionID>
                    <ConditionDisplayName>New</ConditionDisplayName>
                </Item>
                <QuantityPurchased>1</QuantityPurchased>
                <Status>
                    <PaymentHoldStatus>None</PaymentHoldStatus>
                    <InquiryStatus>NotApplicable</InquiryStatus>
                    <ReturnStatus>NotApplicable</ReturnStatus>
                </Status>
                <TransactionID>1927179184015</TransactionID>
                <TransactionPrice currencyID="GBP">7.99</TransactionPrice>
                <ShippingServiceSelected>
                    <ShippingPackageInfo>
                        <EstimatedDeliveryTimeMin>2016-10-12T22:00:00.000Z</EstimatedDeliveryTimeMin>
                        <EstimatedDeliveryTimeMax>2016-10-17T22:00:00.000Z</EstimatedDeliveryTimeMax>
                    </ShippingPackageInfo>
                </ShippingServiceSelected>
                <TransactionSiteID>Italy</TransactionSiteID>
                <Platform>eBay</Platform>
                <Taxes>
                    <TotalTaxAmount currencyID="GBP">0.0</TotalTaxAmount>
                    <TaxDetails>
                        <Imposition>SalesTax</Imposition>
                        <TaxDescription>SalesTax</TaxDescription>
                        <TaxAmount currencyID="GBP">0.0</TaxAmount>
                        <TaxOnSubtotalAmount currencyID="GBP">0.0</TaxOnSubtotalAmount>
                        <TaxOnShippingAmount currencyID="GBP">0.0</TaxOnShippingAmount>
                        <TaxOnHandlingAmount currencyID="GBP">0.0</TaxOnHandlingAmount>
                    </TaxDetails>
                    <TaxDetails>
                        <Imposition>WasteRecyclingFee</Imposition>
                        <TaxDescription>ElectronicWasteRecyclingFee</TaxDescription>
                        <TaxAmount currencyID="GBP">0.0</TaxAmount>
                    </TaxDetails>
                </Taxes>
                <OrderLineItemID>252171600110-1928179174015</OrderLineItemID>
                <ExtendedOrderID>216483631017!739847967018</ExtendedOrderID>
                <eBayPlusTransaction>false</eBayPlusTransaction>
            </Transaction>
            <Transaction>
                <Buyer>
                    <Email>[email protected]</Email>
                    <UserFirstName>Ted Alfy</UserFirstName>
                    <UserLastName>La Guff</UserLastName>
                </Buyer>
                <ShippingDetails>
                    <SellingManagerSalesRecordNumber>21935</SellingManagerSalesRecordNumber>
                </ShippingDetails>
                <CreatedDate>2016-10-08T14:22:45.000Z</CreatedDate>
                <Item>
                    <ItemID>252072320819</ItemID>
                    <Site>UK</Site>
                    <Title>T Shirt </Title>
                    <SKU>ts-002</SKU>
                    <ConditionID>1000</ConditionID>
                    <ConditionDisplayName>New</ConditionDisplayName>
                </Item>
                <QuantityPurchased>1</QuantityPurchased>
                <Status>
                    <PaymentHoldStatus>None</PaymentHoldStatus>
                    <InquiryStatus>NotApplicable</InquiryStatus>
                    <ReturnStatus>NotApplicable</ReturnStatus>
                </Status>
                <TransactionID>1894939757016</TransactionID>
                <TransactionPrice currencyID="GBP">9.99</TransactionPrice>
                <ShippingServiceSelected>
                    <ShippingPackageInfo>
                        <EstimatedDeliveryTimeMin>2016-10-12T22:00:00.000Z</EstimatedDeliveryTimeMin>
                        <EstimatedDeliveryTimeMax>2016-10-17T22:00:00.000Z</EstimatedDeliveryTimeMax>
                    </ShippingPackageInfo>
                </ShippingServiceSelected>
                <TransactionSiteID>Italy</TransactionSiteID>
                <Platform>eBay</Platform>
                <Taxes>
                    <TotalTaxAmount currencyID="GBP">0.0</TotalTaxAmount>
                    <TaxDetails>
                        <Imposition>SalesTax</Imposition>
                        <TaxDescription>SalesTax</TaxDescription>
                        <TaxAmount currencyID="GBP">0.0</TaxAmount>
                        <TaxOnSubtotalAmount currencyID="GBP">0.0</TaxOnSubtotalAmount>
                        <TaxOnShippingAmount currencyID="GBP">0.0</TaxOnShippingAmount>
                        <TaxOnHandlingAmount currencyID="GBP">0.0</TaxOnHandlingAmount>
                    </TaxDetails>
                    <TaxDetails>
                        <Imposition>WasteRecyclingFee</Imposition>
                        <TaxDescription>ElectronicWasteRecyclingFee</TaxDescription>
                        <TaxAmount currencyID="GBP">0.0</TaxAmount>
                    </TaxDetails>
                </Taxes>
                <OrderLineItemID>262002331873-1894939957016</OrderLineItemID>
                <ExtendedOrderID>216583731017!734847937018</ExtendedOrderID>
                <eBayPlusTransaction>false</eBayPlusTransaction>
            </Transaction>
        </TransactionArray>
        <BuyerUserID>xyz123</BuyerUserID>
        <PaidTime>2016-10-08T14:22:45.000Z</PaidTime>
        <IntegratedMerchantCreditCardEnabled>false</IntegratedMerchantCreditCardEnabled>
        <EIASToken>nY+sHZ2PrBmdj6wVyY+sEZ2PrA3dj6wGkYSiAZ2LpASdj6x9nY+seQ==</EIASToken>
        <PaymentHoldStatus>None</PaymentHoldStatus>
        <IsMultiLegShipping>false</IsMultiLegShipping>
        <SellerUserID>pht01</SellerUserID>
        <SellerEIASToken>nY+sHZ2PrBmdj6wVneY+sEZ2PrA2dj6wFlIOpDZeApAudj6x9nY+seQ==</SellerEIASToken>
        <CancelStatus>NotApplicable</CancelStatus>
        <ExtendedOrderID>216589641017!734857936018</ExtendedOrderID>
        <ContainseBayPlusTransaction>false</ContainseBayPlusTransaction>
    </Order>
</OrderArray>
<OrdersPerPage>100</OrdersPerPage>
<PageNumber>1</PageNumber>
<ReturnedOrderCountActual>8</ReturnedOrderCountActual>

in the above XML, there is one OrderID with two SKUs (normally there are many more orders, but to remove 'noise' I've stripped the XML back to be just one order)....a t-shirt with the SKU ts-001 & another t-shirt with the SKU ts-002 ...As a first step I seek some way of counting how many SKUs there are by the OrderID of 216583631017

Here's my (stripped back) code...

Dim objxmldoc As New MSXML2.DOMDocument60
Dim xmlNamespaces As String
xmlNamespaces = "xmlns:ebay='urn:ebay:apis:eBLBaseComponents'"
objxmldoc.SetProperty "SelectionNamespaces", xmlNamespaces
objxmldoc.SetProperty "SelectionLanguage", "XPath"

If InStr(OrderID, "-") = 0 Then  'if no hyphen in the OrderID, then it's a multi item order...
  Set xmlNodes = objxmldoc.selectNodes("//ebay:OrderID[@OrderID='216583631017']")
  Debug.Print "Total Number of nodes selected: " & xmlNodes.length ' show how many of the trapped info was counted
 end if

Upvotes: 0

Answers (2)

Piemol

Reputation: 896

I don't think this question is still bothering you, and I don't understand exactly what you needed.

You can get all Order nodes from the xml with:

/GetOrdersResponse/OrderArray/Order

And then per Order node you can get all SKU's with:

TransactionArray/Transaction/Item/SKU

Or if you're just interested in all SKU's at once:

//Order/TransactionArray/Transaction/Item/SKU

Or just the Orders that have multiple SKU:

/GetOrdersResponse/OrderArray/Order[count(TransactionArray/Transaction/Item/SKU) gt 1]

Upvotes: 0

Kirill Polishchuk

Reputation: 56162

You XPath should be:

//ebay:Order[OrderID = '216583631017']

Please note: XPath is case-sensitive.

Upvotes: 0

Xpath selectnodes using VBA

Answers (2)

Related Questions