The requested output contains values not in the XML (201, 202, 203)! So I'm unclear exactly what you want.
I'm sure you can get there by using some combination of array notation for document:
- document[1] => get the first document element under job
- document[*] => get all the document elements under job
e.g.:
select id, x.*
from xml_tab, xmltable (
'Jobsdata/jobList/jobData/job' passing xml_data
columns
fileID path 'document[1]/fileID',
directoryid path 'directoryid'
) x;
ID FILEID DIRECTORYID
---------- ------------------------------ --------------------
4 100 D100
4 200 D200
4 <null> D300
select id, x.*
from xml_tab, xmltable (
'Jobsdata/jobList/jobData/job' passing xml_data
columns
fileID path 'document[*]/fileID',
directoryid path 'directoryid'
) x;
ID FILEID DIRECTORYID
---------- ------------------------------ --------------------
4 1001000 D100
4 200 D200
4 <null> D300
select id, x.*
from xml_tab, xmltable (
'Jobsdata/jobList/jobData/job' passing xml_data
columns
fileID xmltype path 'document[*]/fileID',
directoryid path 'directoryid'
) x;
ID FILEID DIRECTORYID
---------- ------------------------------ --------------------
4 <fileID>100</fileID> D100
<fileID>1000</fileID>
4 <fileID>200</fileID> D200
4 <null> D300