Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE t(id INT, my_xml VARCHAR2(1000))
INSERT INTO t
VALUES (1,'
<root>
  <person>
    <name>Miguel Martins</name>
    <age>32</age>
    <list_of_numbers>
      <number>1</number>
      <number>2</number>
    </list_of_numbers>
  </person>

  <person>
    <name>Another Person</name>
    <age>19</age>
    <list_of_numbers>
      <number>3</number>
      <number>4</number>
    </list_of_numbers>
  </person>
</root>'
)
1 rows affected

INSERT INTO t
VALUES (2,'
<root>
  <person>
    <name>XXXX</name>
    <age>32</age>
    <list_of_numbers>
      <number>10</number>
      <number>20</number>
       <number>30</number>
    </list_of_numbers>
  </person>

</root>'
)
1 rows affected

INSERT INTO t
VALUES (3,'
<root>
  <person>
    <name>XXXX</name>
    <age>32</age>
  </person>
</root>'
)
1 rows affected

SELECT * FROM t


ID
MY_XML


1

<root>
  <person>
    <name>Miguel Martins</name>
    <age>32</age>
    <list_of_numbers>
      <number>1</number>
      <number>2</number>
    </list_of_numbers>
  </person>

  <person>
    <name>Another Person</name>
    <age>19</age>
    <list_of_numbers>
      <number>3</number>
      <number>4</number>
    </list_of_numbers>
  </person>
</root>

2

<root>
  <person>
    <name>XXXX</name>
    <age>32</age>
    <list_of_numbers>
      <number>10</number>
      <number>20</number>
       <number>30</number>
    </list_of_numbers>
  </person>

</root>

3

<root>
  <person>
    <name>XXXX</name>
    <age>32</age>
  </person>
</root>


select t.id, t1.Name, t1.Age, t2."number"
from t
CROSS JOIN xmltable('/root/person' passing xmltype(t.my_xml) columns name path 'name', age path 'age', list_of_numbers xmltype path 'list_of_numbers/number') t1
CROSS JOIN xmltable('/number' passing t1.list_of_numbers columns  "number" number path '.') t2


ID
NAME
AGE
number


1
Miguel Martins
32
1

1
Miguel Martins
32
2

1
Another Person
19
3

1
Another Person
19
4

2
XXXX
32
10

2
XXXX
32
20

2
XXXX
32
30


select t.id, t1.Name, t1.Age, t2."number"
from t
OUTER APPLY xmltable('/root/person' passing xmltype(t.my_xml) columns name path 'name', age path 'age', list_of_numbers xmltype path 'list_of_numbers/number') t1
OUTER APPLY xmltable('/number' passing t1.list_of_numbers columns  "number" number path '.') t2


ID
NAME
AGE
number


1
Miguel Martins
32
1

1
Miguel Martins
32
2

1
Another Person
19
3

1
Another Person
19
4

2
XXXX
32
10

2
XXXX
32
20

2
XXXX
32
30

3
XXXX
32
null


declare
  x VARCHAR2(2000);
begin
 dbms_utility.expand_sql_text(
        input_sql_text => q'{
          select t.id, t1.Name, t1.Age, t2."number"
from t
CROSS JOIN xmltable('/root/person' passing xmltype(t.my_xml) columns name path 'name', age path 'age', list_of_numbers xmltype path 'list_of_numbers/number') t1
CROSS JOIN xmltable('/number' passing t1.list_of_numbers columns  "number" number path '.') t2
}',
        output_sql_text => x);
        
  dbms_output.put_line(x);
end;
/
1 rows affected

dbms_output:
SELECT "A1"."ID_0" "ID","A1"."NAME_2" "NAME","A1"."AGE_3" "AGE","A1"."number_5" "number" FROM  (SELECT "A3"."ID_0" "ID_0","A3"."MY_XML_1" "MY_XML","A3"."NAME_2" "NAME_2","A3"."AGE_3" "AGE_3","A3"."LIST_OF_NUMBERS_4" "LIST_OF_NUMBERS","A2"."number_0" "number_5" FROM  (SELECT "A7"."ID" "ID_0","A7"."MY_XML" "MY_XML_1","A6"."NAME_0" "NAME_2","A6"."AGE_1" "AGE_3","A6"."LIST_OF_NUMBERS_2" "LIST_OF_NUMBERS_4" FROM "FIDDLE_MBIQITDWKVATIEJJAUXD"."T" "A7", LATERAL( (SELECT EXTRACTVALUE("A8"."COLUMN_VALUE",'/person/name') "NAME_0",EXTRACTVALUE("A8"."COLUMN_VALUE",'/person/age') "AGE_1",XMLQUERY('list_of_numbers/number' PASSING BY XMLTAB "A8"."COLUMN_VALUE" RETURNING CONTENT ) "LIST_OF_NUMBERS_2" FROM  (SELECT VALUE(A9) "COLUMN_VALUE" FROM TABLE("SYS"."XQSEQUENCE"(XMLQUERY('/root/person' PASSING BY VALUE "SYS"."XMLTYPE"("A7"."MY_XML") RETURNING SEQUENCE BY VALUE ))) "A9") "A8")) "A6") "A3", LATERAL( (SELECT CAST(SYS_XQ_UPKXML2SQL(SYS_XQEXVAL(XMLQUERY('.' PASSING BY XMLTAB "A4"."COLUMN_VALUE" RETURNING SEQUENCE BY VALUE ),0,0,54525952,0),50,1,2) AS number ) "number_0" FROM  (SELECT VALUE(A5) "COLUMN_VALUE" FROM TABLE("SYS"."XQSEQUENCE"(XMLQUERY('/number' PASSING BY VALUE "A3"."LIST_OF_NUMBERS_4" RETURNING SEQUENCE BY VALUE ))) "A5") "A4")) "A2") "A1"

