how to use xmltable to read values from a clob?

Question

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

<?xml version="1.0" encoding="UTF-8"?>
<MVRCHPINFF_1.0>
   <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>
 </MVRRecLoop>
</MVRCHPINFF_1.0>

I tried running

SELECT c.Address  
    from MVR_DTL M,  XMLTABLE('/MVRCHPINFF_1.0/MVRRecLoop/CLoop/CRec'
passing XMLTYPE(M.VENDOR_XML)
columns Address XMLTYPE PATH './C_MVRNumberAddr') c;

I'm expecting something like

ROMAN GENERAL ROMAN ST ROMAN CITY, ROME 111111

but i only get 'Statement has failed, however your database does not return any error information.'

Oracle version 12.2.0.1.0

Answer 1

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