1

Yay. Accounting :|

I've got a set of accounting entries; they come in pairs -- 1 debit & 1 credit. The two entries share the same <SequenceID>. I want both entries if either of the entries references account 1111.

The (non-working) query I'm using (which I loosely based on [XPath. Select nodes based on an other, related node) is:

GLPostings/GLTransaction[GLPostings/GLTransaction[AccountCode = '1111']/SequenceID = SequenceID]

but I'm getting "empty sequence returned".

If I test part of the query: GLPostings/GLTransaction[AccountCode = '1111']/SequenceID I get multiple SequenceIDs as expected. So... how do I turn those multiple SequenceIDs into the set of nodes I'm after?

Here's some test data:

<?xml version="1.0" encoding="UTF-8"?>
<GLPostings>
   <GLTransaction RowNumber="1">
      <CRDR>Dr</CRDR>
      <SequenceID>616</SequenceID>
      <AccountCode>5531</AccountCode>
   </GLTransaction>
   <GLTransaction RowNumber="2">
      <CRDR>Cr</CRDR>
      <SequenceID>616</SequenceID>
      <AccountCode>2118</AccountCode>
   </GLTransaction>
   <GLTransaction RowNumber="3">
      <CRDR>Dr</CRDR>
      <SequenceID>617</SequenceID>
      <AccountCode>1111</AccountCode>
   </GLTransaction>
   <GLTransaction RowNumber="4">
      <CRDR>Cr</CRDR>
      <SequenceID>617</SequenceID>
      <AccountCode>1234</AccountCode>
   </GLTransaction>
   <GLTransaction RowNumber="5">
      <CRDR>Dr</CRDR>
      <SequenceID>618</SequenceID>
      <AccountCode>1231</AccountCode>
   </GLTransaction>
   <GLTransaction RowNumber="6">
      <CRDR>Cr</CRDR>
      <SequenceID>618</SequenceID>
      <AccountCode>1231</AccountCode>
   </GLTransaction>
   <GLTransaction RowNumber="7">
      <CRDR>Dr</CRDR>
      <SequenceID>619</SequenceID>
      <AccountCode>2341</AccountCode>
   </GLTransaction>
   <GLTransaction RowNumber="8">
      <CRDR>Cr</CRDR>
      <SequenceID>619</SequenceID>
      <AccountCode>1111</AccountCode>
   </GLTransaction>
</GLPostings>

What I'd like to get back is:

   <GLTransaction RowNumber="3">
      <CRDR>Dr</CRDR>
      <SequenceID>617</SequenceID>
      <AccountCode>1111</AccountCode>
   </GLTransaction>
   <GLTransaction RowNumber="4">
      <CRDR>Cr</CRDR>
      <SequenceID>617</SequenceID>
      <AccountCode>1234</AccountCode>
   </GLTransaction>
   <GLTransaction RowNumber="7">
      <CRDR>Dr</CRDR>
      <SequenceID>619</SequenceID>
      <AccountCode>2341</AccountCode>
   </GLTransaction>
   <GLTransaction RowNumber="8">
      <CRDR>Cr</CRDR>
      <SequenceID>619</SequenceID>
      <AccountCode>1111</AccountCode>
   </GLTransaction>

Any hints greatly appreciated.

EDIT: I can solve the problem so:

<xsl:for-each select="/GLPostings/GLTransaction[AccountCode = 1111']/SequenceID">
    <xsl:variable name="Seq" select="."/>
    <xsl:for-each select="/GLPostings/GLTransaction[SequenceID = $Seq]">
         <xsl:call-template name="output-row">
        </xsl:call-template>
    </xsl:for-each>          
</xsl:for-each>

But it seems kind of... dirty.

Peter Cant
  • 19
  • 4

2 Answers2

0

EDIT : Revisited XPath

//AccountCode[.="1111"]/parent::*|//AccountCode[following::AccountCode[1]="1111" and following::SequenceID[1]=preceding::SequenceID[1]]/parent::*|//AccountCode[preceding::AccountCode[1]="1111" and preceding::SequenceID[2]=preceding::SequenceID[1]]/parent::*

More secure option (in case you don't have two consecutive SequenceID) :

//AccountCode[.="1111"][following::SequenceID[1]=preceding::SequenceID[1]]/parent::*|//AccountCode[.="1111"][preceding::SequenceID[2]=preceding::SequenceID[1]]/parent::*|//AccountCode[following::AccountCode[1]="1111" and following::SequenceID[1]=preceding::SequenceID[1]]/parent::*|//AccountCode[preceding::AccountCode[1]="1111" and preceding::SequenceID[2]=preceding::SequenceID[1]]/parent::*
E.Wiest
  • 5,425
  • 2
  • 7
  • 12
  • Thanks very much for that. Works as advertised. Just for my own edification, I'd still be interested in where my original query cratered. – Peter Cant Mar 12 '20 at 03:19
  • "GLTransaction[GLPostings...." doesn't correspond to the XML structure (GLPostings is at the top of the tree. + "SequenceID = SequenceID]" : you are trying to compare a node with the same node (childs of same parent). – E.Wiest Mar 12 '20 at 03:57
  • Sorry, I was wrong it doesn't work as I need it to. The problem is that the suggestion relies on the the 1111 account node being before it's corresponding pair node. Which I'm I afraid I can't rely on. I've changed the Test data, to reflect this and added Credits and Debits in case that helps with the solution. – Peter Cant Mar 12 '20 at 04:22
  • OK. Post is edited with more options. Check Joel M. Lamsen's solution too. – E.Wiest Mar 12 '20 at 10:27
0
/GLPostings/GLTransaction[AccountCode=1111][SequenceID[.=following::SequenceID or .=preceding::SequenceID]]

will get GLTransaction nodes whose AccountCode child equals 1111 and whose SequenceID child is equal to preceding or following SequenceID nodes

/GLPostings/GLTransaction[SequenceID[.=following::SequenceID[./following-sibling::AccountCode=1111] or .=preceding::SequenceID[./following-sibling::AccountCode=1111]]]

will get GLTransaction nodes whose SequenceID child is equal to preceding or following SequenceID nodes that have an AccountCode following-sibling that is equals 1111

combine these xpaths into:

/GLPostings/GLTransaction[AccountCode=1111][SequenceID[.=following::SequenceID or .=preceding::SequenceID]]|/GLPostings/GLTransaction[SequenceID[.=following::SequenceID[./following-sibling::AccountCode=1111] or .=preceding::SequenceID[./following-sibling::AccountCode=1111]]]

will get you your 4 nodes (tested on xpathtester.com)

Joel M. Lamsen
  • 7,143
  • 1
  • 12
  • 14
  • Thanks Joel & E. Your solutions will work for my situation. ...but if I'm honest, I'm surprised that there isn't a more general solution for searching for a value in a node of the document, and then searching for values derived from the returned sequence anywhere in the document. Maybe it simply doesn't exist and I'm spoiled by SQL ;) (I'm feeling ungrateful here because you gave me what I need, but not what I *want*). – Peter Cant Mar 13 '20 at 03:24
  • can xquery use keys? if so it would be much simpler. – Joel M. Lamsen Mar 13 '20 at 05:19
  • see this link (https://stackoverflow.com/questions/13793199/xquery-function-for-xslt-key-function) – Joel M. Lamsen Mar 13 '20 at 05:21