Solved

XPath expression listing orders and order totals

Posted on 2012-04-05
6
281 Views
Last Modified: 2012-04-05
Hello,

Attached is a small sample of an Amazon settlement file.

For each <Order> and <Adjustment> node, I need to list AmazonOrderID and the sum of any Amount fields inside, using xpath or similar.

I am using Oxygen XML, but I am open to other tools.

For example //AmazonOrderID gives me:
/SettlementReport[1]/Order[1]/AmazonOrderID[1] - 12342134214
/SettlementReport[1]/Order[2]/AmazonOrderID[1] - 001-2234436-7951404
/SettlementReport[1]/Order[3]/AmazonOrderID[1] - 011-1235465-2323809
/SettlementReport[1]/Adjustment[1]/AmazonOrderID[1] - 201-3232197-1108246
/SettlementReport[1]/Adjustment[2]/AmazonOrderID[1] - 221-6633333-3111456

sum(//Amount) gives me the sum for all orders:
155.21

What I need is something like this:
/SettlementReport[1]/Order[1]/AmazonOrderID (12342134214) / 42.22
/SettlementReport[1]/Order[2]/AmazonOrderID (001-2234436-7951404) / 32.21
/SettlementReport[1]/Order[3]/AmazonOrderID (011-1235465-2323809) / 44.87
/SettlementReport[1]/Adjustment[1]/AmazonOrderID (201-3232197-1108246) / -21.11
/SettlementReport[1]/Adjustment[2]/AmazonOrderID (221-6633333-3111456) / - 31.12

or simply the order id and Amount totals:
12342134214, 42.22
001-2234436-7951404, 32.21
and so forth...

What I am trying to do is to track down why a settlement does not balance with another source, so I need an efficient way to query the settlement XML file.

Suggestions of other efficient tools / methods for this are welcome.

Thanks.
test.xml
0
Comment
Question by:Lars007
  • 4
  • 2
6 Comments
 
LVL 60

Accepted Solution

by:
Geert Bormans earned 500 total points
ID: 37814488
I tend to treat analysis of (bigger) XML documents as a reporting transformation into html

Click the oxygen up right button containing teh word XSLT

open a new XSLT document

paste this code in the XSLT document

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    version="1.0">
    
    <xsl:template match="/">
        <html>
            <body>
                <table border="1">
                    <xsl:apply-templates select="//Order | // Adjustment"></xsl:apply-templates>
                </table>
            </body>
        </html>
    </xsl:template>
    
    <xsl:template match="Order | Adjustment">
        <tr>
            <td>
                <xsl:for-each select="ancestor-or-self::*">
                    <xsl:text>/</xsl:text>
                    <xsl:value-of select="name()"/>
                    <xsl:text>[</xsl:text>
                    <xsl:value-of select="count(preceding-sibling::*[name() = name(current())]) + 1"/>
                    <xsl:text>]</xsl:text>
                </xsl:for-each>
            </td>
            <td><xsl:value-of select="AmazonOrderID"/></td>
            <td><xsl:value-of select="format-number(sum(.//Amount), '#.00')"/></td>
        </tr>
    </xsl:template>
    
    
 </xsl:stylesheet>

Open in new window


Click the blue arrow (make sure this XSLT is active and your Amazon XML is as well

This is what you will get

/SettlementReport[1]/Order[1]
12342134214
58.37
/SettlementReport[1]/Order[2]
001-2234436-7951404
23.23
/SettlementReport[1]/Order[3]
011-1235465-2323809
109.48
/SettlementReport[1]/Adjustment[1]
201-3232197-1108246
-16.23
/SettlementReport[1]/Adjustment[2]
221-6633333-3111456
-19.64

but in a nice layout (if you activate th button with "XHTML"à
0
 
LVL 60

Expert Comment

by:Geert Bormans
ID: 37814493
Note that I have different totals than you have (likely because I should exclude some of the values... but than just change the XPath in the sum
0
 

Author Comment

by:Lars007
ID: 37814611
That's a great way to do it, thanks!  

I have not yet bought an XML / XSLT tool, but I quite like the Oxygen tool I am evaluating so far.  But they are not cheap, so I want to make sure I get the right tool for this kind of analysis.  Are there other tools that you recommend that I look into in addition to Oxygen?  It needs to have an XSLT debugger, XPath query tool, etc.
0
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
LVL 60

Expert Comment

by:Geert Bormans
ID: 37814616
For XPath and XSLT, Oxygen simply is teh best tool
You have Saxon bundled in, you have XPath 1 and 2 evaluation,
you can debug against a number of XSLT processors,
and you get all an XML developer need ... a whole bunch of utility tools,
great workbench it is (and I use a number of different tools for various reasons)
Note that Oxygen also has a splendid user support and forum
0
 

Author Closing Comment

by:Lars007
ID: 37814773
A+!
0
 
LVL 60

Expert Comment

by:Geert Bormans
ID: 37815102
cheers
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Problem How to write an Xquery that works like a SQL outer join, providing placeholders for absent data on the outer side?  I give a bit more background at the end. The situation expressed as relational data Let’s work through this.  I’ve …
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question