LINQ to XML Sum Child Nodes in VB.NET

Asked 3/5, 2011 at 20:2 Answered 4/5, 2011 at 11:41

Solved vb.net xpath linq-to-xml amazon-mws

I have the following XML from Amazon's Marketplace API. I need to sum all the values of Item/ItemPrice/Component[type='Principal']/Amount for all Items to compute an order total. Is this possible to do using LINQ to XML in VB.NET?

<?xml version="1.0"?>
<AmazonEnvelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="amzn-envelope.xsd">
  <Header>
    <DocumentVersion>1.01</DocumentVersion>
    <MerchantIdentifier>My Store</MerchantIdentifier>
  </Header>
  <MessageType>OrderReport</MessageType>
  <Message>
    <MessageID>1</MessageID>
    <OrderReport>
      <AmazonOrderID>050-1234567-1234567</AmazonOrderID>
      <AmazonSessionID>902-1030835-1234567</AmazonSessionID>
      <OrderDate>2002-05-01T15:20:15-08:00</OrderDate>
      <OrderPostedDate>2002-05-01T15:21:49-08:00</OrderPostedDate>
      <BillingData>
        <BuyerEmailAddress>[email protected]</BuyerEmailAddress>
        <BuyerName>Joe Smith</BuyerName>
        <BuyerPhoneNumber>206-555-1234</BuyerPhoneNumber>
      </BillingData>
      <FulfillmentData>
        <FulfillmentMethod>Ship</FulfillmentMethod>
        <FulfillmentServiceLevel>Standard</FulfillmentServiceLevel>
        <Address>
          <Name>Joe Smith</Name>
          <AddressFieldOne>1234 Main St.</AddressFieldOne>
          <City>Seattle</City>
          <StateOrRegion>Washington</StateOrRegion>
          <PostalCode>98004</PostalCode>
          <CountryCode>US</CountryCode>
          <PhoneNumber>206-555-1234</PhoneNumber>
        </Address>
      </FulfillmentData>
      <Item>
        <AmazonOrderItemCode>12345678901234</AmazonOrderItemCode>
        <SKU>1234</SKU>
        <Title>Programming Perl, 3rd edition</Title>
        <Quantity>1</Quantity>
        <ProductTaxCode>1234</ProductTaxCode>
        <ItemPrice>
          <Component>
            <Type>Principal</Type>
            <Amount currency="USD">10.00</Amount>
          </Component>
          <Component>
            <Type>Shipping</Type>
            <Amount currency="USD">3.49</Amount>
          </Component>
          <Component>
            <Type>Tax</Type>
            <Amount currency="USD">1.29</Amount>
          </Component>
          <Component>
            <Type>ShippingTax</Type>
            <Amount currency="USD">0.24</Amount>
          </Component>
        </ItemPrice>
        <ItemFees>
          <Fee>
            <Type>Commission</Type>
            <Amount currency="USD">-0.75</Amount>
          </Fee>
        </ItemFees>
        <ItemTaxData>
          <TaxJurisdictions>
            <TaxLocationCode>12345678</TaxLocationCode>
            <City>Seattle</City>
            <County>King</County>
            <State>WA</State>
          </TaxJurisdictions>
          <TaxableAmounts>
            <District currency="USD">10.00</District>
            <City currency="USD">10.00</City>
            <County currency="USD">0.00</County>
            <State currency="USD">10.00</State>
          </TaxableAmounts>
          <NonTaxableAmounts>
            <District currency="USD">0.00</District>
            <City currency="USD">0.00</City>
            <County currency="USD">10.00</County>
            <State currency="USD">0.00</State>
          </NonTaxableAmounts>
          <ZeroRatedAmounts>
            <District currency="USD">0.00</District>
            <City currency="USD">0.00</City>
            <County currency="USD">0.00</County>
            <State currency="USD">0.00</State>
          </ZeroRatedAmounts>
          <TaxCollectedAmounts>
            <District currency="USD">0.23</District>
            <City currency="USD">0.53</City>
            <County currency="USD">0.00</County>
            <State currency="USD">0.53</State>
          </TaxCollectedAmounts>
          <TaxRates>
            <District>0.0230</District>
            <City>0.0530</City>
            <County>0.0230</County>
            <State>0.0530</State>
          </TaxRates>
        </ItemTaxData>
        <ShippingTaxData>
          <TaxJurisdictions>
            <TaxLocationCode>12345678</TaxLocationCode>
            <City>Seattle</City>
            <County>King</County>
            <State>WA</State>
          </TaxJurisdictions>
          <TaxableAmounts>
            <District currency="USD">3.49</District>
            <City currency="USD">3.49</City>
            <County currency="USD">0.00</County>
            <State currency="USD">3.49</State>
          </TaxableAmounts>
          <NonTaxableAmounts>
            <District currency="USD">0.00</District>
            <City currency="USD">0.00</City>
            <County currency="USD">3.49</County>
            <State currency="USD">0.00</State>
          </NonTaxableAmounts>
          <ZeroRatedAmounts>
            <District currency="USD">0.00</District>
            <City currency="USD">0.00</City>
            <County currency="USD">0.00</County>
            <State currency="USD">0.00</State>
          </ZeroRatedAmounts>
          <TaxCollectedAmounts>
            <District currency="USD">0.04</District>
            <City currency="USD">0.10</City>
            <County currency="USD">0.00</County>
            <State currency="USD">0.10</State>
          </TaxCollectedAmounts>
          <TaxRates>
            <District>0.0120</District>
            <City>0.0190</City>
            <County>0.0190</County>
            <State>0.0190</State>
          </TaxRates>
        </ShippingTaxData>
        <Promotion>
          <PromotionClaimCode>ABC123</PromotionClaimCode>
          <MerchantPromotionID>12345678</MerchantPromotionID>
          <Component>
            <Type>Principal</Type>
            <Amount currency="USD">-1.00</Amount>
          </Component>
        </Promotion>
      </Item>
      <Item>
        <AmazonOrderItemCode>12345678901235</AmazonOrderItemCode>
        <SKU>1234</SKU>
        <Title>Programming ASP.NET, 2nd edition</Title>
        <Quantity>1</Quantity>
        <ProductTaxCode>1234</ProductTaxCode>
        <ItemPrice>
          <Component>
            <Type>Principal</Type>
            <Amount currency="USD">12.00</Amount>
          </Component>
          <Component>
            <Type>Shipping</Type>
            <Amount currency="USD">3.49</Amount>
          </Component>
          <Component>
            <Type>Tax</Type>
            <Amount currency="USD">1.42</Amount>
          </Component>
          <Component>
            <Type>ShippingTax</Type>
            <Amount currency="USD">0.24</Amount>
          </Component>
        </ItemPrice>
        <ItemFees>
          <Fee>
            <Type>Commission</Type>
            <Amount currency="USD">-0.75</Amount>
          </Fee>
        </ItemFees>
        <ItemTaxData>
          <TaxJurisdictions>
            <TaxLocationCode>12345678</TaxLocationCode>
            <City>Seattle</City>
            <County>King</County>
            <State>WA</State>
          </TaxJurisdictions>
          <TaxableAmounts>
            <District currency="USD">10.00</District>
            <City currency="USD">10.00</City>
            <County currency="USD">0.00</County>
            <State currency="USD">10.00</State>
          </TaxableAmounts>
          <NonTaxableAmounts>
            <District currency="USD">0.00</District>
            <City currency="USD">0.00</City>
            <County currency="USD">10.00</County>
            <State currency="USD">0.00</State>
          </NonTaxableAmounts>
          <ZeroRatedAmounts>
            <District currency="USD">0.00</District>
            <City currency="USD">0.00</City>
            <County currency="USD">0.00</County>
            <State currency="USD">0.00</State>
          </ZeroRatedAmounts>
          <TaxCollectedAmounts>
            <District currency="USD">0.23</District>
            <City currency="USD">0.53</City>
            <County currency="USD">0.00</County>
            <State currency="USD">0.53</State>
          </TaxCollectedAmounts>
          <TaxRates>
            <District>0.0230</District>
            <City>0.0530</City>
            <County>0.0230</County>
            <State>0.0530</State>
          </TaxRates>
        </ItemTaxData>
        <ShippingTaxData>
          <TaxJurisdictions>
            <TaxLocationCode>12345678</TaxLocationCode>
            <City>Seattle</City>
            <County>King</County>
            <State>WA</State>
          </TaxJurisdictions>
          <TaxableAmounts>
            <District currency="USD">3.49</District>
            <City currency="USD">3.49</City>
            <County currency="USD">0.00</County>
            <State currency="USD">3.49</State>
          </TaxableAmounts>
          <NonTaxableAmounts>
            <District currency="USD">0.00</District>
            <City currency="USD">0.00</City>
            <County currency="USD">3.49</County>
            <State currency="USD">0.00</State>
          </NonTaxableAmounts>
          <ZeroRatedAmounts>
            <District currency="USD">0.00</District>
            <City currency="USD">0.00</City>
            <County currency="USD">0.00</County>
            <State currency="USD">0.00</State>
          </ZeroRatedAmounts>
          <TaxCollectedAmounts>
            <District currency="USD">0.04</District>
            <City currency="USD">0.10</City>
            <County currency="USD">0.00</County>
            <State currency="USD">0.10</State>
          </TaxCollectedAmounts>
          <TaxRates>
            <District>0.0120</District>
            <City>0.0190</City>
            <County>0.0190</County>
            <State>0.0190</State>
          </TaxRates>
        </ShippingTaxData>
        <Promotion>
          <PromotionClaimCode>ABC123</PromotionClaimCode>
          <MerchantPromotionID>12345678</MerchantPromotionID>
          <Component>
            <Type>Principal</Type>
            <Amount currency="USD">-1.00</Amount>
          </Component>
        </Promotion>
      </Item>
    </OrderReport>
  </Message>
