with t as (select xmltype('<ROOT>
                           <Name>
                                <FirstName>FLOKI</FirstName>
                                <LastName/>
                           </Name>
                           <Name>
                                <FirstName>RAGNAR</FirstName>
                                <LastName>LOTHBROK</LastName>
                           </Name>
                           <Name>
                                <FirstName>SIGURDUR</FirstName>
                                <LastName>JOHANSSEN</LastName>
                           </Name>
                           </ROOT>
                           ') xml from dual)
select x.nameid
     , x.lastname
     , x.firstname
from t
     , xmltable(xmlnamespaces('urn:oecd:ties:stffatcatypes:v1' as "n2")
                , 'ROOT/Name'
                 passing t.xml
                 columns
                   nameid for ordinality
                 , lastname      varchar2(50)    path '//LastName'
                 , firstname     varchar2(50)    path '//FirstName'
                 ) x;