Oracle PL/SQL/XML/xmlattributes

Create xml attribute with xmlattribute function

   <source lang="sql">

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>

 </source>
   
  

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

   <source lang="sql">

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>

 </source>