SQL & PL/SQL

Guys,

Currently, I am running 4 separate queries in order to retreive specific data from a XML file. Is there a way of extracting all 4 values via XML tags in a single query - e.g:


Message Reference UTL_RAW.CAST_TO_VARCHAR2 (dbms_lob.substr(message_content, 2000, 2303))
456123 >(Cancelled)</UploadError>
456123 >4561</UserId>
456123 >1234</SecurityIdentifier

At the moment, I am extracting the required info as follows...

Example query 1: message_content, 2000, 2303 > retrieves starting point for an error header


select ml.message_reference, UTL_RAW.CAST_TO_VARCHAR2 (dbms_lob.substr(message_content, 2000, 2303))
from table.msg_archive ma, table.msg_log ml
where ma.message_id = ml.message_id
and ml.message__cd = 'MP_XML'
and ml.message_reference in (456123)

Once retreived, I transfer to Excel and use a formula to extract the specific header (e.g. using =MID(B1,1,11))


Example query 2: message_content, 2000, 581 > retreives the starting point for a user id.


select ml.message_reference, UTL_RAW.CAST_TO_VARCHAR2 (dbms_lob.substr(message_content, 2000, 581))
from table.msg_archive ma, table.msg_log ml
where ma.message_id = ml.message_id
and ml.message__cd = 'MP_XML'
and ml.message_reference in (456123)


Many thanks,
Laurence