xx
select FI.BATCH_KEY, FI.FILE_KEY ,FILE_DETAIL_FILE ,xmlcast(xmlquery('$c//*:BatchSummary/*:header/*:submittingApplicationId' passing FID.FILE_DETAIL_FILE as "c") as char(50)) as submittingApplicationId ,xmlcast(xmlquery('$c//*:BatchSummary/*:header/*:submittingFacility/@value' passing FID.FILE_DETAIL_FILE as "c") as char(50)) as submittingFacility from NPF.FILE FI join NPF.FILE_DETAIL FID on FID.FILE_KEY = FI.FILE_KEY where MESSAGE_TYPE_CODE = 'SUM'
Get attributes:
select FE.BATCH_KEY, FE.FILE_ERROR_KEY --,FILE_ERROR_DETAIL_FILE ,xmlcast(xmlquery('$c//*/*:encounterDataset/*:encounterType/@value' passing FED.FILE_ERROR_DETAIL_FILE as "c") as char(50)) as encounterType ,xmlcast(xmlquery('$c//*/*:encounterDataset/*:attendanceOutcome/@value' passing FED.FILE_ERROR_DETAIL_FILE as "c") as char(50)) as attendanceOutcome from NPF.FILE_ERROR FE join NPF.BATCH BA on BA.SUBMT_ORGANISATION_CODE = FE.SUBMT_ORGANISATION_CODE and BA.BATCH_KEY = FE.BATCH_KEY join NPF.FILE_ERROR_DETAIL FED on FED.SUBMT_ORGANISATION_CODE = FE.SUBMT_ORGANISATION_CODE and FED.FILE_ERROR_KEY = FE.FILE_ERROR_KEY where MESSAGE_TYPE_CODE in ('ADDENC', 'UPDENC') and PHASE_INDICATOR = 'P2'
select * from ( select FI.*, FID.* ,xmlcast(xmlquery('$c//*/*/*/*:referralId' passing FID.FILE_DETAIL_FILE as "c") as char(50)) as referralId from NPF.FILE FI join NPF.BATCH BA on BA.SUBMT_ORGANISATION_CODE = FI.SUBMT_ORGANISATION_CODE and BA.BATCH_KEY = FI.BATCH_KEY join NPF.FILE_DETAIL FID on FID.SUBMT_ORGANISATION_CODE = FI.SUBMT_ORGANISATION_CODE and FID.FILE_KEY = FI.FILE_KEY where MESSAGE_TYPE_CODE in ('ADDPTA', 'UPDPTA') and PHASE_INDICATOR = 'P2' and FID.SUBMT_ORGANISATION_CODE = 'G00029-G' ) T where referralId = '582174'