LINQ to XML Sum Child Nodes in VB.NET

2019-08-11 08:15发布

站内问答 / 前沿技术
1525 4 6

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>joesmith@hotmail.com</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>

4条回答
Luminary・发光体
2楼-- · 2019-08-11 08:31

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

查看更多
0人赞 添加讨论(0) 举报
迷人小祖宗
3楼-- · 2019-08-11 08:45

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.

查看更多
0人赞 添加讨论(0) 举报
戒情不戒烟
4楼-- · 2019-08-11 08: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.

查看更多
0人赞 添加讨论(0) 举报
Animai°情兽
5楼-- · 2019-08-11 08:47

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().

查看更多
0人赞 添加讨论(0) 举报
登录 后发表回答
相关问题
查看全部
相关文章
查看全部
收藏的人(6)