oracle-database

How to extract a value from a SYS_REFCURSOR?


I have this SYS_REFCURSOS:

<?xml version="1.0"?>
<ROWSET>
  <ROW>
    <ITEM>
    &lt;SolicitudModificacionContrato&gt;&lt;Id&gt;2201&lt;/Id&gt;&lt;Tipo&gt;&lt;Id&gt;6&lt;/Id&gt;&lt;Nombre&gt;Solicitud de Modificación de Contrato&lt;
    ...
    </ITEM>

  </ROW>
</ROWSET>

How can i get de Id and store in a variable?


Solution

  • You don't appear to have a SYS_REFCURSOR, you have XML data and you can parse it using XMLTABLE to get the ROWSET/ROW/ITEM and then, if you have valid encoded XML data (you don't, but if you include the missing tags instead of ...), you can parse that as XML and extract the Id value:

    SELECT x.*
    FROM   XMLTABLE(
             '/ROWSET/ROW/ITEM'
             PASSING XMLTYPE('<?xml version="1.0"?>
    <ROWSET>
      <ROW>
        <ITEM>
        &lt;SolicitudModificacionContrato&gt;&lt;Id&gt;2201&lt;/Id&gt;&lt;Tipo&gt;&lt;Id&gt;6&lt;/Id&gt;&lt;Nombre&gt;Solicitud de Modificación de Contrato&lt;/Nombre&gt;&lt;/Tipo&gt;&lt;/SolicitudModificacionContrato&gt;
        </ITEM>
      </ROW>
    </ROWSET>')
             COLUMNS 
               xml CLOB PATH '.'
           ) xt
           CROSS JOIN XMLTABLE(
             '/SolicitudModificacionContrato'
             PASSING XMLTYPE(xt.xml)
             COLUMNS 
               id NUMBER PATH 'Id'
           ) x
    

    Which outputs:

    ID
    2201

    fiddle