declare
  x VARCHAR2(2000);
begin
 dbms_utility.expand_sql_text(
        input_sql_text => q'{
          select t.id, t1.Name, t1.Age, t2."number"
from t
CROSS APPLY xmltable('/root/person' passing xmltype(t.my_xml) columns name path 'name', age path 'age', list_of_numbers xmltype path 'list_of_numbers/number') t1
CROSS APPLY xmltable('/number' passing t1.list_of_numbers columns  "number" number path '.') t2
}',
        output_sql_text => x);
        
  dbms_output.put_line(x);
end;
/
1 rows affected

dbms_output:
SELECT "A1"."ID_0" "ID","A1"."NAME_2" "NAME","A1"."AGE_3" "AGE","A1"."number_5" "number" FROM  (SELECT "A3"."ID_0" "ID_0","A3"."MY_XML_1" "MY_XML","A3"."NAME_2" "NAME_2","A3"."AGE_3" "AGE_3","A3"."LIST_OF_NUMBERS_4" "LIST_OF_NUMBERS","A2"."number_0" "number_5" FROM  (SELECT "A7"."ID" "ID_0","A7"."MY_XML" "MY_XML_1","A6"."NAME_0" "NAME_2","A6"."AGE_1" "AGE_3","A6"."LIST_OF_NUMBERS_2" "LIST_OF_NUMBERS_4" FROM "FIDDLE_MBIQITDWKVATIEJJAUXD"."T" "A7", LATERAL( (SELECT EXTRACTVALUE("A8"."COLUMN_VALUE",'/person/name') "NAME_0",EXTRACTVALUE("A8"."COLUMN_VALUE",'/person/age') "AGE_1",XMLQUERY('list_of_numbers/number' PASSING BY XMLTAB "A8"."COLUMN_VALUE" RETURNING CONTENT ) "LIST_OF_NUMBERS_2" FROM  (SELECT VALUE(A9) "COLUMN_VALUE" FROM TABLE("SYS"."XQSEQUENCE"(XMLQUERY('/root/person' PASSING BY VALUE "SYS"."XMLTYPE"("A7"."MY_XML") RETURNING SEQUENCE BY VALUE ))) "A9") "A8")) "A6") "A3", LATERAL( (SELECT CAST(SYS_XQ_UPKXML2SQL(SYS_XQEXVAL(XMLQUERY('.' PASSING BY XMLTAB "A4"."COLUMN_VALUE" RETURNING SEQUENCE BY VALUE ),0,0,54525952,0),50,1,2) AS number ) "number_0" FROM  (SELECT VALUE(A5) "COLUMN_VALUE" FROM TABLE("SYS"."XQSEQUENCE"(XMLQUERY('/number' PASSING BY VALUE "A3"."LIST_OF_NUMBERS_4" RETURNING SEQUENCE BY VALUE ))) "A5") "A4")) "A2") "A1"

ID	MY_XML
1	<root> <person> <name>Miguel Martins</name> <age>32</age> <list_of_numbers> <number>1</number> <number>2</number> </list_of_numbers> </person> <person> <name>Another Person</name> <age>19</age> <list_of_numbers> <number>3</number> <number>4</number> </list_of_numbers> </person> </root>
2	<root> <person> <name>XXXX</name> <age>32</age> <list_of_numbers> <number>10</number> <number>20</number> <number>30</number> </list_of_numbers> </person> </root>
3	<root> <person> <name>XXXX</name> <age>32</age> </person> </root>

ID	NAME	AGE	number
1	Miguel Martins	32	1
1	Miguel Martins	32	2
1	Another Person	19	3
1	Another Person	19	4
2	XXXX	32	10
2	XXXX	32	20
2	XXXX	32	30