/*Attached is the file with the queries for MS SQL Server 2019
Please check each of the comments to make sure the query works.
Run each of the queries shown below and save them in a single XML file.
Please replace "jiraschema" with the appropriate schema name.*/
/*Place all the result of this query between <CWD_USER></CWD_USER> tags*/
SELECT
concat ( '<row>', '<id>', ID, '</id>', '<user_name>', REPLACE(user_name, '&', '&'), '</user_name>', '<email_address>', REPLACE(email_address, '&', '&'), '</email_address>', '</row>' )
FROM
jiraschema.cwd_user;
/*Place all the result of this query between <ISSUETYPE></ISSUETYPE> tags*/
SELECT
CONCAT('<row>', '<id>', ID, '</id>', '<pname>', REPLACE(REPLACE(REPLACE(pname, '&', '&'), '<', '<'), '>', '<'), '</pname>', '</row>')
FROM
jiraschema.issuetype;
/*Place all the result of this query between <PROJECT_KEY></PROJECT_KEY> tags*/
SELECT
CONCAT('<row>', '<project_id>', project_id, '</project_id>', '<project_key>', project_key, '</project_key>', '</row>')
FROM
jiraschema.project_key;
/*Place all the result of this query between <JIRAISSUE></JIRAISSUE> tags*/
SELECT
CONCAT('<row>', '<id>', ID, '</id>', '<issuenum>', issuenum, '</issuenum>', '<project>', project, '</project>', '</row>')
FROM
jiraschema.jiraissue;
/*Place all the result of this query between <ISSUESTATUS></ISSUESTATUS> tags*/
SELECT
CONCAT('<row>', '<id>', ID, '</id>', '<pname>', REPLACE(REPLACE(REPLACE(pname, '&', '&'), '<', '<'), '>', '<'), '</pname>', '</row>')
FROM
jiraschema.issuestatus;
/*Place all the result of this query between <CUSTOMFIELD></CUSTOMFIELD> tags*/
SELECT
CONCAT('<row>', '<id>', ID, '</id>', '<cfname>', REPLACE(REPLACE(REPLACE(cfname, '&', '&'), '<', '<'), '>', '<'), '</cfname>', '</row>')
FROM
jiraschema.customfield
WHERE
customfieldtypekey IN ('com.atlassian.jira.plugin.system.customfieldtypes:userpicker', 'com.atlassian.jira.plugin.system.customfieldtypes:multigrouppicker', 'com.atlassian.jira.plugin.system.customfieldtypes:multiuserpicker', 'com.atlassian.jira.plugin.system.customfieldtypes:grouppicker');
/*Place all the results of this query between <WORKFLOWS></WORKFLOWS> tags*/
--Execute the following SQL query to select the workflow DESCRIPTOR field data.
SELECT [DESCRIPTOR] FROM [jiraschema].[jiraworkflows]
/*Copy the result of the SQL query and paste it into a text editor.
In the text editor, search for matches with the following text: <action id=" " name=" "...> .
For each line that matches <action id=" " name=" "...>, select it and compile them into a new section of the XML File.
This section should be enclosed between <WORKFLOWS> and </WORKFLOWS> tags, and each line should resemble <action id=...></action>.
Final Result: Once you've compiled all the matching lines, your final result should look something like this:
<WORKFLOWS>
<action id=...></action>
.
..
.
<action...></action>
</WORKFLOWS>
*/
/*Place all the result of this query between <AO_D195E9_APPROVAL></AO_D195E9_APPROVAL> tags*/
SELECT
CONCAT('<row>' , '<ASSIGNEE_ENABLED>' , CASE WHEN ASSIGNEE_ENABLED = 0 THEN 'f' ELSE 't' END , '</ASSIGNEE_ENABLED>' ,
'<CONDITIONAL_RULE_ID>' , case when conditional_rule_id is not null then COALESCE(CONDITIONAL_RULE_ID,'') else null end , '</CONDITIONAL_RULE_ID>' ,
'<CONFIG_ID>' , COALESCE(CONFIG_ID,'') , '</CONFIG_ID>' ,
'<DESCRIPTION>' , COALESCE(DESCRIPTION,'') , '</DESCRIPTION>' ,
'<GROUPS>' , COALESCE(AO_D195E9_APPROVAL.GROUPS,'') , '</GROUPS>' ,
'<GROUP_PICKER>' , COALESCE(GROUP_PICKER,'') , '</GROUP_PICKER>' ,
'<ID>' , ID , '</ID>' , '<NAME>' , NAME , '</NAME>' ,
'<NOT_ASSIGNEE>' , CASE WHEN NOT_ASSIGNEE = 0 THEN 'f' ELSE 't' END , '</NOT_ASSIGNEE>' ,
'<NOT_GROUPS>' , CASE WHEN NOT_GROUPS = 0 THEN 'f' ELSE 't' END , '</NOT_GROUPS>' ,
'<NOT_GROUP_PICKER>' , CASE WHEN NOT_GROUP_PICKER = 0 THEN 'f' ELSE 't' END , '</NOT_GROUP_PICKER>' ,
'<NOT_LEAD>' , CASE WHEN NOT_LEAD = 0 THEN 'f' ELSE 't' END , '</NOT_LEAD>' ,
'<NOT_REPORTER>' , CASE WHEN NOT_REPORTER = 0 THEN 'f' ELSE 't' END , '</NOT_REPORTER>' ,
'<NOT_ROLES>' , CASE WHEN NOT_ROLES = 0 THEN 'f' ELSE 't' END , '</NOT_ROLES>' ,
'<NOT_USERS>' , CASE WHEN NOT_USERS = 0 THEN 'f' ELSE 't' END , '</NOT_USERS>' ,
'<NOT_USER_PICKER>' , CASE WHEN NOT_USER_PICKER = 0 THEN 'f' ELSE 't' END , '</NOT_USER_PICKER>' ,
'<NOT_USER_PICKER_ANY>' , CASE WHEN NOT_USER_PICKER_ANY = 0 THEN 'f' ELSE 't' END , '</NOT_USER_PICKER_ANY>' ,
'<OPERATOR>' , COALESCE(OPERATOR,'') , '</OPERATOR>' ,
'<PROJECT_LEAD_ENABLED>' , CASE WHEN PROJECT_LEAD_ENABLED = 0 THEN 'f' ELSE 't' END , '</PROJECT_LEAD_ENABLED>' ,
'<REPORTER_ENABLED>' , CASE WHEN REPORTER_ENABLED = 0 THEN 'f' ELSE 't' END , '</REPORTER_ENABLED>' ,
'<ROLES>' , COALESCE(ROLES,'') , '</ROLES>' ,
'<SA>' , CASE WHEN SA = 0 THEN 'f' ELSE 't' END , '</SA>' ,
'<SHOW_TYPE>' , CASE WHEN COALESCE(SHOW_TYPE,'') = 0 THEN 'f' else 't'END , '</SHOW_TYPE>' ,
'<USERS>' , REPLACE ( USERS, '&', '&' ) , '</USERS>' ,
'<USER_PICKER>' , COALESCE(USER_PICKER,'') , '</USER_PICKER>' ,
'<USER_PICKER_ALL_EXPR>' , COALESCE(USER_PICKER_ALL_EXPR,'') , '</USER_PICKER_ALL_EXPR>' ,
'<USER_PICKER_ANY>' , COALESCE(USER_PICKER_ANY,'') , '</USER_PICKER_ANY>' ,
'<USER_PICKER_ANY_EXPR>' , COALESCE(USER_PICKER_ANY_EXPR,'') , '</USER_PICKER_ANY_EXPR>' ,
'<EXPR_IN_DETAIL>' , CASE WHEN EXPR_IN_DETAIL = 0 THEN 'f' ELSE 't' END , '</EXPR_IN_DETAIL>' ,
'</row>' )
FROM
jiraschema.AO_D195E9_APPROVAL
/*Place all the result of this query between <AO_D195E9_CONFIG_PROPERTIES></AO_D195E9_CONFIG_PROPERTIES> tags*/
SELECT
CONCAT('<row>' ,
'<DESCRIPTION>', DESCRIPTION, '</DESCRIPTION>',
'<ID>', ID, '</ID>',
'<PROPERTY>', PROPERTY, '</PROPERTY>',
'<VALUE>', VALUE, '</VALUE>',
'</row>') data_xml
FROM jiraschema.AO_D195E9_CONFIG_PROPERTIES;
/*Place all the result of this query between <AO_D195E9_CONFIG></AO_D195E9_CONFIG> tags*/
SELECT
(
CONCAT( '<row>',
'<ABSTAIN_ACTION_ENABLED>', CASE WHEN ABSTAIN_ACTION_ENABLED = 0 THEN 'f' ELSE 't' END , '</ABSTAIN_ACTION_ENABLED>',
'<APPROVAL_TAG>', COALESCE(APPROVAL_TAG,''), '</APPROVAL_TAG>',
'<APP_COND_WF_UPDATABLE>', CASE WHEN APP_COND_WF_UPDATABLE = 0 THEN 'f' ELSE 't' END , '</APP_COND_WF_UPDATABLE>',
'<APP_PANEL_FILTER>', COALESCE(APP_PANEL_FILTER,''), '</APP_PANEL_FILTER>',
'<AUTO_GRANT_PERM>', CASE WHEN AUTO_GRANT_PERM = 0 THEN 'f' ELSE 't' END , '</AUTO_GRANT_PERM>',
'<AWT_CMRA>', CASE WHEN AWT_CMRA = 0 THEN 'f' ELSE 't' END , '</AWT_CMRA>',
'<CHECK_INFO>', COALESCE(CHECK_INFO,''), '</CHECK_INFO>',
'<CUSTOM_COMMENT>', COALESCE(REPLACE(REPLACE (REPLACE ( CUSTOM_COMMENT, '&', '&' ), '<', '<'),'>','>'),''), '</CUSTOM_COMMENT>',
'<CUSTOM_DEFINE_APPROVAL>', CASE WHEN CUSTOM_DEFINE_APPROVAL = 0 THEN 'f' ELSE 't' END, '</CUSTOM_DEFINE_APPROVAL>',
'<CUSTOM_REQUESTER_APPROVAL>', CASE WHEN CUSTOM_REQUESTER_APPROVAL = 0 THEN 'f' ELSE 't' END , '</CUSTOM_REQUESTER_APPROVAL>',
'<DELEGATION_ENABLED>', CASE WHEN DELEGATION_ENABLED = 0 THEN 'f' ELSE 't' END , '</DELEGATION_ENABLED>',
'<ENABLED_NAC>', CASE WHEN ENABLED_NAC = 0 THEN 'f' ELSE 't' END , '</ENABLED_NAC>',
'<EXPANED_APP_PANEL>', CASE WHEN EXPANED_APP_PANEL = 0 THEN 'f' ELSE 't' END , '</EXPANED_APP_PANEL>',
'<FROM_EMAIL>', COALESCE(FROM_EMAIL,''), '</FROM_EMAIL>',
'<HIDE_HACTION_ABSTAINED>', CASE WHEN HIDE_HACTION_ABSTAINED = 0 THEN 'f' ELSE 't' END , '</HIDE_HACTION_ABSTAINED>',
'<HIDE_HACTION_APPROVED>', CASE WHEN HIDE_HACTION_APPROVED = 0 THEN 'f' ELSE 't' END , '</HIDE_HACTION_APPROVED>',
'<HIDE_HACTION_APP_NOTIFY>', CASE WHEN HIDE_HACTION_APP_NOTIFY = 0 THEN 'f' ELSE 't' END , '</HIDE_HACTION_APP_NOTIFY>',
'<HIDE_HACTION_AUNOTIFY>', CASE WHEN HIDE_HACTION_AUNOTIFY = 0 THEN 'f' ELSE 't' END , '</HIDE_HACTION_AUNOTIFY>',
'<HIDE_HACTION_AUTRANS>', CASE WHEN HIDE_HACTION_AUTRANS = 0 THEN 'f' ELSE 't' END , '</HIDE_HACTION_AUTRANS>',
'<HIDE_HACTION_COMMENT>', CASE WHEN HIDE_HACTION_COMMENT = 0 THEN 'f' ELSE 't' END , '</HIDE_HACTION_COMMENT>',
'<HIDE_HACTION_NPENDING>', CASE WHEN HIDE_HACTION_NPENDING = 0 THEN 'f' ELSE 't' END , '</HIDE_HACTION_NPENDING>',
'<HIDE_HACTION_PENDING>', CASE WHEN HIDE_HACTION_PENDING = 0 THEN 'f' ELSE 't' END , '</HIDE_HACTION_PENDING>',
'<HIDE_HACTION_REJECTED>', CASE WHEN HIDE_HACTION_REJECTED = 0 THEN 'f' ELSE 't' END , '</HIDE_HACTION_REJECTED>',
'<HIDE_HACTION_REPLY_NOTIFY>', CASE WHEN HIDE_HACTION_REPLY_NOTIFY = 0 THEN 'f' ELSE 't' END , '</HIDE_HACTION_REPLY_NOTIFY>',
'<HIDE_HACTION_SING_NOTIFY>', CASE WHEN HIDE_HACTION_SING_NOTIFY = 0 THEN 'f' ELSE 't' END , '</HIDE_HACTION_SING_NOTIFY>',
'<HIDE_HISTORY_ACTION>', CASE WHEN HIDE_HISTORY_ACTION = 0 THEN 'f' ELSE 't' END , '</HIDE_HISTORY_ACTION>',
'<HIDE_HISTORY_APPROVAL>', CASE WHEN HIDE_HISTORY_APPROVAL = 0 THEN 'f' ELSE 't' END , '</HIDE_HISTORY_APPROVAL>',
'<HIDE_HISTORY_APPROVER_C>', CASE WHEN HIDE_HISTORY_APPROVER_C = 0 THEN 'f' ELSE 't' END , '</HIDE_HISTORY_APPROVER_C>',
'<HIDE_HISTORY_DESTINATION_S>', CASE WHEN HIDE_HISTORY_DESTINATION_S = 0 THEN 'f' ELSE 't' END , '</HIDE_HISTORY_DESTINATION_S>',
'<HIDE_HISTORY_SOURCE_S>', CASE WHEN HIDE_HISTORY_SOURCE_S = 0 THEN 'f' ELSE 't' END , '</HIDE_HISTORY_SOURCE_S>',
'<HIDE_HISTORY_TRANS>', CASE WHEN HIDE_HISTORY_TRANS = 0 THEN 'f' ELSE 't' END , '</HIDE_HISTORY_TRANS>',
'<HIDE_MENU>', CASE WHEN HIDE_MENU = 0 THEN 'f' ELSE 't' END , '</HIDE_MENU>' ,
'<HIDE_PACOMMENT>', CASE WHEN HIDE_PACOMMENT = 0 THEN 'f' ELSE 't' END , '</HIDE_PACOMMENT>',
'<HIDE_PACTION>', CASE WHEN HIDE_PACTION = 0 THEN 'f' ELSE 't' END , '</HIDE_PACTION>',
'<HIDE_PAPP_NAME>', CASE WHEN HIDE_PAPP_NAME = 0 THEN 'f' ELSE 't' END , '</HIDE_PAPP_NAME>',
'<HIDE_PDETAIL>', CASE WHEN HIDE_PDETAIL = 0 THEN 'f' ELSE 't' END , '</HIDE_PDETAIL>',
'<HIDE_PREQUEST_NOTIF>', CASE WHEN HIDE_PREQUEST_NOTIF = 0 THEN 'f' ELSE 't' END , '</HIDE_PREQUEST_NOTIF>',
'<HISTORY_COLUMN_TITLES>', COALESCE(HISTORY_COLUMN_TITLES,''), '</HISTORY_COLUMN_TITLES>',
'<HISTORY_EXPAND_ROWS>', CASE WHEN HISTORY_EXPAND_ROWS = 0 THEN 'f' ELSE 't' END , '</HISTORY_EXPAND_ROWS>',
'<HISTORY_FILTER>', COALESCE(HISTORY_FILTER,''), '</HISTORY_FILTER>',
'<HISTORY_GROUP_BY>', COALESCE(HISTORY_GROUP_BY,''), '</HISTORY_GROUP_BY>',
'<ID>', ID, '</ID>',
'<LABEL_ABSTAIN_BUTTON>', COALESCE(LABEL_ABSTAIN_BUTTON,''), '</LABEL_ABSTAIN_BUTTON>',
'<LABEL_ACTION_APPROVAL>', COALESCE(LABEL_ACTION_APPROVAL,''), '</LABEL_ACTION_APPROVAL>',
'<LABEL_APPROVAL_BUTTON>', COALESCE(LABEL_APPROVAL_BUTTON,''), '</LABEL_APPROVAL_BUTTON>',
'<LABEL_APPROVAL_NAME>', COALESCE(LABEL_APPROVAL_NAME,''), '</LABEL_APPROVAL_NAME>',
'<LABEL_APP_COMMENT>', COALESCE(LABEL_APP_COMMENT,''), '</LABEL_APP_COMMENT>',
'<LABEL_DETAIL>', COALESCE(LABEL_DETAIL,''), '</LABEL_DETAIL>',
'<LABEL_HISTORY_ACTION>', COALESCE(LABEL_HISTORY_ACTION,''), '</LABEL_HISTORY_ACTION>',
'<LABEL_HISTORY_APPROVAL>', COALESCE(LABEL_HISTORY_APPROVAL,''), '</LABEL_HISTORY_APPROVAL>',
'<LABEL_HISTORY_APPROVER_C>', COALESCE(LABEL_HISTORY_APPROVER_C,''), '</LABEL_HISTORY_APPROVER_C>',
'<LABEL_HISTORY_DESTINATION_S>', COALESCE(LABEL_HISTORY_DESTINATION_S,''), '</LABEL_HISTORY_DESTINATION_S>',
'<LABEL_HISTORY_SOURCE_S>', COALESCE(LABEL_HISTORY_SOURCE_S,''), '</LABEL_HISTORY_SOURCE_S>',
'<LABEL_HISTORY_TRANS>', COALESCE(LABEL_HISTORY_TRANS,''), '</LABEL_HISTORY_TRANS>',
'<LABEL_REJECT_BUTTON>', COALESCE(LABEL_REJECT_BUTTON,''), '</LABEL_REJECT_BUTTON>',
'<LABEL_REQUEST_APPROVAL>', COALESCE(LABEL_REQUEST_APPROVAL,''), '</LABEL_REQUEST_APPROVAL>',
'<LABEL_REQUEST_APPROVALS>', COALESCE(LABEL_REQUEST_APPROVALS,''), '</LABEL_REQUEST_APPROVALS>',
'<LABEL_REQUEST_NOTIFY>', COALESCE(LABEL_REQUEST_NOTIFY,''), '</LABEL_REQUEST_NOTIFY>',
'<LABEL_RESET_BUTTON>', COALESCE(LABEL_RESET_BUTTON,''), '</LABEL_RESET_BUTTON>',
'<LARGE_BUTTONS>', COALESCE(LARGE_BUTTONS,''), '</LARGE_BUTTONS>',
'<LOCK_ACTION_ENABLED>', CASE WHEN LOCK_ACTION_ENABLED = 0 THEN 'f' ELSE 't' END , '</LOCK_ACTION_ENABLED>',
'<NOTIFICATION_RECIPIENT_CUSTOM>', CASE WHEN NOTIFICATION_RECIPIENT_CUSTOM = 0 THEN 'f' ELSE 't' END , '</NOTIFICATION_RECIPIENT_CUSTOM>',
'<RESPONSIVE_AWT>', CASE WHEN RESPONSIVE_AWT = 0 THEN 'f' ELSE 't' END , '</RESPONSIVE_AWT>',
'<TAB_COMM_DISABLED>', CASE WHEN TAB_COMM_DISABLED = 0 THEN 'f' ELSE 't' END , '</TAB_COMM_DISABLED>' ,
'<TAB_HISTORY_ENABLED>', CASE WHEN TAB_HISTORY_ENABLED = 0 THEN 'f' ELSE 't' END , '</TAB_HISTORY_ENABLED>',
'<USER_APPROVER_BULK>', COALESCE(USER_APPROVER_BULK,''), '</USER_APPROVER_BULK>',
'<USER_AWT>', COALESCE(USER_AWT,''), '</USER_AWT>',
'<USER_COND_RULE>', COALESCE(USER_COND_RULE,''), '</USER_COND_RULE>',
'<USER_LARGE_BUTTONS>', COALESCE(USER_LARGE_BUTTONS,''), '</USER_LARGE_BUTTONS>',
'<USER_NOTIFY_NAC>', COALESCE(USER_NOTIFY_NAC,''), '</USER_NOTIFY_NAC>',
'<VIEW_WORKFLOW>', CASE WHEN VIEW_WORKFLOW = 0 THEN 'f' ELSE 't' END , '</VIEW_WORKFLOW>',
'</row>')) data_xml
FROM jiraschema.AO_D195E9_CONFIG
/*Place all the result of this query between <AO_D195E9_CONDITIONAL_RULE></AO_D195E9_CONDITIONAL_RULE> tags*/
SELECT
CONCAT( '<row>',
'<CONDITIONAL_NAME>', CONDITIONAL_NAME, '</CONDITIONAL_NAME>',
'<CONDITIONAL_RULE>', CONDITIONAL_RULE, '</CONDITIONAL_RULE>',
'<ID>', ID, '</ID>',
'<USER_EVALUATOR>', USER_EVALUATOR, '</USER_EVALUATOR>',
'</row>') data_xml
FROM jiraschema.AO_D195E9_CONDITIONAL_RULE;
/*Place all the result of this query between <AO_D195E9_CUSTOM_MAIL_TEMPLATE></AO_D195E9_CUSTOM_MAIL_TEMPLATE> tags*/
SELECT (
CONCAT ('<row>',
'<BODY_SECTION>', REPLACE (COALESCE(BODY_SECTION,''), '&', '&'), '</BODY_SECTION>',
'<BUTTON_LINKS_TYPE>', COALESCE(BUTTON_LINKS_TYPE,''), '</BUTTON_LINKS_TYPE>',
'<BUTTON_TEXTS>', COALESCE(BUTTON_TEXTS,''), '</BUTTON_TEXTS>',
'<COMMENT_ENABLED>', CASE WHEN COMMENT_ENABLED = 0 THEN 'f' ELSE 't' END , '</COMMENT_ENABLED>',
'<COMMENT_ENABLED_IF_REQUIRED>', CASE WHEN COMMENT_ENABLED_IF_REQUIRED = 0 THEN 'f' ELSE 't' END , '</COMMENT_ENABLED_IF_REQUIRED>',
'<CONDITIONAL_RULE_ID>', case when CONDITIONAL_RULE_ID in (null, 0) then null else COALESCE(CONDITIONAL_RULE_ID,'') end, '</CONDITIONAL_RULE_ID>',
'<CONDITION_ENABLING_TEMPLATE_ID>', case when CONDITION_ENABLING_TEMPLATE_ID IS NOT NULL then COALESCE(CONDITION_ENABLING_TEMPLATE_ID,'') else null end, '</CONDITION_ENABLING_TEMPLATE_ID>',
'<CUSTOM_APPROVAL_INSTRUCT>', REPLACE (COALESCE(CUSTOM_APPROVAL_INSTRUCT,''), '&', '&'), '</CUSTOM_APPROVAL_INSTRUCT>',
'<CUSTOM_TEMPLATE_NAME>', COALESCE(CUSTOM_TEMPLATE_NAME,''), '</CUSTOM_TEMPLATE_NAME>',
'<FOOTER>', COALESCE(FOOTER,''), '</FOOTER>',
'<HEADER>', COALESCE(HEADER,''), '</HEADER>',
'<HIDE_APPROVAL_BUTTON>', CASE WHEN HIDE_APPROVAL_BUTTON = 0 THEN 'f' ELSE 't' END , '</HIDE_APPROVAL_BUTTON>',
'<HIDE_APPROVAL_COMMENT>', CASE WHEN HIDE_APPROVAL_COMMENT = 0 THEN 'f' ELSE 't' END , '</HIDE_APPROVAL_COMMENT>',
'<HIDE_APPROVAL_INSTRUCT>', CASE WHEN HIDE_APPROVAL_INSTRUCT = 0 THEN 'f' ELSE 't' END , '</HIDE_APPROVAL_INSTRUCT>',
'<HIDE_ASSIGNEE>', CASE WHEN HIDE_ASSIGNEE = 0 THEN 'f' ELSE 't' END , '</HIDE_ASSIGNEE>',
'<HIDE_COMMENT>', CASE WHEN HIDE_COMMENT = 0 THEN 'f' ELSE 't' END , '</HIDE_COMMENT>' ,
'<HIDE_CREATED>', CASE WHEN HIDE_CREATED = 0 THEN 'f' ELSE 't' END , '</HIDE_CREATED>',
'<HIDE_DESCRIPTION>', CASE WHEN HIDE_DESCRIPTION = 0 THEN 'f' ELSE 't' END , '</HIDE_DESCRIPTION>',
'<HIDE_ISSUE_TYPE>', CASE WHEN HIDE_ISSUE_TYPE = 0 THEN 'f' ELSE 't' END , '</HIDE_ISSUE_TYPE>',
'<HIDE_LOGO>', CASE WHEN HIDE_LOGO = 0 THEN 'f' ELSE 't' END , '</HIDE_LOGO>',
'<HIDE_PRIORITY>', CASE WHEN HIDE_PRIORITY = 0 THEN 'f' ELSE 't' END , '</HIDE_PRIORITY>',
'<HIDE_PROJECT>', CASE WHEN HIDE_PROJECT = 0 THEN 'f' ELSE 't' END , '</HIDE_PROJECT>',
'<HIDE_REPORTER>', CASE WHEN HIDE_REPORTER = 0 THEN 'f' ELSE 't' END , '</HIDE_REPORTER>',
'<HIDE_WORKFLOW_TRANS>', CASE WHEN HIDE_WORKFLOW_TRANS = 0 THEN 'f' ELSE 't' END , '</HIDE_WORKFLOW_TRANS>',
'<ID>', ID, '</ID>',
'<IS_BOTTOM_BODY_SECTION>', CASE WHEN IS_BOTTOM_BODY_SECTION = 0 THEN 'f' ELSE 't' END , '</IS_BOTTOM_BODY_SECTION>',
'<IS_BOTTOM_BUTTON>', CASE WHEN IS_BOTTOM_BUTTON = 0 THEN 'f' ELSE 't' END , '</IS_BOTTOM_BUTTON>',
'<IS_EMAIL_TEMPLATE_DEFAULT>', CASE WHEN IS_EMAIL_TEMPLATE_DEFAULT = 0 THEN 'f' ELSE 't' END , '</IS_EMAIL_TEMPLATE_DEFAULT>',
'<IS_EMAIL_TEMPLATE_REPLY>', CASE WHEN IS_EMAIL_TEMPLATE_REPLY = 0 THEN 'f' ELSE 't' END , '</IS_EMAIL_TEMPLATE_REPLY>',
'<PROJECT_ID>', case when PROJECT_ID is not null then COALESCE(PROJECT_ID,'') else null end, '</PROJECT_ID>',
'<SHOW_LINKED_ISSUES>', CASE WHEN SHOW_LINKED_ISSUES = 0 THEN 'f' ELSE 't' END , '</SHOW_LINKED_ISSUES>',
'<SHOW_SUBTASKS>', CASE WHEN SHOW_SUBTASKS = 0 THEN 'f' ELSE 't' END , '</SHOW_SUBTASKS>',
'<SUBJECT>', REPLACE (COALESCE(SUBJECT,''), '&','&'), '</SUBJECT>',
'<TYPE>', COALESCE(TYPE,''), '</TYPE>',
'</row>')) data_xml
FROM jiraschema.AO_D195E9_CUSTOM_MAIL_TEMPLATE;
/*Place all the result of this query between <AO_D195E9_APPROVAL_NOT_NOTIFY></AO_D195E9_APPROVAL_NOT_NOTIFY> tags*/
SELECT
CONCAT('<row>',
'<ID>', ID, '</ID>',
'<RECIPIENT>', RECIPIENT, '</RECIPIENT>',
'<TYPE>', TYPE, '</TYPE>',
'</row>') data_xml
FROM
jiraschema."AO_D195E9_APPROVAL_NOT_NOTIFY";
/*Place all the result of this query between <AO_D195E9_NTF_SCHEME></AO_D195E9_NTF_SCHEME> tags*/
SELECT
CONCAT(
'<row>',
'<ID>', ID, '</ID>',
'<DESCRIPTION>', REPLACE(REPLACE(REPLACE(DESCRIPTION, '&', '&'), '<', '<'), '>', '<'), '</DESCRIPTION>',
'<NAME>', REPLACE(REPLACE(REPLACE(NAME, '&', '&'), '<', '<'), '>', '<'), '</NAME>',
'</row>'
)
AS data_xml
FROM
jiraschema."AO_D195E9_NTF_SCHEME";
/*Place all the result of this query between <AO_D195E9_NTF_SCHEME_DETAIL></AO_D195E9_NTF_SCHEME_DETAIL> tags*/
SELECT
CONCAT ('<row>',
'<ID>', ID, '</ID>',
'<CUSTOM_MAIL_TEMPLATE_ID>', CUSTOM_MAIL_TEMPLATE_ID, '</CUSTOM_MAIL_TEMPLATE_ID>',
'<EVENT_TYPE_ID>',EVENT_TYPE_ID,'</EVENT_TYPE_ID>',
'<NOTIFY_EXPRESSION>', REPLACE( REPLACE( REPLACE( NOTIFY_EXPRESSION, '&', '&' ), '<', '<' ), '>', '<' ), '</NOTIFY_EXPRESSION>',
'<NTF_SCHEME_ID>', NTF_SCHEME_ID, '</NTF_SCHEME_ID>',
'</row>'
) AS data_xml
FROM
jiraschema."AO_D195E9_NTF_SCHEME_DETAIL";
/*Place all the result of this query between <AO_D195E9_APPROVAL_RECIPIENTS></AO_D195E9_APPROVAL_RECIPIENTS> tags*/
SELECT
CONCAT(
'<row>',
'<ID>', ID, '</ID>',
'<EVENTS>', EVENTS, '</EVENTS>',
'<RECIPIENT>', RECIPIENT, '</RECIPIENT>',
'<TYPE>', TYPE, '</TYPE>',
'</row>'
)
AS data_xml
FROM
jiraschema."AO_D195E9_APPROVAL_RECIPIENTS";
/*Place all the result of this query between <AO_D195E9_APPROVAL_PROJECTS></AO_D195E9_APPROVAL_PROJECTS> tags*/
SELECT
CONCAT(
'<row>',
'<ID>', ID, '</ID>',
'<PROJECT_ID>', PROJECT_ID, '</PROJECT_ID>',
'<REQUESTER_TYPE>', REQUESTER_TYPE, '</REQUESTER_TYPE>',
'</row>'
)AS data_xml
FROM
jiraschema.AO_D195E9_APPROVAL_PROJECTS;
/*Place all the result of this query between <AO_D195E9_APPROVAL_REQUESTERS></AO_D195E9_APPROVAL_REQUESTERS> tags*/
SELECT
CONCAT(
'<row>',
'<ID>', ID, '</ID>',
'<ISSUE_TYPE_ID>', ISSUE_TYPE_ID, '</ISSUE_TYPE_ID>',
'<PROJECT_ID>', PROJECT_ID, '</PROJECT_ID>',
'<REQUESTER>', REQUESTER, '</REQUESTER>',
'<TYPE>', TYPE, '</TYPE>',
'</row>'
)AS data_xml
FROM
jiraschema."AO_D195E9_APPROVAL_REQUESTERS"
WHERE
"TYPE" != 'NOTYPE';
/*Place all the result of this query between <AO_D195E9_APPROVAL_DEFINERS></AO_D195E9_APPROVAL_DEFINERS> tags*/
SELECT
CONCAT(
'<row>',
'<ID>', ID, '</ID>',
'<ISSUE_TYPE_ID>', ISSUE_TYPE_ID, '</ISSUE_TYPE_ID>',
'<PROJECT_ID>', PROJECT_ID, '</PROJECT_ID>',
'<DEFINER>', DEFINER, '</DEFINER>',
'<TYPE>', TYPE, '</TYPE>',
'</row>'
)AS data_xml
FROM
jiraschema."AO_D195E9_APPROVAL_DEFINERS";
/*Place all the result of this query between <AO_D195E9_APPROVAL_MENU_PERM></AO_D195E9_APPROVAL_MENU_PERM> tags*/
SELECT
CONCAT(
'<row>',
'<ID>', ID, '</ID>',
'<ITEM>', ITEM, '</ITEM>',
'<TYPE>', TYPE, '</TYPE>',
'</row>'
) AS data_xml
FROM
jiraschema.AO_D195E9_APPROVAL_MENU_PERM;
/*Place all the result of this query between <AO_D195E9_APPROVAL_MAPPING></AO_D195E9_APPROVAL_MAPPING> tags*/
SELECT
CONCAT('<row>',
'<ABSTAIN_ACTION_STATUS>', COALESCE(ABSTAIN_ACTION_STATUS,''), '</ABSTAIN_ACTION_STATUS>',
'<AREA_COND_RULE_ID>', COALESCE(AREA_COND_RULE_ID,''), '</AREA_COND_RULE_ID>',
'<AREA_DATE_PICKER>', COALESCE(AREA_DATE_PICKER,''), '</AREA_DATE_PICKER>',
'<AREA_ELAPSED_TIME>', COALESCE(AREA_ELAPSED_TIME,''), '</AREA_ELAPSED_TIME>',
'<AREA_SERVICE_NAME>', COALESCE(AREA_SERVICE_NAME,''), '</AREA_SERVICE_NAME>',
'<ARN>', CASE WHEN ARN = 0 THEN 'f' ELSE 't' END, '</ARN>',
'<AWT>', CASE WHEN AWT = 0 THEN 'f' ELSE 't' END , '</AWT>',
'<AWTAD>', CASE WHEN AWTAD = 0 THEN 'f' ELSE 't' END , '</AWTAD>',
'<AWTR>', CASE WHEN AWTR = 0 THEN 'f' ELSE 't' END , '</AWTR>',
'<CONDITIONAL_RULE_ID>', COALESCE(CONDITIONAL_RULE_ID,''), '</CONDITIONAL_RULE_ID>',
'<CONFIG_ID>', COALESCE(CONFIG_ID,''), '</CONFIG_ID>',
'<DA_DECISION>', COALESCE(DA_DECISION,''), '</DA_DECISION>',
'<DA_ELAPSED_TIME>', COALESCE(DA_ELAPSED_TIME,''), '</DA_ELAPSED_TIME>',
'<DA_SERVICE_NAME>', COALESCE(DA_SERVICE_NAME,''), '</DA_SERVICE_NAME>',
'<DA_USER_KEY>', COALESCE(DA_USER_KEY,''), '</DA_USER_KEY>',
'<DESTINATION_STATUS_ID>', COALESCE(DESTINATION_STATUS_ID,''), '</DESTINATION_STATUS_ID>',
'<ID>', ID, '</ID>',
'<ISSUE_TYPE_ID>', COALESCE(ISSUE_TYPE_ID,''), '</ISSUE_TYPE_ID>',
'<IS_DELETE>', CASE WHEN IS_DELETE = 0 THEN 'f' ELSE 't' END , '</IS_DELETE>',
'<IS_DRAFT>', CASE WHEN IS_DRAFT = 0 THEN 'f' ELSE 't' END , '</IS_DRAFT>',
'<IS_LOCKED>', CASE WHEN IS_LOCKED = 0 THEN 'f' ELSE 't' END , '</IS_LOCKED>',
'<IS_NOT_SINGLE>', CASE WHEN IS_NOT_SINGLE = 0 THEN 'f' ELSE 't' END , '</IS_NOT_SINGLE>',
'<LOCKED_DATE>', COALESCE(LOCKED_DATE,''), '</LOCKED_DATE>',
'<LOCKED_INFO>', COALESCE(LOCKED_INFO,''), '</LOCKED_INFO>',
'<MAIL_TEMPLATE_REPLY_ID>',CASE WHEN MAIL_TEMPLATE_REPLY_ID=0 OR MAIL_TEMPLATE_REPLY_ID IS NULL THEN NULL ELSE COALESCE(MAIL_TEMPLATE_REPLY_ID,'') END, '</MAIL_TEMPLATE_REPLY_ID>',
'<MAIL_TEMPLATE_REQUEST_ID>',CASE WHEN MAIL_TEMPLATE_REQUEST_ID=0 OR MAIL_TEMPLATE_REQUEST_ID IS NULL THEN NULL ELSE COALESCE(MAIL_TEMPLATE_REQUEST_ID,'') END, '</MAIL_TEMPLATE_REQUEST_ID>',
'<MRA>', COALESCE(MRA,''), '</MRA>',
'<MRR>', COALESCE(MRR,''), '</MRR>',
'<NAC>', CASE WHEN NAC = 0 THEN 'f' ELSE 't' END , '</NAC>',
'<ORDER_MAP>', COALESCE(ORDER_MAP,''), '</ORDER_MAP>',
'<PROJECT_ID>', COALESCE(PROJECT_ID,''), '</PROJECT_ID>',
'<RAC>', CASE WHEN RAC = 0 THEN 'f' ELSE 't' END , '</RAC>',
'<RACA>', COALESCE(RACA,''), '</RACA>',
'<REPLY_NOTIFICATION_SCHEMA_ID>', CASE WHEN REPLY_NOTIFICATION_SCHEMA_ID =0 OR REPLY_NOTIFICATION_SCHEMA_ID IS NULL THEN NULL ELSE COALESCE(REPLY_NOTIFICATION_SCHEMA_ID,'') END, '</REPLY_NOTIFICATION_SCHEMA_ID>',
'<SA>', CASE WHEN SA = 0 THEN 'f' ELSE 't' END, '</SA>',
'<SAAD>', CASE WHEN SAAD = 0 THEN 'f' ELSE 't' END , '</SAAD>',
'<SHOW_APPROVAL_BUTTONS_JSD>', CASE WHEN SHOW_APPROVAL_BUTTONS_JSD = 0 THEN 'f' ELSE 't' END , '</SHOW_APPROVAL_BUTTONS_JSD>',
'<SHOW_APPROVAL_STATUS_JSD>', CASE WHEN SHOW_APPROVAL_STATUS_JSD = 0 THEN 'f' ELSE 't' END , '</SHOW_APPROVAL_STATUS_JSD>',
'<SOURCE_STATUS_ID>', COALESCE(SOURCE_STATUS_ID,''), '</SOURCE_STATUS_ID>',
'<SUPER_APPROVERS_EXPR>', COALESCE(SUPER_APPROVERS_EXPR,''), '</SUPER_APPROVERS_EXPR>',
'<TRANSITION_ID>', COALESCE(TRANSITION_ID,''), '</TRANSITION_ID>',
'<TRANSITION_ON_REJECTING>', COALESCE(TRANSITION_ON_REJECTING,''), '</TRANSITION_ON_REJECTING>',
'</row>'
) data_xml
FROM jiraschema.AO_D195E9_APPROVAL_MAPPING;
/*Place all the result of this query between <AO_D195E9_APPROVAL_MAPPING_REL></AO_D195E9_APPROVAL_MAPPING_REL> tags*/
SELECT
CONCAT( '<row>',
'<APPROVAL_ID>', APPROVAL_ID, '</APPROVAL_ID>',
'<APPROVAL_MAPPING_ID>', APPROVAL_MAPPING_ID, '</APPROVAL_MAPPING_ID>',
'<ID>', ID, '</ID>',
'<IS_OPTIONAL_APPROVAL>', CASE WHEN IS_OPTIONAL_APPROVAL = 0 THEN 'f' ELSE 't' END , '</IS_OPTIONAL_APPROVAL>',
'<SORT_SA>', SORT_SA, '</SORT_SA>',
'</row>') data_xml
FROM jiraschema.AO_D195E9_APPROVAL_MAPPING_REL;
/*Place all the result of this query between <AO_D195E9_ISSUE_APPROVAL></AO_D195E9_ISSUE_APPROVAL> tags*/
SELECT
CONCAT(
'<row>',
'<ABSTAIN_ACTION_STATUS>', COALESCE(ABSTAIN_ACTION_STATUS,''), '</ABSTAIN_ACTION_STATUS>',
'<APPROVAL_ID>', COALESCE(APPROVAL_ID,''), '</APPROVAL_ID>',
'<APPROVAL_STATUS>', COALESCE(APPROVAL_STATUS,''), '</APPROVAL_STATUS>',
'<APPROVER_USER>', COALESCE(APPROVER_USER,''), '</APPROVER_USER>',
'<AREA_COND_RULE_ID>', COALESCE(AREA_COND_RULE_ID,''), '</AREA_COND_RULE_ID>',
'<AREA_DATE_PICKER>', COALESCE(AREA_DATE_PICKER,''), '</AREA_DATE_PICKER>',
'<AREA_ELAPSED_TIME>', COALESCE(AREA_ELAPSED_TIME,''), '</AREA_ELAPSED_TIME>',
'<AREA_SERVICE_NAME>', COALESCE(AREA_SERVICE_NAME,''), '</AREA_SERVICE_NAME>',
'<ARN>', CASE WHEN ARN = 0 THEN 'f' ELSE 't' END , '</ARN>',
'<AWT>', CASE WHEN AWT = 0 THEN 'f' ELSE 't' END , '</AWT>',
'<AWTAD>', CASE WHEN AWTAD = 0 THEN 'f' ELSE 't' END , '</AWTAD>',
'<AWTR>', CASE WHEN AWTR = 0 THEN 'f' ELSE 't' END , '</AWTR>',
'<COMMENT_NOTIFICATION_DATE>', COALESCE(COMMENT_NOTIFICATION_DATE,''), '</COMMENT_NOTIFICATION_DATE>',
'<CONDITIONAL_RULE_ID>', COALESCE(CONDITIONAL_RULE_ID,''), '</CONDITIONAL_RULE_ID>',
'<CUSTOM_APPROVER>', COALESCE(CUSTOM_APPROVER,''), '</CUSTOM_APPROVER>',
'<DATE>', COALESCE(DATE,''), '</DATE>',
'<DA_DECISION>', COALESCE(DA_DECISION,''), '</DA_DECISION>',
'<DA_ELAPSED_TIME>', COALESCE(DA_ELAPSED_TIME,''), '</DA_ELAPSED_TIME>',
'<DA_SERVICE_NAME>', COALESCE(DA_SERVICE_NAME,''), '</DA_SERVICE_NAME>',
'<DA_USER_KEY>', COALESCE(DA_USER_KEY,''), '</DA_USER_KEY>',
'<DEFINE_CUSTOM_APPROVAL>', CASE WHEN DEFINE_CUSTOM_APPROVAL = 0 THEN 'f' ELSE 't' END , '</DEFINE_CUSTOM_APPROVAL>',
'<DELEGATOR_KEY>', COALESCE(DELEGATOR_KEY,''), '</DELEGATOR_KEY>',
'<ERROR_TRANSIT>', CASE WHEN ERROR_TRANSIT = 0 THEN 'f' ELSE 't' END , '</ERROR_TRANSIT>',
'<ERROR_TRANSIT_TYPE>', COALESCE(ERROR_TRANSIT_TYPE,''), '</ERROR_TRANSIT_TYPE>',
'<HAS_CONFIG_FOR_SINGLE_ISSUE>', COALESCE(HAS_CONFIG_FOR_SINGLE_ISSUE,''), '</HAS_CONFIG_FOR_SINGLE_ISSUE>',
'<ID>', ID, '</ID>',
'<ISSUE_ID>', COALESCE(ISSUE_ID,''), '</ISSUE_ID>',
'<ISSUE_STATUS>', COALESCE(ISSUE_STATUS,''), '</ISSUE_STATUS>',
'<IS_OPTIONAL_APPROVAL>', CASE WHEN IS_OPTIONAL_APPROVAL = 0 THEN 'f' ELSE 't' END , '</IS_OPTIONAL_APPROVAL>',
'<MAIL_TEMPLATE_REPLY_ID>', COALESCE(MAIL_TEMPLATE_REPLY_ID,''), '</MAIL_TEMPLATE_REPLY_ID>',
'<MAIL_TEMPLATE_REQUEST_ID>', COALESCE(MAIL_TEMPLATE_REQUEST_ID,''), '</MAIL_TEMPLATE_REQUEST_ID>',
'<MRA>', COALESCE(MRA,''), '</MRA>',
'<MRR>', COALESCE(MRR,''), '</MRR>',
'<NAC>', CASE WHEN NAC = 0 THEN 'f' ELSE 't' END , '</NAC>',
'<NOTIFICATION_DATE>', COALESCE(NOTIFICATION_DATE,''), '</NOTIFICATION_DATE>',
'<RAC>', CASE WHEN RAC = 0 THEN 'f' ELSE 't' END , '</RAC>',
'<RACA>', COALESCE(RACA,''), '</RACA>',
'<REPLY_NOTIFICATION_SCHEMA_ID>', COALESCE(REPLY_NOTIFICATION_SCHEMA_ID,''), '</REPLY_NOTIFICATION_SCHEMA_ID>',
'<SA>', CASE WHEN SA = 0 THEN 'f' ELSE 't' END , '</SA>',
'<SAAD>', CASE WHEN SAAD = 0 THEN 'f' ELSE 't' END , '</SAAD>',
'<SHOW_APPROVAL_BUTTONS_JSD>', CASE WHEN SHOW_APPROVAL_BUTTONS_JSD = 0 THEN 'f' ELSE 't' END , '</SHOW_APPROVAL_BUTTONS_JSD>',
'<SHOW_APPROVAL_STATUS_JSD>', CASE WHEN SHOW_APPROVAL_STATUS_JSD = 0 THEN 'f' ELSE 't' END , '</SHOW_APPROVAL_STATUS_JSD>',
'<SORT_SA>', COALESCE(SORT_SA,''), '</SORT_SA>',
'<SOURCE_STATUS_ID>', COALESCE(SOURCE_STATUS_ID,''), '</SOURCE_STATUS_ID>',
'<SUBMITTED_DATE>', COALESCE(SUBMITTED_DATE,''), '</SUBMITTED_DATE>',
'<SUPER_APPROVERS_EXPR>', COALESCE(SUPER_APPROVERS_EXPR,''), '</SUPER_APPROVERS_EXPR>',
'<TRANSITION_ID>', COALESCE(TRANSITION_ID,''), '</TRANSITION_ID>',
'<TRANSITION_NAME>', COALESCE(TRANSITION_NAME,''), '</TRANSITION_NAME>',
'<TRANSITION_ON_REJECTING>', COALESCE(TRANSITION_ON_REJECTING,''), '</TRANSITION_ON_REJECTING>',
'<TYPE>', COALESCE(TYPE,''), '</TYPE>',
'</row>'
) data_xml
FROM jiraschema.AO_D195E9_ISSUE_APPROVAL WHERE TYPE = 'SINGLE_ISSUE';
/*Place all the result of this query between <AO_D195E9_APPROVAL_MRA></AO_D195E9_APPROVAL_MRA> tags*/
SELECT
CONCAT('<row>',
'<APPROVAL_MAPPING_ID>', COALESCE(APPROVAL_MAPPING_ID,''), '</APPROVAL_MAPPING_ID>',
'<CONDITIONAL_RULE_ID>', COALESCE(CONDITIONAL_RULE_ID,''), '</CONDITIONAL_RULE_ID>',
'<ID>', ID, '</ID>',
'<ISSUE_ID>', COALESCE(ISSUE_ID,''), '</ISSUE_ID>',
'<MRA>', COALESCE(MRA,''), '</MRA>',
'<ORDER>', COALESCE([ORDER],''), '</ORDER>',
'<SOURCE_STATUS_ID>', COALESCE(SOURCE_STATUS_ID,''), '</SOURCE_STATUS_ID>',
'<TRANSITION_ID>', COALESCE(TRANSITION_ID,''), '</TRANSITION_ID>',
'<TYPE>', COALESCE([TYPE],''), '</TYPE>',
'</row>') data_xml
FROM jiraschema.AO_D195E9_APPROVAL_MRA;
/*Place all the result of this query between <AO_D195E9_APPROVAL_HISTORY></AO_D195E9_APPROVAL_HISTORY> tags*/
SELECT
CONCAT(
'<row>',
'<ACTION>', a.ACTION, '</ACTION>',
'<APPROVAL_COMMENT>', REPLACE(REPLACE(REPLACE(a.APPROVAL_COMMENT, '&', '&'), '<', '<'), '>', '<'), '</APPROVAL_COMMENT>',
'<APPROVAL_DETAIL>', REPLACE(REPLACE(REPLACE(a.APPROVAL_DETAIL, '&', '&'), '<', '<'), '>', '<'), '</APPROVAL_DETAIL>',
'<APPROVAL_NAME>', REPLACE(REPLACE(REPLACE(a.APPROVAL_NAME, '&', '&'), '<', '<'), '>', '<'), '</APPROVAL_NAME>',
'<COMMENT>', REPLACE(REPLACE(REPLACE(a.COMMENT, '&', '&'), '<', '<'), '>', '<'), '</COMMENT>',
'<DATE>', a.DATE, '</DATE>',
'<DELEGATOR_KEY>', case when a.DELEGATOR_KEY is not null then CONCAT('JIRAUSER', c.ID) else null end , '</DELEGATOR_KEY>',
'<DESTINATION_STATUS>', REPLACE(REPLACE(REPLACE(a.DESTINATION_STATUS, '&', '&'), '<', '<'), '>', '<'), '</DESTINATION_STATUS>',
'<EXECUTOR>', a.EXECUTOR, '</EXECUTOR>',
'<EXECUTOR_KEY>', case when a.EXECUTOR is not null then CONCAT('JIRAUSER', b.ID) else null end, '</EXECUTOR_KEY>',
'<ID>', a.ID, '</ID>',
'<ISSUE_ID>', a.ISSUE_ID, '</ISSUE_ID>',
'<NOTIFICATION_DATE>', a.NOTIFICATION_DATE, '</NOTIFICATION_DATE>',
'<SOURCE_STATUS>', REPLACE(REPLACE(REPLACE(a.SOURCE_STATUS, '&', '&'), '<', '<'), '>', '<'), '</SOURCE_STATUS>',
'<SUBMITTED_DATE>', a.SUBMITTED_DATE, '</SUBMITTED_DATE>',
'<TRANSITION_NAME>', REPLACE(REPLACE(REPLACE(a.TRANSITION_NAME, '&', '&'), '<', '<'), '>', '<'), '</TRANSITION_NAME>',
'</row>'
) AS data_xml FROM
jiraschema.AO_D195E9_APPROVAL_HISTORY a
left join jiraschema.cwd_user b on a.EXECUTOR =b.user_name
left join jiraschema.cwd_user c on a.DELEGATOR_KEY =c.user_name;
/*Place all the result of this query between <AO_D195E9_APPROVAL_DELEGATED></AO_D195E9_APPROVAL_DELEGATED>*/
SELECT
CONCAT(
'<row>',
'<DELEGATED_KEY>', case when a.DELEGATED_KEY is not null then CONCAT('JIRAUSER', b.ID) else null end, '</DELEGATED_KEY>',
'<DELEGATOR_KEY>', case when a.DELEGATOR_KEY is not null then CONCAT('JIRAUSER', c.ID) else null end, '</DELEGATOR_KEY>',
'<END_DATE>', a.END_DATE, '</END_DATE>',
'<ID>', a.ID, '</ID>',
'<ISSUE_TYPE_ID>', a.ISSUE_TYPE_ID, '</ISSUE_TYPE_ID>',
'<PROJECT_ID>', a.PROJECT_ID, '</PROJECT_ID>',
'<SEND_APPROVAL_REQUESTS>', a.SEND_APPROVAL_REQUESTS, '</SEND_APPROVAL_REQUESTS>',
'<START_DATE>', a.START_DATE, '</START_DATE>',
'</row>'
) AS data_xml
FROM
jiraschema.AO_D195E9_APPROVAL_DELEGATED a
left join jiraschema.cwd_user b on a.DELEGATED_KEY =b.user_name
left join jiraschema.cwd_user c on a.DELEGATOR_KEY =c.user_name;
|
