How do I insert data from a table to the XML table

Question

When I run this code I didn't get the right data from the standard table

create table COUNTRY_XML(
C_X sys.xmltype);

begin 
    for cursor in
       (select country, continental, population2019 from countries)
    loop
    insert into country_xml values(
    sys.xmltype.createXML(
    '<ac_x createdby="Guangzhe">
       <country_info>
       <Country>mycursor.country</Country>
       <Continental>mycursor.continental</Continental>
       <Population2019>mycursor.population2019</Population2019>
       </country_info>
    </ac_x>'));
    end loop;
end;

select c.c_x.extract('/').getstringval() from country_xml c

There results are as follow and they are all the same things for each row.

"<ac_x createdby="Guangzhe">
  <country_info>
    <Country>mycursor.country</Country>
    <Continental>mycursor.continental</Continental>
    <Population2019>mycursor.population2019</Population2019>
  </country_info>
</ac_x>"

Answer 1

You are not doing concatnation properly.

You need to use || (concatanation operator) as following

Table creation

SQL> CREATE TABLE COUNTRY_XML (
  2      C_X   SYS.XMLTYPE
  3  );

Table created.

Solution you need:

SQL>
SQL> begin
  2      for mycursor in -- changed the name
  3         (select 'INDIA' as country, 'ASIA' as continental, '130B' as population2019 from dual)
  4         --(select country, continental, population2019 from countries)
  5      loop
  6      insert into country_xml values(
  7      sys.xmltype.createXML(
  8      '<ac_x createdby="Guangzhe">
  9         <country_info>
 10         <Country>' || mycursor.country || '</Country>
 11         <Continental>' || mycursor.continental|| '</Continental>
 12         <Population2019>' || mycursor.population2019|| '</Population2019>
 13         </country_info>
 14      </ac_x>'));
 15      end loop;
 16  end;
 17  /

PL/SQL procedure successfully completed.

Output

SQL> SELECT C.C_X.EXTRACT('/').GETSTRINGVAL()
  2  FROM COUNTRY_XML C
  3  ;

C.C_X.EXTRACT('/').GETSTRINGVAL()
--------------------------------------------------------------------------------
<ac_x createdby="Guangzhe">
  <country_info>
    <Country>INDIA</Country>
    <Continental>ASIA</Continental>
    <Population2019>130B</Population2019>
  </country_info>
</ac_x>


SQL>

Cheers!!