Database Software

Hi,

For performance reasons I'm using the XMLTable functionality on a OR table. Every root element of the XML document has an attribute with an unique ID. Now I want to do a sum or max of some recurring element in the XML document and return the aggregated value on a document basis, together with the document ID.

First the definition:

set long 10000000
set pages 5000
var schemaPath varchar2(256)
var schemaURL  varchar2(256)
var xmlSchema  clob;
--
begin
   :schemaURL := 'http://localhost/public/xsd/simple.xsd';
   :schemaPath := '/public/simple.xsd';
   :xmlSchema :=
'<?xml version="1.0" encoding="ISO-8859-1"?>
<xs:schema
   xmlns="http://localhost/public/xsd/simple.xsd"
   targetNamespace="http://localhost/public/xsd/simple.xsd"
   xmlns:xs="http://www.w3.org/2001/XMLSchema"
   xmlns:xdb="http://xmlns.oracle.com/xdb"
   elementFormDefault="qualified"
   attributeFormDefault="unqualified"
   xdb:storeVarrayAsTable="true">
  <xs:element name="root" xdb:defaultTable="simple_or" xdb:maintainDOM="false">
    <xs:complexType>
      <xs:sequence>
        <xs:element name="ea">
          <xs:complexType>
            <xs:sequence>
              <xs:element name="eb" minOccurs="1" maxOccurs="unbounded">
                <xs:complexType>
                  <xs:sequence>
                    <xs:element name="c1" type="xs:double" />
                    <xs:element name="c2" type="xs:double" />
                    <xs:element name="c3" type="xs:double" />
                  </xs:sequence>
                </xs:complexType>
              </xs:element>
            </xs:sequence>
           </xs:complexType>
        </xs:element>
      </xs:sequence>
      <xs:attribute name="id" type="xs:string" use="required" /> 
    </xs:complexType>
  </xs:element>
</xs:schema>';
end;
/ 

alter session set events='31098 trace name context forever'
/ 

DECLARE
  BINARY_XML boolean:=FALSE;
BEGIN
   IF (BINARY_XML)
   THEN
      dbms_xmlschema.registerSchema(SCHEMAURL => :schemaURL,
                                    SCHEMADOC => :xmlschema,
                                    LOCAL     => TRUE,
                                    GENTYPES  => FALSE,
                                    GENBEAN   => FALSE,
                                    GENTABLES => FALSE,
                                    ENABLEHIERARCHY => DBMS_XMLSCHEMA.ENABLE_HIERARCHY_NONE,
                                    FORCE     => FALSE,
                                    OPTIONS   => DBMS_XMLSCHEMA.REGISTER_BINARYXML,
                                    OWNER     => USER);
   ELSE
      dbms_xmlschema.registerSchema(SCHEMAURL => :schemaURL,
                                    SCHEMADOC => :xmlSchema,
                                    LOCAL     => TRUE,
                                    GENTYPES  => TRUE,
                                    GENBEAN   => FALSE,
                                    GENTABLES => TRUE,
                                    ENABLEHIERARCHY => DBMS_XMLSCHEMA.ENABLE_HIERARCHY_NONE,
                                    FORCE     => FALSE,
                                    OWNER     => USER);
   END IF;
END;
/ 

And the data. I've loaded 10 documents each having 50.000 eb elements:

<?xml version="1.0" encoding="UTF-8"?>
<root id="1" xmlns="http://localhost/public/xsd/simple.xsd">
<ea>
<eb>
<c1>4.0</c1>
<c2>5.0</c2>
<c3>6.0</c3>
</eb>
<eb>
<c1>7.0</c1>
<c2>8.0</c2>
<c3>9.0</c3>
</eb>
<eb>
<c1>7.0</c1>
<c2>8.0</c2>
<c3>9.0</c3>
</eb>
<eb>
<c1>7.0</c1>
<c2>8.0</c2>
<c3>9.0</c3>
</eb>
...etc...

Getting the global max of c2 is quite easy (thanks to Marco and Mark):

SELECT max(c2)
  FROM "simple_or",
       xmltable
       (
         xmlnamespaces
         (
            default 'http://localhost/public/xsd/simple.xsd'
         ),
         '/root/ea/eb'
         passing OBJECT_VALUE
         columns
         C2 PATH 'c2'
       )
/ 

However, in most cases I need to have the maximum per document. So, in I need to introduce a group by functionality. Amongst other solutions, I've tried the following:

select id, max(c2)
  from "simple_or",
       xmltable
       (
         xmlnamespaces
         (
            default 'http://localhost/public/xsd/simple.xsd'
         ),
         '/root/ea/eb'
         passing OBJECT_VALUE
         columns
         C2 PATH 'c2',
         ID PATH './../../@id'
       )
GROUP BY id
/ 

This however only gives me one result, which is actually the global maximum again. The reason is that the './../../@id' doesn't return the proper value, but an empty something.

Anybody who knows how to rewrite the query above to something that works (while keeping in mind the performance)?