Parsing XML - Xpath/Child nodes problem (any XML experts here?)

P

peskywinnets

Registered User.
Local time
Today, 05:42
Joined
Feb 4, 2014
Messages
578
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, because 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)

Code: 
    <?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>[COLOR="red"]214583631017[/COLOR]</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>
						[COLOR="red"]<SKU>[/COLOR]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>
						[COLOR="Red"]<SKU>[/COLOR]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>
</GetOrdersResponse>

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)....I've highlighted/coloured the field names of interest in red 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...
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...
    [COLOR="Magenta"]  Set xmlNodes = objxmldoc.selectNodes("//ebay:OrderID[@OrderID='216583631017']") '<<this syntax is the bit I'm struggling with[/COLOR]
      Debug.Print "Total Number of nodes selected: " & xmlNodes.length ' show how many of the trapped info was counted
     end if

Top tips warmly received re the correct syntax to select OrderID with a value of 216583631017 & then count the number of child SKU entries relating to that OrderID (which should be two)
 
Last edited:
Pesky..
I am not familiar with xml, but have made a file of your xml and have tried to find examples for parsing and traversing the document. I can not find examples using vba. After some reading and searching I came up with this.

I offer the following code to find the Item info and the SKU info. I realize it is very basic, but hopefully it is useful to you. If any readers have good examples of parsing xml with Access vba or any related tutorials, I would like to see same. There certainly seems to be a lack of such info available.

Are you sure that it is OrderID that has the hyphen for multi-item order??? I ask because I see OrderLineItemID with hyphen, not OrderID???

The output to the immediate window is
Code: 
Item  252071330119 UK T Shirt  ts-001 1000 New
SKU  ts-001
Item  252072320819 UK T Shirt  ts-002 1000 New
SKU  ts-002

The program:
Code: 
'---------------------------------------------------------------------------------------
' Procedure : Ebay_pesky_XML
' Author    : mellon
' Date      : 10-Oct-2016
' Purpose   :from http://www.access-programmers.co.uk/forums/showthread.php?t=289814&highlight=ebay
'
'Trying to understand xml parsing [have not found examples with vba]
'---------------------------------------------------------------------------------------
'
Sub Ebay_pesky_XML()

    Dim xml As New MSXML2.DOMDocument60
    Dim nList As MSXML2.IXMLDOMNodeList
    Dim n As IXMLDOMNode
    Dim i As Integer

10  On Error GoTo Ebay_pesky_XML_Error

20  xml.Load ("C:\users\mellon\Documents\Ebay.xml")

    ' Debug.Print xml.xml

30  Set nList = xml.getElementsByTagName("*")       'this finds all nodes

40  For i = 0 To nList.Length - 1
50      If nList(i).nodename = "Item" Or _                    'this finds only Item or SKU
           nList(i).nodename = "SKU" Then
60          Debug.Print nList(i).nodename & "  " & nList(i).text  
' you could add logic here to do something with the found values
70      End If
80  Next
90  On Error GoTo 0
100 Exit Sub

Ebay_pesky_XML_Error:

110 MsgBox "Error " & err.number & " in line " & Erl & " (" & err.Description & ") in procedure Ebay_pesky_XML of Module xmlhttp_Etc"

End Sub
 
Last edited:
Firstly...wow thanks...I wasn't expect anyone to go to this effort. You are right, there's scant info about parsing XML using VBA, the bits I've learnt has been by brute force trial & error & a lot of kludging bits learnt from here & there!

I'm trying your code & can only get it to run on some simplified XML....

Code: 
<orderarray>
	<order>
		<orderid>1</orderid>
		<transactionarray>
			<transaction>
				<item>
					<SKU>xy-01</SKU>
				</item>
			</transaction>
			<transaction>
				<item>
					<SKU>xy-02</SKU>
				</item>
			</transaction>
		</transactionarray>
	</order>
	<order>
		<orderid>2</orderid>
		<transactionarray>
			<transaction>
				<item>
					<SKU>xy-03</SKU>
				</item>
			</transaction>
		</transactionarray>
	</order>
	<order>
		<orderid>3</orderid>
		<transactionarray>
			<transaction>
				<item>
					<SKU>xy-04</SKU>
				</item>
			</transaction>
		</transactionarray>
	</order>
</orderarray>

when I use the actual XML from ebay with your code, which looks like this....
Code: 
<?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>