</AmazonEnvelope>

Scoggins answered 3/5, 2011 at 20:2 Comment(1)

Check my answer for one liner XPath expression. – Sheff 3/5, 2011 at 22:44

This is how you'd solve this in VB using the more idomatic inline-XML with Linq syntax.

Dim result =
   From xcmp In azm...<Item>.<ItemPrice>.<Component>
   Where xcmp.<Type>.Value = "Principal"
   Select Convert.ToDecimal(xcmp.<Amount>.Value)

Console.WriteLine(result.Sum().ToString())

The triple dot syntax is for descendant searches. The single dot syntax moves to the child node. The azm variable is an XDocument holding your XML.

Torchbearer answered 4/5, 2011 at 11:41 Comment(1)

Best answer so far! I love the axis operators in VB10 – Scoggins 4/5, 2011 at 13:22

Here's the c# version that would work

XDocument d = XDocument.Load(xmlFileName);
var sum = d.Root.Descendants("Item").Where(i => i.Element("ItemPrice")
                                                 .Element("Component")
                                                 .Element("Type")
                                                 .Value == "Principal")
                                    .Sum(i => Convert.ToDouble(i.Element("ItemPrice")
                                                               .Element("Component")
                                                               .Element("Amount")
                                                               .Value));

See if you can use a converter such as http://converter.telerik.com/ to convert to vb.net to use it.

