2 Replies Latest reply: Jan 18, 2012 8:08 AM by 875664 RSS

    Aggregation of data in XMLtype table

    875664
      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
          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
            Thanks Odie!