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'