Using GROUP BY with an XMLCast and XMLQuery gives ORA-22950

The following is an example of what I am trying to do:

WITH xdata AS
         (SELECT 1 AS a_id,
                 xmltype ('<a>
                    <b>
                        <b_id>1</b_id>
                        <val>2</val>
                    </b>
                    <b>
                        <b_id>1</b_id>
                        <val>3</val>
                    </b>
                 </a>') AS xcol
            FROM DUAL
          UNION ALL
          SELECT 2 AS a_id,
                 xmltype ('<a>
                    <b>
                        <b_id>3</b_id>
                        <val>5</val>
                    </b>
                    <b>
                        <b_id>4</b_id>
                        <val>4</val>
                    </b>
                 </a>') AS xcol
            FROM DUAL)
  SELECT a_id,
         XMLCAST (
             XMLQUERY ('sum($doc/a/b/val/text())'
                       PASSING xcol AS "doc" RETURNING CONTENT) AS INTEGER)
             b_val
    FROM xdata
GROUP BY a_id, xcol;

When I run the above query, I get the error:

ORA-22950: cannot ORDER objects without MAP or ORDER method

If I remove the GROUP BY clause the query works fine.

The real world code that I'm working on requires me to use the GROUP BY clause. I went to every link in this Google search results page but couldn't find any helpful information in the XML context. Please help me understand why this error appears in XMLCast and XMLQuery also.

Thanks in advance!

In order to aggregate via a value, the value must be sortable/hashable by, meaning that any two values have to be comparable (in order to be sorted/hashed). XMLType is not sortable/hashable. An object/class in Oracle is hashable/sortable when it has one of the special functions map or order defined. See the respective Oracle documentation.

You are aggregating via xcol, which is an XMLType value. So, a resolution of your issue would be to aggregate via something else.

Based on the chaotic info you are giving us, two solutions come to my mind ...

Solution 1

WITH xdata AS
     (SELECT 1 AS a_id,
             xmltype ('<a>
                <b>
                    <b_id>1</b_id>
                    <val>2</val>
                </b>
                <b>
                    <b_id>1</b_id>
                    <val>3</val>
                </b>
             </a>') AS xcol
        FROM DUAL
      UNION ALL
      SELECT 2 AS a_id,
             xmltype ('<a>
                <b>
                    <b_id>3</b_id>
                    <val>5</val>
                </b>
                <b>
                    <b_id>4</b_id>
                    <val>4</val>
                </b>
             </a>') AS xcol
        FROM DUAL)
SELECT a_id,
     sum(XMLCAST (
         XMLQUERY ('sum($doc/a/b/val)'
                   PASSING xcol AS "doc" RETURNING CONTENT) AS INTEGER))
         b_val
FROM xdata
group by a_id;

Solution 2

WITH xdata AS
     (SELECT 1 AS a_id,
             xmltype ('<a>
                <b>
                    <b_id>1</b_id>
                    <val>2</val>
                </b>
                <b>
                    <b_id>1</b_id>
                    <val>3</val>
                </b>
             </a>') AS xcol
        FROM DUAL
      UNION ALL
      SELECT 2 AS a_id,
             xmltype ('<a>
                <b>
                    <b_id>3</b_id>
                    <val>5</val>
                </b>
                <b>
                    <b_id>4</b_id>
                    <val>4</val>
                </b>
             </a>') AS xcol
        FROM DUAL)
select X.a_id, sum(Y.b_val) as b_val
from xdata X
    cross join xmltable(
        '/a/b'
        passing X.xcol
        columns
            b_val integer path 'val'
    ) Y
group by X.a_id;