DevOps, CI/CD and Automation

Hi

<?xml version='1.0' encoding='UTF-8'?>
<S:Envelope xmlns:S="http://schemas.xmlsoap.org/soap/envelope/">
<S:Body><ns2:xyzResponse xmlns:ns2="http://xyz.xyz">
<return>
    <resCode>0</resCode>
    <resDef>SUCCESS</resDef>
    <RecordResult>
        <no>1</no>
        <resCode>0</resCode>
        <resDef>SUCCESS</resDef>
        <refNo>191206011906</refNo>
        <no>2</no>
        <resCode>-1</resCode>
        <resDef>FAILURE</resDef>
        <refNo>191206011907</refNo>
    </RecordResult>
</return>
</ns2:xyzResponse>
</S:Body>
</S:Envelope>

I have above XML and I 've been desperately trying to have it in columns.

Not sure if I can create something like LOOP for xml paths.. But I want two different output:

1-Server Response

and

2-Process Result

xmltable('//return'
        passing xmltype(x.service_response)
        columns
            rescode varchar2(5) path '//resCode',
            resDef varchar2(2000) path '//resDef'

won't work. however, if I use resCode[1] it works. But only for the one line..

I think it 's not possible to bind a variable to this node (or elements?) in plsql. I 've also tried to pass variables but couldn't achived any thing useful yet.

Could you help me creating these queries?

Thank you very much in advance