0 votes
136 views
in Interview Questions by (14.7k points)

1 Answer

0 votes
by (14.7k points)

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

Related questions

+1 vote
1 answer 153 views
0 votes
0 answers 21 views
0 votes
1 answer 42 views
0 votes
1 answer 27 views
0 votes
1 answer 85 views
0 votes
0 answers 29 views
0 votes
0 answers 33 views
0 votes
3 answers 587 views
0 votes
0 answers 26 views

561 questions

455 answers

85 comments

1.7k users

Dosvak IBM BPM /BAW Products, Download Evaluation
Process & Performance Tools Process & Performance Tools
Code Analyzer Code Analyzer
UI Toolkit UI Toolkit
Integration Monitoring Integration Monitoring
Welcome to BPM Tips Q&A, Community wiki/forum where you can ask questions and receive answers from other IBM BPM experts and members of the community. Users with 2000 points will automatically be promoted to expert level.
Created by Dosvak LLC
Our Youtube Channel
...