Oracle PL/SQL/XML/xmlattributes

Create xml attribute with xmlattribute function

 

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>
SQL> select xmlelement( "Product",xmlattributes(p.product_id,p.name as "Name"),xmlforest(p.category as "Category",p.price as "price"))
  2  from products p;
no rows selected
SQL>
SQL> drop table products;
Table dropped.
SQL>
SQL>

Must use "a single-group group function" with xmlattribute

 
SQL> CREATE TABLE countries (
  2    country_id         CHAR(2),
  3    country_name       VARCHAR2(40),
  4    country_subregion  VARCHAR2(30),
  5    country_region     VARCHAR2(20)
  6  );
Table created.
SQL>
SQL> select xmlelement( "Country",
  2         xmlattributes(c.country_region as "Region"),
  3         xmlagg(xmlelement("Country",xmlforest(c.country_name as "Name"))))
  4  from countries c
  5  /
       xmlattributes(c.country_region as "Region"),
                     *
ERROR at line 2:
ORA-00937: not a single-group group function

SQL>
SQL>
SQL> drop table countries;
Table dropped.
SQL>