Loop through related tables to create XML file

Question

I have three tables Country, Province, City and I need to turn them into an XML format. The following is roughly what I am looking for since I have sample code designed to work with this format.

<Countries>
    <Country>
        <CountryId>1</CountryId><CountryName>Canada</CountryName>
        <Provinces>
            <Province>
                <ProvinceId>1</ProvinceId><ProvinceName>Alberta</ProvinceName>
                <Cities>
                    <CityId>1</CityId><CityName>City 1</CityName>
                    <CityId>2</CityId><CityName>City 2</CityName>
                </Cities>
            </Province>
            <Province>
                <ProvinceId>2</ProvinceId><ProvinceName>Ontario</ProvinceName>
            </Province>
        </Provinces>
    </Country>
    <Country>
        <CountryId>2</CountryId><CountryName>United States of America</CountryName>
        <Provinces>
            <Province>
                <ProvinceId>1</ProvinceId><ProvinceName>Florida</ProvinceName>
            </Province>
        </Provinces>
    </Country>
</Countries>

Table Country

Country_Code
Country_Name

Table Province

Prov_Code
Prov_Name
Country_Code

Table City

City_Id
City_Name
Prov_Code
Country_Code

declare
  thiscountry country_table%rowtype;

  cursor mycountries is select * from country_table where rownum<50;
begin
  --alter buffer size for 10g+
  DBMS_OUTPUT.ENABLE (buffer_size => NULL);
  DBMS_OUTPUT.PUT_LINE ('<Countries>');
  open mycountries;
  loop
    fetch mycountries into thiscountry;
    exit WHEN mycountries%NOTFOUND;

    DBMS_OUTPUT.PUT_LINE ('<Country>');
      DBMS_OUTPUT.PUT_LINE ('<CountryId>' || thiscountry.country_code || '</CountryId>');
      DBMS_OUTPUT.PUT_LINE ('<CountryName>' || thiscountry.country_eng || '</CountryName>');
      DBMS_OUTPUT.PUT_LINE ('<Provinces>');
        declare
          thisprovince province_table%ROWTYPE;
          cursor myprovinces is select * from province_table where country_code=thiscountry.country_code and rownum<5;
        begin
          open myprovinces;
          loop
            fetch myprovinces into thisprovince;
            exit when myprovinces%NOTFOUND;
            DBMS_OUTPUT.PUT_LINE ('<Provience>');
            DBMS_OUTPUT.PUT_LINE ('<ProvienceId>' || thisprovince.prov_code || '</ProvienceId>');
            DBMS_OUTPUT.PUT_LINE ('<ProvienceName>' || thisprovince.prov_eng || '</ProvienceName>');
            DBMS_OUTPUT.PUT_LINE ('<Cities>');  
            declare
              thiscity city_table%ROWTYPE;
              cursor mycities is select * from city_table where city_table.prov_code=thisprovince.prov_code and rownum<5;
            begin
              open mycities;
              loop
                fetch mycities into thiscity;
                DBMS_OUTPUT.PUT_LINE ('<City>');
                DBMS_OUTPUT.PUT_LINE ('<CityID>' || thiscity.city_id || '</CityID>');
                DBMS_OUTPUT.PUT_LINE ('<CityName>' || thiscity.city_en || '</CityName>');
                DBMS_OUTPUT.PUT_LINE ('</City>');
              end loop;
              close mycities;
            end;
            DBMS_OUTPUT.PUT_LINE ('</Cities>');
            DBMS_OUTPUT.PUT_LINE ('<Provience>');
          end loop;
          close myprovinces;
        end;
    DBMS_OUTPUT.PUT_LINE ('</Provinces>');  
    DBMS_OUTPUT.PUT_LINE ('</Country>');
  end loop;
  close mycountries;
  DBMS_OUTPUT.PUT_LINE ('</Countries>');
end;

When I run the above, I have both performance issues, and it does not work because it runs out of memory ORA-04030: out of process memory when trying to allocate.

I went a created a view which would have all the information needed. But I come to the issue of how to cycle through the records while displaying the correct output.

Answer 1

There are several ways to generate such a XML.

One approach is to work with Object TYPES:

CREATE OR REPLACE TYPE "City" AS OBJECT (
 "CityId" NUMBER,
 "CityName" VARCHAR2(100));

CREATE OR REPLACE TYPE "Cities_T" IS TABLE OF "City";

CREATE OR REPLACE TYPE "Province" AS OBJECT (
    "ProvinceId" NUMBER,
    "ProvinceName" VARCHAR2(100),
    "Cities" "Cities_T"
);

CREATE OR REPLACE TYPE "Provinces_T" IS TABLE OF "Province";

CREATE OR REPLACE TYPE "Provinces" AS OBJECT (
    "Provinces" "Provinces_T"
);


SELECT
    XMLTYPE(
        "Province"(
            Prov_Code, 
            Prov_Name, 
            CAST(MULTISET(SELECT city_id, city_name FROM CITY c WHERE c.Prov_Code = p.Prov_Code ORDER BY city_id) AS "Cities_T") 
        ) 
    )
FROM PROVINCE p

Or you create the XML Elements more manually like this:

SELECT 
    XMLELEMENT("Province", 
        XMLELEMENT("ProvinceId", Prov_Code),
        XMLELEMENT("ProvinceName", Prov_Name),
        XMLELEMENT("Cities", (
            SELECT XMLAGG(
                XMLELEMENT("City", 
                    XMLELEMENT("CityId", city_id), 
                    XMLELEMENT("CityName", city_name)
                ) ORDER BY city_id) 
            FROM CITY c 
            WHERE c.Prov_Code = p.Prov_Code)
        )
    )
FROM PROVINCE p

I would recommend to create some views or CTE tables (using WITH city AS (SELECT ...), then the final statement is less complex, e.g.

CREATE OR REPLACE VIEW XV_CITY AS
SELECT Prov_Code,
    XMLELEMENT("Cities", 
        XMLAGG(
        XMLELEMENT("City", 
            XMLELEMENT("CityId", city_id), 
            XMLELEMENT("CityName", city_name)
        ) ORDER BY city_id)
    ) AS CITIES_XML
FROM CITY
GROUP BY Prov_Code;

SELECT 
    XMLELEMENT("Province", 
        XMLELEMENT("ProvinceId", Prov_Code),
        XMLELEMENT("ProvinceName", Prov_Name),
        (SELECT CITIES_XML FROM XV_CITY c WHERE c.Prov_Code = p.Prov_Code)      
    )
FROM PROVINCE p

Another approach is to use DBMS_XMLGEN, see also Generating XML Using DBMS_XMLGEN