CODEWITHSUNDEEP
xmloracle-databaseplsqlgroup-byxmltable
afeef
afeef

Reputation: 4716

how to use group by in xmltable oracle

When I use group by in XML table it's throwing error:

ORA-01422: exact fetch returns more than one requested number of rows

I tried LISTAGG() function as solution but it did not resolve the problem.

My SQL

   SELECT xmlelement("logs", xmlagg(xmlelement("log", xmlforest(v1.column_1 as "id", v1.column_2 as "name")))).getclobval()
   INTO   v_output 
   FROM   xmltable( '/logs/log' passing xmltype(in_xml) columns id number path 
  'id' ) x 
 JOIN   view v1 
 ON     v1.id= x.id;

Input xml

declare
  in_xml clob := '<?xml version="1.0" encoding="UTF-8"?>
    <logs>
        <log>
            <id>123456</id>
        </log>
        <log>
            <id>456898</id>
        </log>
    </logs>';
  v_output clob;

begin

   select xmlelement("logs", xmlagg(xmlelement("log", xmlforest(v1.column_1 as "id", v1.column_2 as "name")))).getclobval()
  into v_output
  from xmltable(
    '/logs/log'
    passing xmltype(in_xml)
    columns id number path 'id'
  ) x
  join view v1 on v1.id = x.id
 GROUP BY  v1.id;

  dbms_output.put_line (v_output);

end;

Any solution is most welcome.

Upvotes: 0

Views: 1044

Answers (2)

Satheeshkumar P
Satheeshkumar P

Reputation: 7

WITH XMLData AS (
    SELECT XMLType('<permission updatetype="User">
        <application code="Security">
            <entity code="Secgroups">
                <entry id="1814">
                    <user name="Dir">
                        <permission type="admin" grant="1"/>
                    </user>
                </entry>
                <entry id="8295">
                    <user name="Dir"/>
                </entry>
                <entry id="1234">
                    <user name="Dir">
                        <permission type="admin" grant="1"/>
                    </user>
                </entry>
            </entity>
        </application>
    </permission>') AS xml_data
    FROM dual
)
SELECT  
  ent.entry_id,
  ent.user_name,
  ent.permission_type,
 app.application_code,
  app.entity_code
FROM
    XMLData,
    XMLTable(
        '/permission/application'
        PASSING xml_data
        COLUMNS
            application_code VARCHAR2(20) PATH '@code',
            entity_code VARCHAR2(20) PATH 'entity/@code'
    ) app,
    XMLTable(   
        '/permission/application/entity/entry'
        PASSING xml_data
        COLUMNS
            entry_id INT PATH '@id',
            user_name VARCHAR2(20) PATH 'user/@name'
            permission_type VARCHAR2(20) PATH 'user/permission/@type'
    ) ent

Upvotes: 0

Sentinel
Sentinel

Reputation: 6449

Your first query is fine as originally written, however, in your second code block you've introduced the group by clause which may be making your query return multiple records, yet you are trying to select them into a single output variable.

You either need to limit your query to a single ID, or change v_output to a collection of clob values and use bulk operations:

declare
  in_xml clob := '<?xml version="1.0" encoding="UTF-8"?>
    <logs>
        <log>
            <id>123456</id>
        </log>
        <log>
            <id>456898</id>
        </log>
    </logs>';
  type t_clob is table of clob;
  v_output t_clob;

begin

   select xmlelement("logs", xmlagg(xmlelement("log", xmlforest(v1.column_1 as "id", v1.column_2 as "name")))).getclobval()
  bulk collect into v_output
  from xmltable(
    '/logs/log'
    passing xmltype(in_xml)
    columns id number path 'id'
  ) x
  join view v1 on v1.id = x.id
 GROUP BY  v1.id
 fetch first 10 rows only;

 for iter in v_output.first .. v_output.last loop
   dbms_output.put_line (v_output);
 end loop;   
end;

Some caution should be exercised with the above code as it can, without the fetch limit, potentially exhaust available memory if a large number of rows are selected into v_output. As currently written only the first 10 records will be retrieved and written out.

Upvotes: 1

Related Questions