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
union all
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>
         <JRec>
            <J_SVCDesc>MVR RECORD CLEAR</J_SVCDesc>
         </JRec>
      </HIJLoop>
      </MVRRecLoop>
</MVRCHPINFF_1.0>')
from dual;
2 rows affected

-- errors because of multiple JRec nodes
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;
ORA-19279: XPTY0004 - XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence

-- also errors because of multiple JRec nodes
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;
ORA-19279: XPTY0004 - XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence

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

MVR RECORD CLEAR|MVR RECORD CLEAR
ROMAN GENERAL|ROMAN ST|ROMAN CITY, ROME 111111
J_SVCDESC	XMLDETAILS
MVR RECORD CLEAR	ROMAN GENERAL\|ROMAN ST\|ROMAN CITY, ROME 111111
MVR RECORD CLEAR\|MVR RECORD CLEAR	ROMAN GENERAL\|ROMAN ST\|ROMAN CITY, ROME 111111