sum values if same

[ayoungan]

I have XML documents that are returned from a Web Service, which are each put into an array.

sXml(i) = "<foobar><foo1></foo1><foo2></foo2></foobar>"

They all have the same elements. From my results, I need to sum <foo1> if <foo2> is the same and display in a datagrid.

Any suggestions?
ang

 

[ayoungan]

Before totaling:

<?xml version="1.0"?>
<pbXML>
<shipment>
<DelDate>11/2/2005 5:00:00 PM</DelDate>
<TotalFreight>10.00</TotalFreight>
<CarrierID>UPS</CarrierID>
<Service>GRD</Service>
<Tariff>UPSGD</Tariff>
<CarrierServiceName>UPS Ground</CarrierServiceName>
</shipment>
<shipment>
<DelDate>11/2/2005 5:00:00 PM</DelDate>
<TotalFreight>15.00</TotalFreight>
<CarrierID>UPS</CarrierID>
<Service>GRD</Service>
<Tariff>UPSGD</Tariff>
<CarrierServiceName>UPS Ground</CarrierServiceName>
</shipment>
<shipment>
<DelDate>11/2/2005 5:00:00 PM</DelDate>
<TotalFreight>22.22</TotalFreight>
<CarrierID>UPS</CarrierID>
<Service>2DAY</Service>
<Tariff>UPS2D</Tariff>
<CarrierServiceName>UPS 2nd Day</CarrierServiceName>
</shipment>
</pbXML>

After totaling:

<?xml version="1.0"?>
<pbXML>
<shipment>
<DelDate>11/2/2005 5:00:00 PM</DelDate>
<TotalFreight>25.00</TotalFreight>
<CarrierID>UPS</CarrierID>
<Service>GRD</Service>
<Tariff>UPSGD</Tariff>
<CarrierServiceName>UPS Ground</CarrierServiceName>
</shipment>
<shipment>
<DelDate>11/2/2005 5:00:00 PM</DelDate>
<TotalFreight>22.22</TotalFreight>
<CarrierID>UPS</CarrierID>
<Service>2DAY</Service>
<Tariff>UPS2D</Tariff>
<CarrierServiceName>UPS 2nd Day</CarrierServiceName>
</shipment>
</pbXML>

 

[JontyMC]

Try some like this:

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="[URL unfurl="true"]http://www.w3.org/1999/XSL/Transform">[/URL]
  <xsl:output method="xml" indent="yes"/>
  <xsl:template match="/">
    <pbXML>
      <xsl:apply-templates select="pbXML/shipment[not(Tariff = preceding-sibling::shipment/Tariff)]"/>
    </pbXML>
  </xsl:template>
  <xsl:template match="shipment">
    <shipment>
      <DelDate>
        <xsl:value-of select="DelDate"/>
      </DelDate>
      <TotalFreight>
        <xsl:value-of select="format-number(sum(../shipment[Tariff = current()/Tariff]/TotalFreight), '##.00')"/>
      </TotalFreight>
      <CarrierID>
        <xsl:value-of select="CarrierID"/>
      </CarrierID>
      <Service>
        <xsl:value-of select="Service"/>
      </Service>
      <Tariff>
        <xsl:value-of select="Tariff"/>
      </Tariff>
      <CarrierServiceName>
        <xsl:value-of select="CarrierServiceName"/>
      </CarrierServiceName>
    </shipment>
  </xsl:template>
</xsl:stylesheet>

Jon

"I don't regret this, but I both rue and lament it.

 

[ayoungan]

But how about if this needs to go into a VB.NET page that has other controls such as datalist, html tables, etc.? How does that work?

Thank you! That xsl will work if I can understand how to incorporate it into the rest of my page.

 

[JontyMC]

Something like this (not tested):

Dim sw As StringWriter = New StringWriter()

'Load xml file into XMLDoc
XMLDoc = New XPathDocument(Server.MapPath("pbXML.xml"))

'Load XSL
XSLTDoc = New XslTransform()
XSLTDoc.Load(Server.MapPath("pbXML.xsl"))

'Transform XMLDoc
XSLTDoc.Transform(XMLDoc, Nothing, sw, Nothing)

// Bind to dataset.
Dim ds As New DataSet("myXML")
ds.ReadXml(sw.ToString())
DataGrid1.DataSource = ds
DataGrid1.DataBind()
writer.Close()

Jon

"I don't regret this, but I both rue and lament it.

 

[ayoungan]

Only thing is that pbXML.xml is in an array.

sXml(0) = "<?xml version="1.0"?><pbXML><shipment>..."
sXml(1) = "<?xml version="1.0"?><pbXML><shipment>..."
sXml(2) = "<?xml version="1.0"?><pbXML><shipment>..."
sXml(3) = "<?xml version="1.0"?><pbXML><shipment>..."

 

[JontyMC]

1. Dim XMLDoc as XmlDocument = new XmlDocument()
XMLDoc.LoadXml("<pbXML/>")
2. loop through the array
3. convert each string to XML doc
4. grab shipment nodes and append to XMLDoc/pbXML/*
5. do for all in array
6. Apply transform to XMLDoc

Jon

"I don't regret this, but I both rue and lament it.