cursor in query and generate hierarchical xml

cursor in query and generate hierarchical xml
================================================


CREATE TABLE table_1 (countryid VARCHAR2(10),country VARCHAR2(20));


INSERT INTO table_1
VALUES ('100', 'Bangladesh');

INSERT INTO table_1
VALUES ('200', 'India');

INSERT INTO table_1
VALUES ('300', 'Pakistan');

COMMIT ;


CREATE TABLE table_2 (cityid VARCHAR2(10),city VARCHAR2(20),countryid VARCHAR2(10));


INSERT INTO table_2
VALUES ('10', 'Dhaka', '100');

INSERT INTO table_2
VALUES ('20', 'Sylhet', '100');

INSERT INTO table_2
VALUES ('30', 'Rajshahi', '100');


INSERT INTO table_2
VALUES ('10', 'Kolkata', '200');

INSERT INTO table_2
VALUES ('20', 'Mumbai', '200');

INSERT INTO table_2
VALUES ('30', 'Rajastan', '200');


COMMIT ;

----Query

SELECT countryid, country,
CURSOR (SELECT cityid, city
FROM table_2 t1
WHERE t1.countryid = t2.countryid) AS cityname
FROM table_1 t2


-----generate an xml file with hierarchical

select DBMS_XMLquery.GetXML('SELECT COUNTRYID, COUNTRY,
CURSOR (SELECT CITYID, CITY
FROM TABLE_2 T1
WHERE T1.COUNTRYID = T2.COUNTRYID) AS CITYNAME
FROM TABLE_1 T2',2) dd from dual;