小贝子编程

使用VBA进行Xpath选择节点



我正在尝试从一些XML中提取sku值，

我不能像平常那样遍历XML，因为根据从Ebay返回XML的方式，如果订单中有超过一个SKU，则只解析第一个SKU。因此，我需要一种方法来捕获那些在订单上有多个SKU的订单(这很简单，因为这样的订单在OrderID字段中没有连字符)，但随后需要一个命令来遍历被捕获的OrderID的SKU。这是XML(个人数据更改)

<?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>sales@google.com</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>steve@yahootest.it</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>steve@yahootest.it</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>

在上面的XML中，有一个OrderID和两个sku(通常有更多的订单，但为了消除"噪音"，我将XML剥离回只有一个订单)....印有SKU t- 001 &另一件印有SKU ts-002的t恤…作为第一步，我想通过OrderID 216583631017来计算有多少个sku

这是我的(剥离)代码…

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

您的XPath应该是:

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

请注意:XPath区分大小写。

我不认为这个问题还困扰着你，我也不太明白你到底需要什么。

你可以从xml中获取所有的Order节点:

/GetOrdersResponse/OrderArray/Order

然后每个Order节点你可以用

得到所有SKU

TransactionArray/Transaction/Item/SKU

或者如果你只是对所有的SKU感兴趣:

//Order/TransactionArray/Transaction/Item/SKU

或者只是包含多个SKU的订单:

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

相关内容

  • 没有找到相关文章

最新更新


热门标签：

javascript python java c# php android html jquery c++ css ios sql mysql arrays asp.net json python-3.x ruby-on-rails .net sql-server django objective-c excel regex ruby linux ajax iphone xml vba spring asp.net-mvc database wordpress string postgresql wpf windows xcode bash git oracle list vb.net multithreading eclipse algorithm macos powershell visual-studio image forms numpy scala function api selenium