Amazon Marketplace XML parsing with LINQ

Question

I have a series of complicated XML files from Amazon showing order reports.

A XML snippet is as follow:

<Order>
  <AmazonOrderID>000-1111111-2222222</AmazonOrderID>
  <MerchantOrderID>111-3333333-4444444</MerchantOrderID>
  <PurchaseDate>2012-03-02T13:28:53+00:00</PurchaseDate>
  <LastUpdatedDate>2012-03-02T13:29:05+00:00</LastUpdatedDate>
  <OrderStatus>Pending</OrderStatus>
  <SalesChannel>Amazon.com</SalesChannel>
  <URL>http://www.amazon.com</URL>
  <FulfillmentData>
    <FulfillmentChannel>Amazon</FulfillmentChannel>
    <ShipServiceLevel>Standard</ShipServiceLevel>
    <Address>
      <City>Beverly Hills</City>
      <State>CA</State>
      <PostalCode>90210-1234</PostalCode>
      <Country>US</Country>
    </Address>
  </FulfillmentData>
  <OrderItem>
    <ASIN>AmazonASIN </ASIN>
    <SKU> Internal-SKU</SKU>
    <ItemStatus>Pending</ItemStatus>
    <ProductName> This is the name of the product </ProductName>
    <Quantity>1</Quantity>
    <ItemPrice>
      <Component>
        <Type>Principal</Type>
        <Amount currency="USD">19.99</Amount>
      </Component>
    </ItemPrice>
  </OrderItem>
</Order>

What I need to do with this file is extract various parts of the XML document and then do a number of things with the data.

The problem I am having comes with multiple order items.

The following code will correctly grab each node and put it into a list item, however I am unsure how to associate those multiple items with the same order number within C#.

C# snippet:

List<string> getNodes(string path, string nodeName) {

    List<string> nodes = new List<string>(); 

    XDocument xmlDoc = XDocument.Load(path); //Create the XML document type

    foreach (var el in xmlDoc.Descendants(nodeName)) {
            //for debugging
            //nodes.Add(el.Name + " " + el.Value);

            //for production
            nodes.Add(el.Value);
    }
   return nodes;
} //end getNodes

The method is called like:

List<string> skuNodes = xml.getNodes(@"AmazonSalesOrders.xml", "SKU");

where xml is the instantiated class.

To further explain the complication: If each node is put into its own list the length of the list will be constant providing only one item is ordered. Once multiple items are ordered the SKU, quantity, price etc lists will become longer and prevent an easy loop.

I am sure there is a LINQ to XML statement that can do what I need, but I am no where near experienced enough with C# to hack it out.

+++++++++++++++ EDIT +++++++++++++++++++

I'm trying some LINQ suggestions I have found around the web. The following looks promising but is returning exception:

base {System.SystemException} = {"Object reference not set to an instance of an object."}

code is:

var query = from xEle in xmlDoc.Descendants(node)
            where xEle.Element("AmazonOrderID").Value.ToString() == primaryKey
            select new {
                   tag = xEle.Name.LocalName,
                   value = xEle.Value
            };

I am unsure why this is occurring, the variables of node, and primary key are passed at runtime.

If I set breakpoints, I can see that primaryKey is being passed correctly, same with node; however when I get to:

Dictionary<string, string> ordersByID = new Dictionary<string, string>();

        foreach (var CurNode in query) {
            ordersByID.Add(CurNode.tag, CurNode.value);
        }

I get the null reference error as it parses CurNode.

Please tell us what amazon service you are consuming, and what's the URL you are hitting to get this XML? — Diego, Mar 02 '12 at 15:14
The URL and web service are not relevant to the question as the problem is not in getting the XML file itself, its parsing it to associate multiple order items with the same order ID. — Robert H, Mar 02 '12 at 15:20
Would the following statement work? `var query = from xEle in xmlDoc.Descendants(node) where xEle.Element(primaryKey).Value == primaryKey select new { tag = xEle.Name.LocalName, value = xEle.Value }; Dictionary ordersByID = new Dictionary(); foreach (var CurNode in query) { ordersByID.Add(CurNode.tag, CurNode.value); } return ordersByID;` — Robert H, Mar 02 '12 at 17:15
I am thinking if I can get the order information to be placed into a dictionary I can then take that dictionary and build it out, having a format like: [orderID,{sku,qty,amount ... }],[orderID,{sku,qty,amount ...}], .... — Robert H, Mar 02 '12 at 17:17
Please don't prefix your titles with "C#" and such. That's what the tags are for. — John Saunders, Mar 02 '12 at 19:24

Standage · Accepted Answer · 2012-03-05T16:43:57.237

2

You can achieve this by using linq as you thought, somthing like this should work, just add more elements if required for itemprice etc.. : (Where ns is the namespace)

xmlDoc = XDocument.Parse(sr.ReadToEnd());

XNamespace ns = "w3.org/2001/XMLSchema-instance";

var query = from order in xmlDoc.Descendants(ns + "Order")
            from orderItem in order.Elements(ns + "OrderItem")
            select new
            {        
                amazonOrdeID = order.Element(ns + "AmazonOrderID").Value,
                merchantOrderID = order.Element(ns + "MerchantOrderID ").Value,
                orderStatus = order.Element(ns + "OrderStatus ").Value,
                asin = orderItem.Element(ns + "ASIN").Value,
                quantity = orderItem.Element(ns + "quantity").Value
            };

using the above you shold be able to bring back all the information you need per amazon order in a singleline...

edited Mar 05 '12 at 16:43

answered Mar 02 '12 at 22:04

Standage

1,517
7
22
41

That's a big help, thanks a bunch Paul. Next question is regarding the namespace - I am currently returning no data with the query and suspect that the namespace is the issue. From the XML document the namespace is: xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="amzn-envelope.xsd">. I added the first namespace with no resolution, how would I add the correct namespace to this code? – Robert H Mar 05 '12 at 14:12
See my change above, the namespace would be "w3.org/2001/XMLSchema-instance", let me know if this solves it for you. I had a couple of typo's (captials) in my previous code as well which i've fixed. – Standage Mar 05 '12 at 16:45
I don't think my solution will work TBH looking at this and you need to reference the second part of the namespace.... – Standage Mar 05 '12 at 17:24
Thank you for the edits Paul - I wont really have a solid opportunity to test this until next week, but I'll follow up once I've had the chance. – Robert H Mar 06 '12 at 13:41

score 0 · Answer 2 · answered Mar 12 '12 at 15:40

0

We've since decided to use an alternate method, so I didn't get a chance to fully test the solution, I am marking as complete due to a change in direction.

answered Mar 12 '12 at 15:40

Robert H

11,520
18
68
110

Amazon Marketplace XML parsing with LINQ

2 Answers2

Linked

Related