This discussion is archived
2 Replies Latest reply: Jan 18, 2012 6:08 AM by 875664 RSS

Aggregation of data in XMLtype table

875664 Newbie
Currently Being Moderated
I have a problem with aggregation of data in XMLtype table.

The following is a simplified version of dataset I'm working on:

<ACCOUNT number="5443">
<SERVICE number="4365465">
<MSISDN>48756849432</MSISDN>
</SERVICE>
</ACCOUNT>

<ACCOUNT number="6323">
<SERVICE number="546545">
<MSISDN>48756849432</MSISDN>
</SERVICE>
<SERVICE number="512343">
<MSISDN>48867368934</MSISDN>
</SERVICE>
<SERVICE number="95674">
<MSISDN>48590327346</MSISDN>
</SERVICE>
</ACCOUNT>

<ACCOUNT number="8789">
<SERVICE number="6753453">
<MSISDN>48189457438</MSISDN>
</SERVICE>
<SERVICE number="9832784">
<MSISDN>48423656576</MSISDN>
</SERVICE>
</ACCOUNT>

I create the table:

create table XMLCLOB of XMLType
XMLTYPE store as CLOB;

And insert the documents:

insert into XMLCLOB values(xmltype(
'<ACCOUNT number="5443">
<SERVICE number="4365465">
<MSISDN>48756849432</MSISDN>
</SERVICE>
</ACCOUNT>'));

...

So, each ACCOUNT node is inserted in a separate tuple of the table.

I want to count all MSISDN elements in the table, so I execute:

select XMLQuery(
'for $x in collection("oradb:/ANDRZEJ/XMLCLOB")
return count ($x//MSISDN)' returning content)
from dual;

where ANDRZEJ is the name of the schema.

What I obtain is:

XMLQUERY('FOR$XINCOLLECTION("ORADB:/ANDRZEJ/XMLCLOB")RETURNCOUNT($X//MSISDN)'RET

--------------------------------------------------------------------------------

1 3 2


What is my mistake?
  • 1. Re: Aggregation of data in XMLtype table
    odie_63 Guru
    Currently Being Moderated
    What is my mistake?
    You're iterating over the sequence returned by the collection function, so you're retrieving a separate count for each XML instance document stored in the table.

    Try one of these instead :
    SQL> SELECT XMLCast(
      2          XMLQuery(
      3           'count(collection("oradb:/DEV/TEMP_XML")/ACCOUNT/SERVICE/MSISDN)'
      4           returning content
      5          )
      6          as number
      7         ) as cnt
      8  FROM dual;
     
           CNT
    ----------
             6
     
    or, more straightforwardly :
    SQL> SELECT count(*) as cnt
      2  FROM temp_xml
      3     , XMLTable('/ACCOUNT/SERVICE/MSISDN' passing object_value)
      4  ;
     
           CNT
    ----------
             6
     
  • 2. Re: Aggregation of data in XMLtype table
    875664 Newbie
    Currently Being Moderated
    Thanks Odie!

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points