How to Insert data using cursor into new table having single column containing XML type data in Oracle?

Question

I'm able to insert values into table 2 from table 1 and execute the PL/SQL procedure successfully but somehow the output is clunky. I don't know why?

Below is the code :

 create table airports_2_xml
 (
    airport xmltype
 );

 declare
 cursor insert_xml_cr is select * from airports_1_orcl;

  begin
  for i in insert_xml_cr
  loop
      insert into airports_2_xml values
      (
           xmlelement("OneAirport",
           xmlelement("Rank", i.Rank) ||
           xmlelement("airport",i.airport) ||
           xmlelement("Location",i.Location) || 
           xmlelement("Country", i.Country) ||
           xmlelement("Code_iata",i.code_iata) || 
           xmlelement("Code_icao", i.code_icao) ||
           xmlelement("Total_Passenger",i.Total_Passenger) ||
           xmlelement("Rank_change", i.Rank_change) ||
           xmlelement("Percent_Change", i.Percent_change)
    ));
  end loop;
 end;
 /

select * from airports_2_xml;   

Output:

Why it is showing &lt ,&gt in the output ? And why am I unable to see the output fully?

Expected output:

      <OneAirport>
         <Rank>3</Rank>
         <Airport>Dubai International</Airport>
        <Location>Garhoud</Location>
        <Country>United Arab Emirates</Country>
        <Code_IATA>DXB</Code_IATA>
       <Code_ICAO>OMDB</Code_ICAO>
        <Total_passenger>88242099</Total_passenger>
       <Rank_change>0</Rank_change>
        <Percent_Change>5.5</Percent_Change>
      </OneAirport>

Answer 1

Try this below block :

declare
 cursor insert_xml_cr is select * from airports_1_orcl;
v_airport_xml SYS.XMLTYPE;
  begin
  for i in insert_xml_cr
  loop

  SELECT XMLELEMENT ( "OneAirport",
                             XMLFOREST(i.Rank as "Rank"
                                      ,i.airport as "Airport"
                                      ,i.Location as "Location"
                                      ,i.Country as "Country"
                                      ,i.code_iata as "Code_iata"
                                      ,i.code_icao as "code_icao"
                                      ,i.Total_Passenger as  "Total_Passenger" 
                                      , i.Rank_change as "Rank_change"
                                      ,i.Percent_change as "Percent_Change"
                                      ))
                                       into v_airport_xml
                                              FROM DUAL;

                        insert into airports_2_xml values (v_airport_xml);

  end loop;
 end;

Answer 2

The main issue is how you are constructnig the XML. You have an outer XMLElement for OneAirport, and the content of that element is a single string.

You are generating individual XMLElements from the cursor fields, but then you are concenating those together, which gives you a single string which still has the angle brackets you're expecting. So you're trying to do something like, simplified a bit:

select
xmlelement("OneAirport", '<Rank>1</Rank><airport>Hartsfield-Jackson</airport>')
from dual;

    XMLELEMENT("ONEAIRPORT",'<RANK>1</RANK><AIRPORT>HARTSFIELD-JACKSON</AIRPORT>')  
--------------------------------------------------------------------------------
<OneAirport>&lt;Rank&gt;1&lt;/Rank&gt;&lt;airport&gt;Hartsfield-Jackson&lt;/airp

and by default XMLElement() escapes entities in the passed-in values, so the angle-brackets are being converted to 'safe' equivalents like <. If it didn't do that, or you told it not to with noentityescaping:

select xmlelement(noentityescaping "OneAirport", '<Rank>1</Rank><airport>Hartsfield-Jackson</airport>')
from dual;

XMLELEMENT(NOENTITYESCAPING"ONEAIRPORT",'<RANK>1</RANK><AIRPORT>HARTSFIELD-JACKS
--------------------------------------------------------------------------------
<OneAirport><Rank>1</Rank><airport>Hartsfield-Jackson</airport></OneAirport>

then that would appear to be better, but you still actually have a single element with a single string (with characters that are likely to cause problems down the line), rather than the XML structure you almost certainly intended.

A simple way to get an zctual structure is with XMLForest():

xmlelement("OneAirport",
  xmlforest(i.Rank, i.airport, i.Location, i.Country, i.code_iata,
    i.code_icao, i.Total_Passenger, i.Rank_change, i.Percent_change)
)

You don't need the cursor loop, or any PL/SQL; you can just do:

insert into airports_2_xml (airport)
select xmlelement("OneAirport",
  xmlforest(i.Rank, i.airport, i.Location, i.Country, i.code_iata,
    i.code_icao, i.Total_Passenger, i.Rank_change, i.Percent_change)
  )
from airports_1_orcl i;

The secondary issue is the display. You'll see more data if you issue some formatting commands, such as:

set lines 120
set long 32767
set longchunk 32767

Those will tell your client to retrieve and show more of the long (XMLType here) data, rather the default 80 characters it's giving you now.

Once you are generating a nested XML structure you can use XMLSerialize() to display that more readable when you query your second table.