CODEWITHSUNDEEP
sqlxmloracle-databaseoracle11g
simplify_life
simplify_life

Reputation: 405

oracle XML to Database table

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

Upvotes: 1

Views: 96

Answers (1)

Gurwinder Singh
Gurwinder Singh

Reputation: 39457

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

Upvotes: 1

Related Questions