SQL> CREATE TABLE products(
2 product_id NUMBER(6),
3 name VARCHAR2(50),
4 category VARCHAR2(50),
5 price NUMBER(8,2),
6 min_price NUMBER(8,2)
7 );
Table created.
SQL> select xmlelement( "Prodcategory",
2 xmlattributes(p.category as "Category"),
3 xmlagg(xmlelement("Product",
4 xmlforest(p.name as "Name"))))
5 from products p
6 group by p.category;
no rows selected
SQL>
SQL> drop table products;
Table dropped.