The best way to process xml blob data in columns and query the xml data elements directly is using the XMLTABLE available in oracle and DB2.
One example of getting all validation errors for a snapshot is as follows:
SELECT DISTINCT VER.PO_VERSION_ID, VER.PO_ID, VER.PO_TYPE, PTY.PROCESS_TYPE_ID, VER.PO_GUID, VER.PO_ROOT_ID, VER.LAST_MODIFIED, VER.VALID_STATE, LUX.USER_NAME LAST_MODIFIED_USERNAME, VER.PO_NAME, PT.PO_TABLE_NAME PO_TABLE, PTY.PROCESS_TYPE_NAME,
dbms_lob.substr(LPR.DEPENDENCY_SUMMARY,1000,1) DEPENDENCY_SUMMARY, dbms_lob.substr(LPR.DESCRIPTION,1000,1) DESCRIPTION, LPR.ERROR_HANDLER_ITEM_ID, LPR.EXPOSED_TYPE, LPR.IMAGE, LPR.IS_AJAX_EXPOSED, LPR.IS_ERROR_HANDLER_ENABLED,
LPR.IS_LOGGING_VARIABLES, LPR.IS_ROOT_PROCESS, LPR.IS_SECURED, LPR.IS_TRACKING_ENABLED, LPR.IS_TRANSACTIONAL, LPR.ITEM_LABEL, LPR.MOBILE_READY, LPR.PARTICIPANT_REF, LPR.PROCESS_TIMING_LEVEL,
LPR.SBO_SYNC_ENABLED, LPR.STARTING_PROCESS_ITEM_ID, LPR.TAB_GROUP, XT1.TYPEID, XT1.MESSAGEID, XT1.MESSAGE
FROM LSW_SNAPSHOT SNAP, LSW_BRANCH BRANCH, LSW_PO_VERSIONS VER, PO_TYPES PT, LSW_PROJECT LP, LSW_PROCESS LPR, PROCESS_TYPES PTY, LSW_USR_XREF LUX,
XMLTABLE (XMLNAMESPACES (
'http://lombardisoftware.com/schema/povalidationdata' as "pov"
), '/pov:po-validation-data/pov:validation-err' PASSING xmltype(VER.VALIDATION_ERRS,nls_charset_id('AL32UTF8'))
COLUMNS
TYPEID varchar2(250) PATH '@type' ,
MESSAGEID varchar2(100) PATH '@messageId',
MESSAGE varchar2(1000) PATH '@message' ) AS XT1
WHERE SNAP.PROJECT_ID = BRANCH.PROJECT_ID
AND BRANCH.BRANCH_ID = VER.BRANCH_ID
AND SNAP.BRANCH_ID = VER.BRANCH_ID
AND VER.PO_TYPE = PT.PO_TYPE_ID
AND LP.PROJECT_ID = "BRANCH".PROJECT_ID
AND SNAP.PROJECT_ID = LP.PROJECT_ID
AND LPR.PROCESS_ID = VER.PO_ID
AND LPR.VERSION_ID = VER.PO_VERSION_ID
AND PTY.PROCESS_TYPE_ID(+) = LPR.PROCESS_TYPE
AND VER.LAST_MODIFIED_BY_USER_ID = LUX.USER_ID
AND SNAP.SNAPSHOT_ID LIKE ?
AND VER.PO_TYPE = 1
AND VER.VALIDATION_ERRS is not null