0 votes
1.2k views
in Interview Questions by (16.3k points)

1 Answer

0 votes
by (16.3k 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 921 views
0 votes
0 answers 643 views
0 votes
1 answer 644 views
0 votes
1 answer 654 views
0 votes
1 answer 726 views
0 votes
0 answers 509 views
0 votes
0 answers 740 views
0 votes
3 answers 2.8k views
0 votes
0 answers 230 views

635 questions

495 answers

98 comments

2.9k users

Join BPM Community Discord Channel

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
...