Databases
StackOverflow2013
Postsdbo
POAggregate values from XMLTYPE in database
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POAggregate values from XMLTYPE in database
    text
    copied!<p>I have XML data stored in each row of field in XMLTYPE, the first row of the XML field should be:</p> <pre><code>&lt;cdata&gt; &lt;r&gt; &lt;year&gt;2009&lt;/year&gt; &lt;month&gt;Jan&lt;/month&gt; &lt;day&gt;1&lt;/day&gt; &lt;data&gt;1180&lt;/data&gt; &lt;/r&gt; &lt;/cdata&gt; </code></pre> <p>and the second row of the XML field should be:</p> <pre><code>&lt;cdata&gt; &lt;r&gt; &lt;year&gt;2009&lt;/year&gt; &lt;month&gt;Jan&lt;/month&gt; &lt;day&gt;2&lt;/day&gt; &lt;data&gt;1280&lt;/data&gt; &lt;/r&gt; &lt;/cdata&gt; </code></pre> <p>and the belowed codes are also modified into the upper example (each data stored in a row and embedded a 'cdata' root element.</p> <pre><code>&lt;r&gt; &lt;year&gt;2009&lt;/year&gt; &lt;month&gt;Jan&lt;/month&gt; &lt;day&gt;3&lt;/day&gt; &lt;data&gt;1380&lt;/data&gt; &lt;/r&gt; &lt;r&gt; &lt;year&gt;2009&lt;/year&gt; &lt;month&gt;Feb&lt;/month&gt; &lt;day&gt;1&lt;/day&gt; &lt;data&gt;2180&lt;/data&gt; &lt;/r&gt; &lt;r&gt; &lt;year&gt;2009&lt;/year&gt; &lt;month&gt;Feb&lt;/month&gt; &lt;day&gt;2&lt;/day&gt; &lt;data&gt;2280&lt;/data&gt; &lt;/r&gt; &lt;r&gt; &lt;year&gt;2009&lt;/year&gt; &lt;month&gt;Feb&lt;/month&gt; &lt;day&gt;3&lt;/day&gt; &lt;data&gt;2380&lt;/data&gt; &lt;/r&gt; &lt;r&gt; &lt;year&gt;2010&lt;/year&gt; &lt;month&gt;Jan&lt;/month&gt; &lt;day&gt;1&lt;/day&gt; &lt;data&gt;1181&lt;/data&gt; &lt;/r&gt; &lt;r&gt; &lt;year&gt;2010&lt;/year&gt; &lt;month&gt;Jan&lt;/month&gt; &lt;day&gt;2&lt;/day&gt; &lt;data&gt;1281&lt;/data&gt; &lt;/r&gt; &lt;r&gt; &lt;year&gt;2010&lt;/year&gt; &lt;month&gt;Jan&lt;/month&gt; &lt;day&gt;3&lt;/day&gt; &lt;data&gt;1381&lt;/data&gt; &lt;/r&gt; &lt;r&gt; &lt;year&gt;2010&lt;/year&gt; &lt;month&gt;Feb&lt;/month&gt; &lt;day&gt;1&lt;/day&gt; &lt;data&gt;2181&lt;/data&gt; &lt;/r&gt; &lt;r&gt; &lt;year&gt;2010&lt;/year&gt; &lt;month&gt;Feb&lt;/month&gt; &lt;day&gt;2&lt;/day&gt; &lt;data&gt;2281&lt;/data&gt; &lt;/r&gt; &lt;r&gt; &lt;year&gt;2010&lt;/year&gt; &lt;month&gt;Feb&lt;/month&gt; &lt;day&gt;3&lt;/day&gt; &lt;data&gt;2381&lt;/data&gt; &lt;/r&gt; &lt;/cdata&gt; </code></pre> <p>now, i use this sql:</p> <pre><code>SELECT X.* FROM xmltest, XMLTABLE ('$d/cdata/r' passing xmldoc as "d" COLUMNS year integer path 'year', month varchar(3) path 'month', day varchar(2) path 'day', data float path 'data' ) AS X </code></pre> <p>I can retrieve the value in record, and the question is how can I aggregate the total of 'data' element which is in 2009/Jan? and what is the code that I can use to aggregate the 'data' element in all of 2009?</p>
 

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