CREATE OR REPLACE TYPE div_obj AS OBJECT
("@DIV_ID"   NUMBER,
 div_desc    VARCHAR2(10)
);
/

CREATE OR REPLACE TYPE divlist_typ AS TABLE OF div_obj; 
/

CREATE OR REPLACE TYPE sales_obj AS OBJECT
("@YEAR"      NUMBER(4),
 div_list     divlist_typ,
 sales        NUMBER
);
/



SELECT
 XMLElement("Year",
   sales_obj(q_sales.c_year,
             CAST(MULTISET(
               SELECT
                 q_reg.region_id,
                 q_reg.region_desc
               FROM (SELECT  1  region_id,  'AUS'  region_desc FROM DUAL UNION 
                     SELECT  2  region_id,  'CAN'  region_desc FROM DUAL UNION
                     SELECT  3  region_id,  'CHN'  region_desc FROM DUAL UNION
                     SELECT  4  region_id,  'IND'  region_desc FROM DUAL UNION
                     SELECT  5  region_id,  'USA'  region_desc FROM DUAL
                    ) q_reg
               WHERE q_reg.region_id = q_sales.region_id)
              AS divlist_typ),
              q_sales.sales
             )
        )  AS sales_data_xml
FROM
 (SELECT  4 region_id, 2011 c_year, 17500 sales FROM DUAL UNION
  SELECT  5 region_id, 2011 c_year, 15500 sales FROM DUAL UNION
  SELECT  4 region_id, 2012 c_year, 12500 sales FROM DUAL UNION
  SELECT  5 region_id, 2012 c_year, 19500 sales FROM DUAL UNION
  SELECT  4 region_id, 2013 c_year, 13500 sales FROM DUAL
 ) q_sales
WHERE q_sales.region_id IN (4, 5);