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

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>


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


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

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>

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