Convert XML to rows and columns in pl/sql

 with t as

(

select

'<responsibility>

<responsibility_data><responsibility_name>@Advanced Supply Chain Planner</responsibility_name><end_date>2008-07-11</end_date></responsibility_data>

<responsibility_data><responsibility_name>Advanced Planning Scenario Manager</responsibility_name><end_date>2017-02-27</end_date></responsibility_data>

</responsibility>' myxml from dual

)

SELECT x.*

FROM t,

XMLTABLE ('/responsibility/responsibility_data'

PASSING XMLPARSE (DOCUMENT t.myxml)

COLUMNS "responsibility_name" VARCHAR2 (2000) PATH 'responsibility_name',

"end_date" DATE PATH 'end_date') x;