how to add <?xml version="1.0" encoding="UTF-8"?> in XML output using SQL in OTBI?

User_9F2L2-Oracle

I'm generating a XML file using OTBI data model and layout for an integration with other applications. The xml output is successfully generated in WCC.

The customer requested to add XML Declaration, <?xml version="1.0" encoding="UTF-8"?>, but haven't found a way to do this. See below SQL.

SELECT 

CAND.CANDIDATE_NUMBER,

XMLELEMENT("NS_MANAGEHIRES", XMLATTRIBUTES('R' AS "class"),

XMLELEMENT("HIRE_TYPE", XMLATTRIBUTES('Y' AS "IsChanged"), 'H'),

XMLELEMENT("NS_CRIT_PAY_CAT", XMLATTRIBUTES('Y' AS "IsChanged"), ''),

XMLELEMENT("NS_PGM_ID", XMLATTRIBUTES('Y' AS "IsChanged"), ''),

XMLELEMENT("SRC_KEY", XMLATTRIBUTES('Y' AS "IsChanged"), ''),

XMLELEMENT("EMPLID", XMLATTRIBUTES('Y' AS "IsChanged"), ''),

XMLELEMENT("EMPL_RCD", XMLATTRIBUTES('Y' AS "IsChanged"), ''),

XMLELEMENT("APPLICANT_TYPE", XMLATTRIBUTES('Y' AS "IsChanged"), 'E'),

XMLELEMENT("APPNAME_DISPLAY", XMLATTRIBUTES('Y' AS "IsChanged"), NAME.FULL_NAME),

XMLELEMENT("BUSINESS_UNIT", XMLATTRIBUTES('Y' AS "IsChanged"), 'NSA00'),

XMLELEMENT("DSRD_ST_DT", XMLATTRIBUTES('Y' AS "ISCHANGED"), ASGN.PROJECTED_START_DATE),

-- XMLELEMENT("GVT_PAY_PLAN", XMLATTRIBUTES('Y' AS "IsChanged"), GRD.ATTRIBUTE1),

XMLELEMENT("SAL_ADMIN_PLAN", XMLATTRIBUTES('Y' AS "IsChanged"), '0000'),

XMLELEMENT("GRADE", XMLATTRIBUTES('Y' AS "IsChanged"),GRD.ATTRIBUTE2),

XMLELEMENT("STEP", XMLATTRIBUTES('Y' AS "IsChanged"), STP.SEQUENCE),

XMLELEMENT("GVT_PAY_RATE_DETER", XMLATTRIBUTES('Y' AS "IsChanged"), 'A'),

XMLELEMENT("HRS_PERSON_ID", XMLATTRIBUTES('Y' AS "IsChanged"), CAND.CANDIDATE_NUMBER),

XMLELEMENT("JOBCODE", XMLATTRIBUTES('Y' AS "IsChanged"), ' '),

XMLELEMENT("POSITION_NBR", XMLATTRIBUTES('Y' AS "IsChanged"), ASGN.ASS_ATTRIBUTE5),

XMLELEMENT("SRC_SYSTEM", XMLATTRIBUTES('Y' AS "IsChanged"), ' '),

XMLELEMENT("JOB_CREATE", XMLATTRIBUTES('Y' AS "IsChanged"), ' '),

XMLELEMENT("PERSON_CREATE", XMLATTRIBUTES('Y' AS "IsChanged"), ' '),

XMLELEMENT("SEQ_NBR", XMLATTRIBUTES('Y' AS "IsChanged"), ' '),

XMLELEMENT("SM_RUN", XMLATTRIBUTES('Y' AS "IsChanged"), ' '),

  XMLELEMENT("NS_PREPTOHIRE",XMLATTRIBUTES('R' AS "class"),

XMLELEMENT("APPLICANT_TYPE", XMLATTRIBUTES('Y' AS "IsChanged"), 'E'),

XMLELEMENT("BUSINESS_UNIT", XMLATTRIBUTES('Y' AS "IsChanged"), 'NSA00'),

XMLELEMENT("EMPL_RCD", XMLATTRIBUTES('Y' AS "IsChanged"), '0'),

XMLELEMENT("EMPLID", XMLATTRIBUTES('Y' AS "IsChanged"), ''),

XMLELEMENT("DSRD_ST_DT", XMLATTRIBUTES('Y' AS "IsChanged"),ASGN.PROJECTED_START_DATE),

XMLELEMENT("GVT_PAY_RATE_DETER", XMLATTRIBUTES('Y' AS "IsChanged"), 'A'),

XMLELEMENT("HRS_PERSON_ID", XMLATTRIBUTES('Y' AS "IsChanged"), CAND.CANDIDATE_NUMBER),

XMLELEMENT("HRS_PROFILE_SEQ", XMLATTRIBUTES('Y' AS "IsChanged"), '0'),

XMLELEMENT("JOBCODE", XMLATTRIBUTES('Y' AS "IsChanged"), ' '),

XMLELEMENT("POSITION_NBR", XMLATTRIBUTES('Y' AS "IsChanged"),ASGN.ASS_ATTRIBUTE5),

XMLELEMENT("SAL_ADMIN_PLAN", XMLATTRIBUTES('Y' AS "IsChanged"), '0000'),

XMLELEMENT("GVT_PAY_PLAN", XMLATTRIBUTES('Y' AS "IsChanged"),GRD.ATTRIBUTE1),

XMLELEMENT("GRADE", XMLATTRIBUTES('Y' AS "IsChanged"),GRD.ATTRIBUTE2),

XMLELEMENT("STEP", XMLATTRIBUTES('Y' AS "IsChanged"), STP.SEQUENCE)

  ) "NS_PREPTOHIRE",

    XMlELEMENT("NS_RECRTINGDATA",XMLATTRIBUTES('R' AS "class"),

  XMLELEMENT("PERSON_ID" , XMLATTRIBUTES('Y' AS "IsChanged"), CAND.CANDIDATE_NUMBER),

  XMLELEMENT("ACTION" , XMLATTRIBUTES('Y' AS "IsChanged"), CASE WHEN EXTR.PEI_INFORMATION1 = 'Yes' THEN 'REH' ELSE 'HIR' END),

  XMLELEMENT("ACTION_REASON" , XMLATTRIBUTES('Y' AS "IsChanged"), 'H04'),

   XMLELEMENT("BENEFIT_PROGRAM" , XMLATTRIBUTES('Y' AS "IsChanged"), 'FRA'),

XMLELEMENT("DEPTID" , XMLATTRIBUTES('Y' AS "IsChanged"), 'Y43'),

   XMLELEMENT("FLSA_STATUS" , XMLATTRIBUTES('Y' AS "IsChanged"),''),

XMLELEMENT("HIGHEST_EDUC_LVL",XMLATTRIBUTES('Y' AS "IsChanged"), '13'),

XMLELEMENT("DD_TSP_ELIG_DT",XMLATTRIBUTES('Y' AS "IsChanged"),TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MM:SS') ),

  XMLELEMENT("GVT_TENURE",XMLATTRIBUTES('Y' AS "IsChanged"), '1'),

   XMLELEMENT("GVT_DISABILITY_CD" , XMLATTRIBUTES('Y' AS "IsChanged"),''),

  XMLELEMENT("GVT_ELIG_FEHB" , XMLATTRIBUTES('Y' AS "IsChanged"),''),

  XMLELEMENT("GVT_FEGLI" , XMLATTRIBUTES('Y' AS "IsChanged"),''),

  XMLELEMENT("GVT_NOA_CODE" , XMLATTRIBUTES('Y' AS "IsChanged"),'170'),

  XMLELEMENT("GVT_PAR_NTE_DATE" , XMLATTRIBUTES('Y' AS "IsChanged"),''),

  XMLELEMENT("GVT_TYPE_OF_APPT" , XMLATTRIBUTES('Y' AS "IsChanged"),CASE WHEN ASS_ATTRIBUTE13 = 'Permanent' THEN 38 ELSE 45 END),

  XMLELEMENT("GVT_WORK_SCHED" , XMLATTRIBUTES('Y' AS "IsChanged"), CASE WHEN ASGN.FULL_PART_TIME = 'FULL_TIME' THEN 'F' ELSE 'P' END),

  XMLELEMENT("HIGHEST_EDUC_LVL" , XMLATTRIBUTES('Y' AS "IsChanged"),'13'),

  XMLELEMENT("LOCATION" , XMLATTRIBUTES('Y' AS "IsChanged"),'NSAW'),

  XMLELEMENT("REG_TEMP" , XMLATTRIBUTES('Y' AS "IsChanged"), 'R'),

--   XMLELEMENT("REG_TEMP" , XMLATTRIBUTES('Y' AS "IsChanged"), ASGN.PERMANENT_TEMPORARY_FLAG),

  XMLELEMENT("STD_HOURS" , XMLATTRIBUTES('Y' AS "IsChanged"), ASGN.NORMAL_HOURS)

    ) 

).GETCLOBVAL() AS XML_DATA

FROM IRC_OFFERS OFFR, 

PER_PERSON_NAMES_F_V NAME, 

PER_ALL_ASSIGNMENTS_M ASGN, 

IRC_CANDIDATES CAND, 

PER_GRADES_F GRD, 

PER_ASSIGN_GRADE_STEPS_F AGT, 

PER_GRADE_STEPS_F_VL STP, 

PER_PEOPLE_EXTRA_INFO_F EXTR

WHERE OFFR.OBJECT_STATUS = 'ORA_ACTIVE'

--AND SUBSTR(OFFR.ATTRIBUTE_CHAR19,1,240)='FJO'

AND OFFR.PERSON_ID = NAME.PERSON_ID 

AND OFFR.PERSON_ID = ASGN.PERSON_ID

AND OFFR.PERSON_ID = CAND.PERSON_ID

AND OFFR.PERSON_ID = EXTR.PERSON_ID

AND ASGN.GRADE_ID  = GRD.GRADE_ID

AND ASGN.ASSIGNMENT_ID = AGT.ASSIGNMENT_ID

AND STP.GRADE_ID  = GRD.GRADE_ID

AND AGT.GRADE_STEP_ID = STP.GRADE_STEP_ID

AND EXTR.INFORMATION_TYPE = 'NS_PLACE_OF_BIRTH'

AND SYSDATE BETWEEN ASGN.EFFECTIVE_START_DATE AND ASGN.EFFECTIVE_END_DATE 

AND SYSDATE BETWEEN GRD.EFFECTIVE_START_DATE(+) AND GRD.EFFECTIVE_END_DATE(+) 

AND SYSDATE BETWEEN STP.EFFECTIVE_START_DATE(+) AND STP.EFFECTIVE_END_DATE(+) 

AND CAND.PERSON_ID IN ('300000005198141','300000009424714')

AND ASGN.ASSIGNMENT_ID = '300000010435771'

AND OFFR.OFFER_ID = '300000010435737'

Appreciate really any help on this.

Thank you.