there's nothing showing in the VBA immediate window when this command is reached in your code when the Ebay XML is used/loaded...

Code: 
 Debug.Print xml.xml

...could it be to do with the ebay namespaces within the ebay XML? (as an aside, Xpath commands with namespaces/URNs...I don't think I'll ever wrap my head around that one!)

jdraw said:
Are you sure that it is OrderID that has the hyphen for multi-item order??? I ask because I see OrderLineItemID with hyphen, not OrderID???
Click to expand...

Yes I'm sure....all OrderID with a hyphen embedded in the middle of the OrderID value are single item orders (e.g. 261187444317-1896098455016)...they are much longer, whereas those that are shorter with no hypen are multi-item orders (e.g. 216663888017) .

But anyway, perhaps we can park up the larger ebay XML for a moment & for the sake of clarity/simplicity just focus on the simple XML I posted above in this post. I actually need the output to be along these lines (I only need OrderID & the SKU name...

OrderID 1 SKU xy-01
OrderID 1 SKU xy-02
OrderID 2 SKU xy-03
OrderID 3 SKU xy-04

...which I guess is just a rejigging of the output format from the parsed XML...I have to dash out for a short while so will look at this when I get back - I thank you once again, I've spent 4 evenings on this without success!!
 
Last edited:
Please post the entire xml you are working with.

I developed the code based on your original xml.
 
jdraw said:
Please post the entire xml you are working with.

I developed the code based on your original xml.
Click to expand...

I'm using the same XML pasted in my opening post (sourced from Ebay). I've pasted into a file on my desktop called test1.xml, I'm using this first part of your code....

Code: 
xml.Load ("C:/Users/pesky/Desktop/[B]test1.xml[/B]")
Debug.Print [B]xml.xml[/B]

but seeing nothing!

Are you seeing the xml print onscreen when you run your code? (i.e. at the debug.print command?)
 
Last edited:
The Debug.Print command will print into the immediate window.

Hit cntl-G to get to vbe and make sure you have the Immediate Window visible.

Here is a jpg of my window after running the code. I uncommented the debug.print xml.xml
 

Attachments

  • Pesky_Debug.jpg
    Pesky_Debug.jpg
    101.9 KB · Views: 489
How bizarre.

I've copied the XML in my initial post (i.e. the XML from ebay - the same XML that you used to code with) into a file called test1.xml ....I've now put it on my c drive root, but still the debug print is not printing the xml out (see attached jp called test1.jpg)

However, when I put this basic (slimmed down) XML into a file...

Code: 
<orderarray>
	<order>
		<orderid>1</orderid>
		<transactionarray>
			<transaction>
				<item>
					<SKU>xy-01</SKU>
				</item>
			</transaction>
			<transaction>
				<item>
					<SKU>xy-02</SKU>
				</item>
			</transaction>
		</transactionarray>
	</order>
	<order>
		<orderid>2</orderid>
		<transactionarray>
			<transaction>
				<item>
					<SKU>xy-03</SKU>
				</item>
			</transaction>
		</transactionarray>
	</order>
	<order>
		<orderid>3</orderid>
		<transactionarray>
			<transaction>
				<item>
					<SKU>xy-04</SKU>
				</item>
			</transaction>
		</transactionarray>
	</order>
</orderarray>


......called test.xml & do the same...I see the XML print successfully in my immediate window (see attached jpg called test.jpg)...like I say, it seems to be something to do with the xml ....but in the light that it's working for you, I'm perplexed!
 

Attachments

  • test1.jpg
    test1.jpg
    53.8 KB · Views: 489
  • test.jpg
    test.jpg
    56.5 KB · Views: 487
Ok, back to basics, using this simple XML (which is sort of a replica of the ebay XML, just stripped down to have the fields of interest)....

Code: 
<orderarray>
	<order>
		<orderid>[COLOR="Red"]1[/COLOR]</orderid>
		<transactionarray>
			<transaction>
				<item>
					<SKU>[COLOR="red"]xy-01[/COLOR]</SKU>
				</item>
			</transaction>
			<transaction>
				<item>
					<SKU>[COLOR="red"]xy-02[/COLOR]</SKU>
				</item>
			</transaction>
		</transactionarray>
	</order>
	<order>
		<orderid>2</orderid>
		<transactionarray>
			<transaction>
				<item>
					<SKU>xy-03</SKU>
				</item>
			</transaction>
		</transactionarray>
	</order>
	<order>
		<orderid>3</orderid>
		<transactionarray>
			<transaction>
				<item>
					<SKU>xy-04</SKU>
				</item>
			</transaction>
		</transactionarray>
	</order>
</orderarray>

The following code seems to work for pulling out the SKU values for a nominated specific OrderID....

Code: 
Public Sub test()

Dim objxmldoc As New MSXML2.DOMDocument60
objxmldoc.Load ("C:\Users\pesky\Desktop\test.xml")

Debug.Print objxmldoc.xml

Set Ordercount = objxmldoc.selectNodes("//order[orderid='1']")
Set SKUXML = objxmldoc.selectNodes("//order[orderid='1']/transactionarray/transaction/item/SKU")

Debug.Print "Total Number of Orders Selected : " & Ordercount.length 
Debug.Print "Total Number of SKUs in Selected Order: " & SKUXML.length

R=0
For Each node In SKUXML
Debug.Print SKUXML(R).text
R = R + 1
Next
End Sub

....I've just got to work out a way to apply it to the proper ebay XML now.
 
Last edited:
Pesky..

OK. After some more searching I found some older material that I have adapted. Still not
understanding how to parse the DOM properly or with some fancy code to get me
to the proper node. But the DisplayNode sub does most of the work.

The result, using the ebay.xml I created based on your first post, is
Code: 
OrderID 214583631017
SKU ts-001
SKU ts-002


Hope it's helpful.


Code: 
'---------------------------------------------------------------------------------------
' Procedure : LoadXMLDocument
' Author    : mellon
' Date      : 10/10/2016
' Purpose   : This  loads and parses the ebay xml document provided.
'
'attempt to learn more on parse xml with vba
'---------------------------------------------------------------------------------------
'
Public Sub LoadXMLDocument()
      Dim xdoc As DOMDocument60
      Dim xmldoc As String

10    xmldoc = "c:\users\mellon\documents\ebay.xml"
20    Set xdoc = New DOMDocument60
30    xdoc.validateOnParse = False
40    If xdoc.Load(xmldoc) Then '
         ' The document loaded successfully.
         ' Now do something interesting.
         ' Debug.Print xdoc.xml  '<---for testing
50        DisplayNode xdoc.childNodes, 0, xdoc

60    Else
        [B] ' The document failed to load.
         Dim strErrText As String
         Dim xPE As MSXML2.IXMLDOMParseError
         ' Obtain the ParseError object[/B]
70       Set xPE = xdoc.parseError
80       With xPE
90          strErrText = "Your XML Document failed to load" & _
              "due the following error." & vbCrLf & _
              "Error #: " & .errorCode & ": " & xPE.reason & _
              "Line #: " & .Line & vbCrLf & _
              "Line Position: " & .linepos & vbCrLf & _
              "Position In File: " & .filepos & vbCrLf & _
              "Source Text: " & .srcText & vbCrLf & _
              "Document URL: " & .URL
100       End With

110       MsgBox strErrText, vbExclamation
120   End If

130   Set xPE = Nothing

End Sub


Code: 
'---------------------------------------------------------------------------------------
' Procedure : DisplayNode
' Author    : mellon
' Date      : 10-Oct-2016
' Purpose   : modified this code to find the Order and SKU information in the
' response information from ebay (my  ebay.xml)
'---------------------------------------------------------------------------------------
'
Public Sub DisplayNode(ByRef Nodes As MSXML2.IXMLDOMNodeList, _
                       ByVal Indent As Integer, xdoc As DOMDocument60)

'NOTE:  These Dims were in the original code. I did not remove them. I did remove some logic dealing
'=====   with attribute processing logic, since there were no attributes in the ebay question (from pesky).
    Dim xNode As IXMLDOMNode
    Dim oNode As IXMLDOMNode
    Dim xattr As IXMLDOMAttribute
    Dim xattr1 As IXMLDOMAttribute
    Dim xElem As IXMLDOMElement
    Dim strSQL As String
    Dim strSQL1 As String

10  Indent = Indent + 1

20  On Error Resume Next

30  For Each xNode In Nodes
 'If we find an Order, print the OrderID
40      If xNode.nodename = "OrderID" Then
50          Debug.Print xNode.nodename & " " & xNode.text
60      End If

'  We should find a SKU  or SKUs associated with the Order
'   print the SKU(s)
70      If xNode.nodename = "SKU" Then
80          Debug.Print xNode.nodename & " " & xNode.text
90      End If
        

100     If xNode.hasChildNodes Then
110         DisplayNode xNode.childNodes, Indent, xdoc

120     End If
130 Next xNode

End Sub
 
Last edited:
Wow, that works (thanks - I've also run it on some much larger live data retrieved from Ebay - success!), but it's blown my mind...I've not got a clue what's going on, as I step through it, it's all over the place & I get brain fuzz!

the parsed output has exactly the data content I need, except I need it slightly re-arranged from this format...

Code: 
OrderID 214583631017
SKU ts-001
SKU ts-002

to be in this format...
Code: 
214583631017  ts-001
214583631017  ts-002

This is becuase the parsed XML output is ultimately to be piped into a csv file. I will be using an ofile.write type command to structure the data to like this...

Code: 
[B]OrderID,SKU[/B]
214583631017,ts-001
214583631017,ts-002
Would you have any idea where to start?! (I'll have a go, but there's a whole load of new concepts for me to munch on here & it's likely to take me a while!)
 
Last edited:
OK.

I added a Tempvar, TempOrder into the DisplayNode routine. This will give the following output.

Code: 
[COLOR="Blue"]214583631017,ts-001
214583631017,ts-002[/COLOR]

Here is the revised code.
Code: 
'---------------------------------------------------------------------------------------
' Procedure : DisplayNode
' Author    : mellon
' Date      : 10-Oct-2016
' Purpose   : modified this code to find the Order and SKU information in the
' response information from ebay (my  ebay.xml)
'
'Added a TempVar to hold the OrderID
'---------------------------------------------------------------------------------------
'
Public Sub DisplayNode(ByRef Nodes As MSXML2.IXMLDOMNodeList, _
                       ByVal Indent As Integer, xdoc As DOMDocument60)

    Dim xNode As IXMLDOMNode
    Dim oNode As IXMLDOMNode
    Dim xattr As IXMLDOMAttribute
    Dim xattr1 As IXMLDOMAttribute
    Dim xElem As IXMLDOMElement
    Dim strSQL As String
    Dim strSQL1 As String
    Dim tempOrder As TempVar              'tempvar
    Dim TempSKU As TempVar               'tempvar

10  Indent = Indent + 1

20  On Error Resume Next

30  For Each xNode In Nodes

40      If xNode.nodename = "OrderID" Then
50          'Debug.Print xNode.nodename & " " & xNode.text
            TempVars!tempOrder = xNode.text  'store the OrderId in the TempVar
60      End If
70      If xNode.nodename = "SKU" Then
80          'Debug.Print xNode.nodename & " " & xNode.text  '<---previous print
            Debug.Print TempVars!tempOrder & "," & xNode.text   '  print the Order and SKU
90      End If
100     If xNode.hasChildNodes Then
110         DisplayNode xNode.childNodes, Indent, xdoc

120     End If
130 Next xNode

End Sub
 
Perfect.

I'm not sure I understand what's going on, nor that this solution is better than using selectnodes (which is what I'm familiar with), but at least it allows me to crack on until I have a better understanding of parsing XML with VBA....thank you very much :-)
 
Happy to help.
If you find a good tutorial/article, or if you perfect your xpath/xml skills, please let us know. It ould make a great reference for us all.

Good luck with your project.
 
I'll come in late with the qualifier that I have limited experience with xml, but finding client increasing demand for importing/exporting data via xml.

I've found some files that look like being in xml format will not import because they are not fully compliant with the correct format.

You can test for a valid load with the following
Code: 
xDoc.validateOnParse = False
If xDoc.Load(FileName) = False Then
    If xDoc.parseError <> 0 Then
         Dim myError As Object
         Set myError = xDoc.parseError
         MsgBox "Failed: " & myError.reason
    End If
End If

Also, rather than looping through all nodes, you can loop through the root child nodes to find the one containing required data, set a node collection to that child node and then have an inner loop(s) to drill down. Something like

Code: 
For Each nCNode In xDoc.childNodes(1).childNodes
     If nCNode.baseName <> "whatever" Then      
         For Each nNode In nCNode.childNodes

Then again, if the xml file is not too large or if there are not to many xml files to process in the batch, it may not be worth the coding/testing time.
 
You must log in or register to reply here.

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom