Databases
StackOverflow2013
Postsdbo
PO
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    text
    copied!<p>This can be achieved in XSLT1.0 by using a method called Muenchian Grouping.</p> <p>Firstly, you define a key to look-up the <strong>line</strong> elements for a given <strong>lines</strong> element using the <strong>line_id</strong> attribute as the key. So, for a given lines element and line_id attribute, all matching <strong>line</strong> elements will be returned. You will need a concatenated key for this.</p> <pre><code>&lt;xsl:key name="id" match="line" use="concat(concat(generate-id(..), ','), @line_id)"/&gt; </code></pre> <p>Next, you need to match the <strong>line</strong> elements which have the first occurrence of each distinct <strong>line_id</strong> attribute within the <strong>lines</strong> element. This is achieved as follows:</p> <pre><code>&lt;xsl:apply-templates select="line[ generate-id() = generate-id(key('id',concat(concat(generate-id(..),','),@line_id))[1])]" /&gt; </code></pre> <p>i.e. Match <strong>line</strong> elements which happen to be the first elements in the list for the given key.</p> <p>Then, you can get the total quantity, simply by summing all elements in the key</p> <pre><code>&lt;xsl:value-of select="sum(key('id', concat(concat(generate-id(..), ','), @line_id))/qty)"/&gt; </code></pre> <p>Here is the full XSLT document:</p> <pre><code>&lt;xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"&gt; &lt;xsl:output method="xml" indent="yes"/&gt; &lt;xsl:key name="id" match="line" use="concat(concat(generate-id(..), ','), @line_id)"/&gt; &lt;xsl:template match="/foo"&gt; &lt;newfoo&gt; &lt;xsl:apply-templates select="lines"/&gt; &lt;/newfoo&gt; &lt;/xsl:template&gt; &lt;xsl:template match="lines"&gt; &lt;items&gt; &lt;xsl:apply-templates select="line[generate-id() = generate-id(key('id', concat(concat(generate-id(..), ','), @line_id))[1])]"/&gt; &lt;/items&gt; &lt;/xsl:template&gt; &lt;xsl:template match="line"&gt; &lt;item&gt; &lt;item_id&gt; &lt;xsl:value-of select="@line_id"/&gt; &lt;/item_id&gt; &lt;quantity&gt; &lt;xsl:value-of select="sum(key('id', concat(concat(generate-id(..), ','), @line_id))/qty)"/&gt; &lt;/quantity&gt; &lt;/item&gt; &lt;/xsl:template&gt; &lt;/xsl:stylesheet&gt; </code></pre> <p>When applied to the following XML:</p> <pre><code>&lt;foo&gt; &lt;lines&gt; &lt;line line_id="10000"&gt; &lt;qty&gt;10&lt;/qty&gt; &lt;/line&gt; &lt;line line_id="10000"&gt; &lt;qty&gt;4&lt;/qty&gt; &lt;/line&gt; &lt;line line_id="10000"&gt; &lt;qty&gt;12&lt;/qty&gt; &lt;/line&gt; &lt;line line_id="20000"&gt; &lt;qty&gt;1&lt;/qty&gt; &lt;/line&gt; &lt;line line_id="30000"&gt; &lt;qty&gt;4&lt;/qty&gt; &lt;/line&gt; &lt;line line_id="30000"&gt; &lt;qty&gt;6&lt;/qty&gt; &lt;/line&gt; &lt;/lines&gt; &lt;lines&gt; &lt;line line_id="10000"&gt; &lt;qty&gt;4&lt;/qty&gt; &lt;/line&gt; &lt;/lines&gt; &lt;/foo&gt; </code></pre> <p>The following XML is output:</p> <pre><code>&lt;newfoo&gt; &lt;items&gt; &lt;item&gt; &lt;item_id&gt;10000&lt;/item_id&gt; &lt;quantity&gt;26&lt;/quantity&gt; &lt;/item&gt; &lt;item&gt; &lt;item_id&gt;20000&lt;/item_id&gt; &lt;quantity&gt;1&lt;/quantity&gt; &lt;/item&gt; &lt;item&gt; &lt;item_id&gt;30000&lt;/item_id&gt; &lt;quantity&gt;10&lt;/quantity&gt; &lt;/item&gt; &lt;/items&gt; &lt;items&gt; &lt;item&gt; &lt;item_id&gt;10000&lt;/item_id&gt; &lt;quantity&gt;4&lt;/quantity&gt; &lt;/item&gt; &lt;/items&gt; &lt;/newfoo&gt; </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload