oracle XML to Database table

Question

I have XML like this . I am unable to parse it into a table

  <REQMST>
  <ROW>
    <REQ_ID>668 </REQ_ID>    
      <RequestDetails>
      <REQ_DTL_ID>845</REQ_DTL_ID>
      <INTERFACE_REFNUM>1</INTERFACE_REFNUM>
      </RequestDetails>
      <RequestDetails>
      <REQ_DTL_ID>846</REQ_DTL_ID>
      <INTERFACE_REFNUM>2</INTERFACE_REFNUM>
       </RequestDetails>     
    </ROW>
</REQMST>

I am trying to parse it into table using

select *  
FROM XMLTABLE('/REQMST/ROW'  
         PASSING   
            xmltype('
           <REQMST>
  <ROW>
    <REQ_ID>668 </REQ_ID>    
      <RequestDetails>
      <REQ_DTL_ID>845</REQ_DTL_ID>
      <INTERFACE_REFNUM>1</INTERFACE_REFNUM>
      </RequestDetails>
      <RequestDetails>
      <REQ_DTL_ID>846</REQ_DTL_ID>
      <INTERFACE_REFNUM>2</INTERFACE_REFNUM>
       </RequestDetails>     
    </ROW>
</REQMST>')
         COLUMNS  
            --describe columns and path to them:  
            REQ_ID  varchar2(20)                 PATH './REQ_ID',
            REQ_DTL_ID  varchar2(20)           PATH './RequestDetails/REQ_DTL_ID',
            INTERFACE_REFNUM varchar2(20)      PATH './RequestDetails/INTERFACE_REFNUM'
     ) xmlt  
;

I am getting error ORA-19279: XPTY0004 - XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence

it works if there is only one RequestDetails

Gurwinder Singh · Accepted Answer · 2017-02-20 02:39:50Z

You need to go one more level down to get the details from RequestDetails tag using the same technique as parent.

Used column name xml to get the xml as xmltype column and then use xmltable on it to get the further details:

Try this:

select xmlt.req_id, x.REQ_DTL_ID, x.INTERFACE_REFNUM
FROM XMLTABLE('/REQMST/ROW'  
         PASSING   
            xmltype('
           <REQMST>
                <ROW>
                    <REQ_ID>668 </REQ_ID>    
                    <RequestDetails>
                        <REQ_DTL_ID>845</REQ_DTL_ID>
                        <INTERFACE_REFNUM>1</INTERFACE_REFNUM>
                    </RequestDetails>
                    <RequestDetails>
                        <REQ_DTL_ID>846</REQ_DTL_ID>
                        <INTERFACE_REFNUM>2</INTERFACE_REFNUM>
                    </RequestDetails>     
                </ROW>
            </REQMST>')
         COLUMNS  
            --describe columns and path to them:  
            REQ_ID  varchar2(20)                 PATH 'REQ_ID',
            xml  xmltype         PATH 'RequestDetails'
     ) xmlt,  xmltable('/RequestDetails'
        passing xmlt.xml
        columns
             REQ_DTL_ID  varchar2(20)           PATH 'REQ_DTL_ID',
            INTERFACE_REFNUM varchar2(20)      PATH 'INTERFACE_REFNUM'
     ) x;

Produces:

REQ_ID  REQ_DTL_ID  INTERFACE_REFNUM
668     845         1
668     846         2

Collectives™ on Stack Overflow

oracle XML to Database table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related