Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table mvr_dtl (vendor_xml) as
select to_clob('<MVRCHPINFF_1.0>
   <Routing ReplyToQMgr="PQ21" ReplyToQ="A4218QA.BIZTALK.REPLY.REPORT.PROD" CorelId="712393102361590" MsgType="8" Expiry="-1" MsgID="201904051632015"></Routing>
   <MVRRecLoop>
      <CLoop>
         <CRec>
            <C_MVRNumberAddr>ROMAN GENERAL</C_MVRNumberAddr>
         </CRec>
         <CRec>
            <C_MVRNumberAddr>ROMAN ST</C_MVRNumberAddr>
         </CRec>
         <CRec>
            <C_MVRNumberAddr>ROMAN CITY, ROME 111111</C_MVRNumberAddr>
         </CRec>
      </CLoop>
      <HIJLoop>
         <JRec>
            <J_SVCDesc>MVR RECORD CLEAR</J_SVCDesc>
         </JRec>
      </HIJLoop>
      </MVRRecLoop>
</MVRCHPINFF_1.0>')
from dual;
1 rows affected

select x.j_svcdesc, x.xmldetails
from mvr_dtl m
cross join xmltable (
  '/MVRCHPINFF_1.0/MVRRecLoop'
  passing xmltype(m.vendor_xml)
  columns J_SVCDesc varchar2(50) path 'HIJLoop/JRec/J_SVCDesc',
    xmldetails varchar2(200) path 'string-join(CLoop/CRec/C_MVRNumberAddr, "|")'
) x;


J_SVCDESC
XMLDETAILS


MVR RECORD CLEAR
ROMAN GENERAL|ROMAN ST|ROMAN CITY, ROME 111111


select x.j_svcdesc, x.xmldetails
from mvr_dtl m
cross join xmltable (
  '/MVRCHPINFF_1.0/MVRRecLoop'
  passing xmltype(m.vendor_xml)
  columns J_SVCDesc varchar2(50) path 'string-join(HIJLoop/JRec/J_SVCDesc, "|")',
    xmldetails varchar2(200) path 'string-join(CLoop/CRec/C_MVRNumberAddr, "|")'
) x;


J_SVCDESC
XMLDETAILS


MVR RECORD CLEAR
ROMAN GENERAL|ROMAN ST|ROMAN CITY, ROME 111111