Here's what I got from automatic conversion.

Dim d As XDocument = XDocument.Load(xmlFileName)
Dim sum = d.Root.Descendants("Item").Where(Function(i) i.Element("ItemPrice").Element("Component").Element("Type").Value = "Principal").Sum(Function(i) Convert.ToDouble(i.Element("ItemPrice").Element("Component").Element("Amount").Value))

EDIT:

Switched from Convert.ToInt64() to Convert.ToDouble().

Donavon answered 3/5, 2011 at 20:19 Comment(4)

The questioner was asking for VB, and the syntax of this answer is poor compared to the more idomatic Linq given by hermiod. Congrats on getting the answer anyway. Speed over accuracy wins on this site I suppose, but as a VB dev I much prefer seeing VB questions here get real VB answers. – Torchbearer 3/5, 2011 at 21:19

@Torchbearer the thing with hermiod's answer is that he starts with Descendants("Component") which could be wrong as the OP has specifically mentioned a path to use. There could be a Component tag inside of a non "item" item tag which his answer would pick up. I was just trying to be specific to the question. – Donavon 3/5, 2011 at 21:23

I'm a c# developer and I'm not familiar with VB syntax but I know the concept is similar. I don't have to give an answer that the OP can copy and paste and expect it to work. The c# syntax has been formatted well to give the OP a hint and there are converters out there that do a decent job if you have trouble. – Donavon 3/5, 2011 at 21:26

