Aggregate values from XMLTYPE in database

Question

I have XML data stored in each row of field in XMLTYPE, the first row of the XML field should be:

<cdata>
<r> <year>2009</year>
<month>Jan</month>
<day>1</day>
<data>1180</data>
</r>
</cdata>

and the second row of the XML field should be:

<cdata>
<r>
<year>2009</year>
<month>Jan</month>
<day>2</day>
<data>1280</data>
</r>
</cdata>

and the belowed codes are also modified into the upper example (each data stored in a row and embedded a 'cdata' root element.

<r>
<year>2009</year>
<month>Jan</month>
<day>3</day>
<data>1380</data>
</r>
<r>
<year>2009</year>
<month>Feb</month>
<day>1</day>
<data>2180</data>
</r>
<r>
<year>2009</year>
<month>Feb</month>
<day>2</day>
<data>2280</data>
</r>
<r>
<year>2009</year>
<month>Feb</month>
<day>3</day>
<data>2380</data>
</r>
<r>
<year>2010</year>
<month>Jan</month>
<day>1</day>
<data>1181</data>
</r>
<r>
<year>2010</year>
<month>Jan</month>
<day>2</day>
<data>1281</data>
</r>
<r>
<year>2010</year>
<month>Jan</month>
<day>3</day>
<data>1381</data>
</r>
<r>
<year>2010</year>
<month>Feb</month>
<day>1</day>
<data>2181</data>
</r>
<r>
<year>2010</year>
<month>Feb</month>
<day>2</day>
<data>2281</data>
</r>
<r>
<year>2010</year>
<month>Feb</month>
<day>3</day>
<data>2381</data>
</r>
</cdata>

now, i use this sql:

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

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?

score 2 · Answer 1 · answered Mar 12 '12 at 14:38

2

Once you've worked out how to query the XML, you can aggregate with standard SQL aggregate functions:

SELECT X.year, x.month, sum(data)
FROM (select xmltype(xmldoc) xmldoc from data), 
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
group by x.year, x.month

answered Mar 12 '12 at 14:38

Vincent Malgrat

66,725
9
119
171

sorry...something i got error with this sql: 00942. 00000 - "table or view does not exist" – user1264222 Mar 12 '12 at 15:04
oh, i am so sorry with my careless and poor english...i have now modified the new meaning of the question and please see again...thanks – user1264222 Mar 12 '12 at 15:39

Justin Cave · Answer 2 · 2012-03-12T15:14:27.087

Depending on exactly what output you can do something like this to get the total in January of 2009

SELECT SUM( x.data )
  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
 WHERE x.year = 2009
   AND x.month = 'Jan'

and this to get the total in all of 2009

SELECT SUM( x.data )
  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
 WHERE x.year = 2009

Using your sample data and assuming that the table is defined as

CREATE TABLE xmltest (
  xmldoc XMLTYPE 
);

that produces output like this

SQL> ed
Wrote file afiedt.buf

  1  SELECT SUM( x.data )
  2    FROM xmltest,
  3    XMLTABLE ('$d/cdata/r' passing xmldoc as "d"
  4     COLUMNS
  5    year integer path 'year',
  6    month varchar(3) path 'month',
  7    day varchar(2) path 'day',
  8    data float path 'data'
  9    ) AS X
 10*  WHERE x.year = 2009
SQL> /

SUM(X.DATA)
-----------
      10680

and

SQL> SELECT SUM( x.data )
  2    FROM xmltest,
  3    XMLTABLE ('$d/cdata/r' passing xmldoc as "d"
  4     COLUMNS
  5    year integer path 'year',
  6    month varchar(3) path 'month',
  7    day varchar(2) path 'day',
  8    data float path 'data'
  9    ) AS X
 10   WHERE x.year = 2009
 11     AND x.month = 'Jan';

SUM(X.DATA)
-----------
       3840

@user1264222 - The queries work for me. I posted the output on my system. Are you saying both of those queries return `NULL` for you? — Justin Cave, Mar 12 '12 at 15:16
oh, i am so sorry with my careless and poor english...i have now modified the new meaning of the question and please see again...thanks — user1264222, Mar 12 '12 at 15:38

Aggregate values from XMLTYPE in database

2 Answers2