Creating & comparing node sets

skipw · February 20th, 2004, 11:46 AM

I'm trying to write XSLT code to accomplish the following:
1) I have "items sold" in a sales journal document
2) I have "items in inventory" in an inventory document
3) I need to create a node set containing items that match on item ID so that I can then "sum" on cost in order to get the "cost of items sold"

Can someone share some code to accomplish it ... or suggest a better way?

Thanks - Skip (University of Delaware)

Skip White
Professor of Accounting & MIS
Unviersity of Delaware

joefawcett · February 20th, 2004, 12:10 PM

Sounds fairly straightforward (famous last words). Could you provide a small sample of each document?

On a separate issue is the "Unviersity of Delaware" in your sig related in anyway to the "University of Delaware"?

Joe (MVP - xml)

skipw · February 20th, 2004, 05:49 PM

Joe - Thanks for the reply

My documents look like:
Sales journal:
<entryDetail>
   <account> xxx
   <amount> xxx
   <measurableId>rs200
   </entryDetail>
<entryDetail>
   etc.

Inventory document:
<item>
    <itemId> xxx
    <cost> xxx
    </item>
<item>
    <itemId>rs200
    <cost> 49.50
    </item>

So the node set for cost of goods sold will be a subset of the items in the the inventory doc but will match on itemId with the measurableId's in the sales journal doc.

Quote:

quote:Originally posted by joefawcett
Sounds fairly straightforward (famous last words). Could you provide a small sample of each document?

On a separate issue is the "Unviersity of Delaware" in your sig related in anyway to the "University of Delaware"?

Joe (MVP - xml)

Skip White
Professor of Accounting & MIS
Unviersity of Delaware

joefawcett · February 21st, 2004, 05:51 AM

Still not sure I've understood the entire problem but this might point you in the right direction, assuming your entryDetails are in xml like:

Code:

<entries>
  <entryDetail>
    <account>abc</account>
    <amount>xxx</amount>
    <measurableId>rs100</measurableId>
  </entryDetail>
  <entryDetail>
    <account>abc</account>
    <amount>xxx</amount>
    <measurableId>rs200</measurableId>
  </entryDetail>
  <entryDetail>
    <account>def</account>
    <amount>xxx</amount>
    <measurableId>rs300</measurableId>
  </entryDetail>
  <entryDetail>
    <account>def</account>
    <amount>xxx</amount>
    <measurableId>rs200</measurableId>
  </entryDetail>
  <entryDetail>
    <account>ghi</account>
    <amount>xxx</amount>
    <measurableId>rs100</measurableId>
  </entryDetail>
</entries>

then the following shows how to group on measurableId, search the Web for Muenchian grouping for an explanation of how this works:

Code:

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <xsl:key name="entries-by-id" match="entryDetail" use="measurableId" />

  <xsl:template match="/">
    <html><head><title>Costs</title></head>
    <body>
      <table width="60%" align="center" border="1">
        <caption>Entries Grouped By Id</caption>
        <tbody>
          <xsl:apply-templates/>
        </tbody>
      </table>
    </body>    
    </html>
  </xsl:template>

  <xsl:template match="entries">
    <xsl:for-each select="entryDetail[count(. | key('entries-by-id', measurableId)[1]) = 1]">
          <xsl:sort select="measurableId" />
          <tr><td><xsl:value-of select="measurableId" /></td><td>#xa0;</td></tr> 
          <xsl:for-each select="key('entries-by-id', measurableId)">
              <xsl:sort select="account" />
              <tr><td>#xa0;</td><td><xsl:value-of select="account" /></td></tr>
          </xsl:for-each>
      </xsl:for-each>  
  </xsl:template>
</xsl:stylesheet>

To match up with the other xml you would need to use the document function to bring the inventory document into play and lookup the cost using the measurableId.
If you need more show your hoped for output and input documents that are valid xml.

--

Joe