Reading XML into Access - what the approach?

[peskywinnets]

I'm wanting to take baby steps pulling in some of my data from Amazon (the target being to just 'go & fetch' my product prices & pull them into an access table as a first step)

I've just had a dabble with their (Amazon) 'scratchpad' to pull in some data relating to some of my recent sales orders ( https://mws.amazonservices.co.uk/scratchpad/index.html) ...the end result from a 'query' using their scratchpad is some XML.

So I imported the XML into Access, but access has created several tables with nothing that can actually link ( 'join') the tables...they are all independent of one another!

I'm figuring this is because access has read the file in a way that wasn't intended

Anyway, I'm just wondering what the general approach to 'getting data' from an online marketplace is ....is the data normally downloaded in a file & then the file parsed....is it normally VBA that does the parsing (at least for getting the data contents into Access that is!).

Here's some sample data I retrieved (names/addresses/personal information all changed to be fictitious!)

<?xml version="1.0"?>
<ListOrdersResponse xmlns="https://mws.amazonservices.com/Orders/2013-09-01">
  <ListOrdersResult>
    <Orders>
      <Order>
        <LatestShipDate>2016-01-02T21:30:11Z</LatestShipDate>
        <OrderType>StandardOrder</OrderType>
        <PurchaseDate>2016-01-01T09:30:41Z</PurchaseDate>
        <BuyerEmail>YYYBBBBBBBBXX@marketplace.amazon.co.uk</BuyerEmail>
        <AmazonOrderId>202-8325195-9903548</AmazonOrderId>
        <LastUpdateDate>2016-01-02T21:31:15Z</LastUpdateDate>
        <ShipServiceLevel>Expedited</ShipServiceLevel>
        <NumberOfItemsShipped>1</NumberOfItemsShipped>
        <OrderStatus>Shipped</OrderStatus>
        <SalesChannel>Amazon.co.uk</SalesChannel>
        <NumberOfItemsUnshipped>0</NumberOfItemsUnshipped>
        <BuyerName>Ivor Biggun</BuyerName>
        <OrderTotal>
          <CurrencyCode>GBP</CurrencyCode>
          <Amount>15.99</Amount>
        </OrderTotal>
        <IsPremiumOrder>false</IsPremiumOrder>
        <EarliestShipDate>2016-01-02T21:30:11Z</EarliestShipDate>
        <MarketplaceId>A1F83G8C2ARO7P</MarketplaceId>
        <FulfillmentChannel>AFN</FulfillmentChannel>
        <PaymentMethod>Other</PaymentMethod>
        <ShippingAddress>
          <StateOrRegion>Northumberland</StateOrRegion>
          <City>Alnwick</City>
          <Phone>99999 9999999</Phone>
          <CountryCode>GB</CountryCode>
          <PostalCode>NE12 8GH</PostalCode>
          <Name>Les James</Name>
          <AddressLine1>9 Acacia Terrace</AddressLine1>
          <AddressLine2>Greenbanks</AddressLine2>
        </ShippingAddress>
        <IsPrime>false</IsPrime>
        <ShipmentServiceLevelCategory>Expedited</ShipmentServiceLevelCategory>
        <SellerOrderId>202-8325195-9943545</SellerOrderId>
      </Order>
    </Orders>
    <CreatedBefore>2016-01-20T14:58:50Z</CreatedBefore>
    <NextToken>pj45b4ETLIGaJqJYLDm0ZIfVkJJPpovR1tFFBYOLuVtUojd74H46trQzazHyYVyLqBXdLk4iogwgT7ZByNq5q7ke3OztunujhgC37Ju18/RFhykYz3HcDyJ0wMvlylZkWQWPqGlbsnPFsZtrK/Gz4LWvwdLkjfLaGQ7QTpHzGR1KeCGFWqH6NLuZIF9n45mtnrZ4AbBdBTeicp5jJPQPcgCy5/GuGI4OLzyB960RsbIZEWUDFvtT5/1Gvg0p9bwuOnmj31rkbVkL6wMhkP8877CXSaG7w7Y2T+A9FnRdLrFVIqDORMMh+vgSGNlBymhlgh51o+El5IcqVtOYt5i3YVBgjvy5fUeBQQUAf14MpG9ZIGdoV/L9zRWfvLiFuaTwCVT8UxY7ZsC/VnSd8D/qMiqqlcW5i699T6B1SgezW1etTshJEF7OfKWUaQKPaA2s0XG5Ei9f2Sc=</NextToken>
  </ListOrdersResult>
  <ResponseMetadata>
    <RequestId>9ccf3d1e-ba68-4536-b58d-74ed798c5c09</RequestId>
  </ResponseMetadata>
</ListOrdersResponse>

So all the info is contained between the <Order> & </Order> ...is it just a case of reading in each line between those two 'markers' ..... & then farming the data out to a field in a table, then moving on to the next order (which I've not shown in the above....but would be in another section top & tailed with <Order> & </Order> ???

 

[peskywinnets]

Nobody?

I can't be the first person to come to XML wondering how to 'process' it into Access

 

[jdraw]

You may get some ideas from these links I found via Google
http://analystcave.com/vba-xml-working-xml-files/

and/or
http://stackoverflow.com/questions/14401193/loop-through-a-full-xml-document