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

-- original, plus address for info
SELECT x1.J_SVCDesc, x2.XMLDetails, x1.address from MVR_DTL M,  
XMLTABLE('/MVRCHPINFF_1.0/MVRRecLoop'
passing XMLTYPE(M.VENDOR_XML)
columns 
Address XMLTYPE path './CLoop/CRec/C_MVRNumberAddr',
J_SVCDesc varchar(50) PATH './HIJLoop/JRec/J_SVCDesc') x1
CROSS JOIN XMLTable(
  'string-join(., "|")'
  PASSING x1.Address
  COLUMNS XMLDetails varchar2(200) PATH '.') x2;


J_SVCDESC
XMLDETAILS
ADDRESS


MVR RECORD CLEAR
ROMAN GENERALROMAN STROMAN CITY, ROME 111111
<C_MVRNumberAddr>ROMAN GENERAL</C_MVRNumberAddr><C_MVRNumberAddr>ROMAN ST</C_MVRNumberAddr><C_MVRNumberAddr>ROMAN CITY, ROME 111111</C_MVRNumberAddr>


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


J_SVCDESC
XMLDETAILS


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