DB2 SQL Query XML data element

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'