I do a whole lot of C# myself and I don't expect anyone to give a copy-and-paste answer. But I see this as a teaching site, and Linq to XML is one of those places where C# and VB.NET were not created equal and a native VB.NET speaker would better serve the instructive needs of answering the question. That having been said, I certainly respect your rep and your working solution... just asking for a little care towards being sure that this stays an instructive site, because a quick answer helps the asker in the short term, but the google hits last forever. – Torchbearer 4/5, 2011 at 11:49

Try this code

    Dim foo As List(Of Decimal) = (From i In xmlstring.Descendants("Component")
                                   Where i.Parent.Name = "ItemPrice"
                                   Where i.Element("Type") = "Principal"
                                   Select Convert.ToDecimal(i.Element("Amount").Value)).ToList()

    Dim result = foo.Sum()

xmlstring variable is the xdocument.

Aubreyaubrie answered 3/5, 2011 at 20:41 Comment(2)

This produces a sum of 20D, not 22D. – Scoggins 3/5, 2011 at 20:52

Have fixed it now, didn't see the additional nodes under <Promotion> which matched my where condition. – Aubreyaubrie 3/5, 2011 at 21:46

This is how you'd solve this in VB using the more idomatic inline-XML with Linq syntax.

Dim result =
   From xcmp In azm...<Item>.<ItemPrice>.<Component>
   Where xcmp.<Type>.Value = "Principal"
   Select Convert.ToDecimal(xcmp.<Amount>.Value)

Console.WriteLine(result.Sum().ToString())

The triple dot syntax is for descendant searches. The single dot syntax moves to the child node. The azm variable is an XDocument holding your XML.

Torchbearer answered 4/5, 2011 at 11:41 Comment(1)

Best answer so far! I love the axis operators in VB10 – Scoggins 4/5, 2011 at 13:22

I need to sum all the values of Item/ItemPrice/Component[type='Principal']/Amount for all Items to compute an order total.

This XPath expression:

sum(
  /AmazonEnvelope
    /Message
      /OrderReport
        /Item
          /ItemPrice
            /Component[Type='Principal']
              /Amount
)

Result: 22

Sheff answered 3/5, 2011 at 22:43 Comment(2)

@Mark for XPath, you can do (sorry, again c#)

XmlDocument doc = new XmlDocument();     doc.Load(xmlFileName);     XPathNavigator nav = doc.CreateNavigator();     XPathExpression expr = nav.Compile("sum(/AmazonEnvelope/Message/OrderReport/Item/ItemPrice/Component[Type='Principal']/Amount)");     double total = (double) nav.Evaluate(expr);

– Donavon 4/5, 2011 at 12:24

@Mark But I thought you specifically wanted a linq-to-xml solution. – Donavon 4/5, 2011 at 12:29

Recommended topics

Hot tags