I have this SYS_REFCURSOS:
<?xml version="1.0"?>
<ROWSET>
<ROW>
<ITEM>
<SolicitudModificacionContrato><Id>2201</Id><Tipo><Id>6</Id><Nombre>Solicitud de Modificación de Contrato<
...
</ITEM>
</ROW>
</ROWSET>
How can i get de Id and store in a variable?
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>
<SolicitudModificacionContrato><Id>2201</Id><Tipo><Id>6</Id><Nombre>Solicitud de Modificación de Contrato</Nombre></Tipo></SolicitudModificacionContrato>
</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 |