how to get data from different levels in a xmltable?

I’m trying to get the values of two attributes from table MVR_DTL in column VENDOR_XML. VENDOR_XML is of datatype clob and contains an xml that looks like this

<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>

I tried running

SELECT c.J_SVCDesc, c.XMLDetails from MVR_DTL M,
    XMLTABLE(
        'string-join(/MVRCHPINFF_1.0/MVRRecLoop/CLoop/CRec/C_MVRNumberAddr, "|")'
    passing XMLTYPE(M.VENDOR_XML)
    columns XMLDetails varchar2(200) PATH '.',
            J_SVCDesc varchar2(50) PATH './../../../HIJLoop/JRec/J_SVCDesc') c;

and i get this error

Error during Execute
 S1000(19112)[Oracle][ODBC][Ora]ORA-19112: error raised during evaluation: 
XVM-01020: [XPTY0020] The path step context item is not a node

I also tried

SELECT x1.J_SVCDesc, x2.XMLDetails 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;

but errored out with

Error during Execute
 S1000(19279)[Oracle][ODBC][Ora]ORA-19279: XPTY0004 - XQuery dynamic type mismatch: 
expected singleton sequence - got multi-item sequence

I’m trying to get

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

Could someone help me figure out what I’m missing.

Answer

You can move the string-join down to the columns clause:

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

Demo with your sample data in a CTE:

with 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
)
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                                                                                                                                                          

If the HIJLoop node name suggests there could be multiple JRec values too then you could concatenate those too:

  columns J_SVCDesc varchar2(50) path 'string-join(HIJLoop/JRec/J_SVCDesc, "|")',
    xmldetails varchar2(200) path 'string-join(CLoop/CRec/C_MVRNumberAddr, "|")'

which makes no difference to the output with the sample XML.

db<>fiddle

---

Incidentally, your second attempt sort of works; it doesn’t error but doesn’t get the right result either. The address you pass is an XML fragment with just sibling nodes, and the string-join only sees one value, consisting of the text() from those (I think… something like that). If you pass the CLoop down instead and expand the second XPath then it works:

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;

db<>fiddle

But if you were actually getting “ORA-19279: XPTY0004 – XQuery dynamic type mismatch: expected singleton sequence – got multi-item sequence” then I suspect you have data that does actually have multiple JRec nodes; in which case see my second query above.

db<>fiddle showing that issue with this approach and my first query; and it working with my second query. So you probably need to use that:

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;

You might need to increase the returned column sizes, too.