XMLTOCursor Problem

Stan1243 · Aug 21, 2011

Hello Experts,
I am using the following Command to convert a xml to cursor ;
XMLTOCURSOR("d:\test\HCAS0054_fmb_plsql.xml","ora2vfp",512)
There are no error but the result is a cursor with one Blank record.
Any help will be greatly appreciated.
Regards
Stanley
*************************************************************************************************************************************
The following is the Xml file. It looks jumbled up but if you open it in notepad++ or any other xml editor it will make more sense.
************************************************************************************************************************************
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Module xmlns="

http://xmlns.oracle.com/Forms">

<FormModule Name="HCAS0054">
<Block Name="CASE_NOTE">
<Item Name="CASE_NOTE_NO">
<Trigger DirtyInfo="true" DisplayInKeyboardHelp="true" FireInQuery="false" Name="KEY-ENTQRY" TriggerText="go_block('case_note_text'); &#10;clear_block; &#10;go_block('case_note'); &#10;enter_query; &#10;go_block('case_note_text'); &#10;get_case_note_text; &#10;go_block('case_note'); &#10;if :case_note.case_note_no is null then &#10; do_key('execute_query'); &#10;end if;"/>
<Trigger DirtyInfo="true" DisplayInKeyboardHelp="true" FireInQuery="false" KeyboardHelpText="<PRINT>" Name="KEY-PRINT" TriggerText="-- if not HCX employee, prohibit faxing &#10;if :company.m_user_location_no != 65 then &#10; set_field('reprint_request.c_transmit_method_id',enterable,attr_off); &#10; :reprint_request.c_transmit_method_id := 'PAPR'; &#10;end if; &#10;:reprint_request.c_no := :hcx_case.m_case_no; &#10;:reprint_request.c_copy_count := 1; &#10;go_field('reprint_request.c_destination_name');"/>
</Item>
<Item Name="CASE_NOTE_CATEGORY_ID">
<Trigger DirtyInfo="true" DisplayInKeyboardHelp="true" FireInQuery="false" Name="WHEN-VALIDATE-ITEM" TriggerText="if :case_note.case_note_category_id is not null then &#10; begin &#10; select descr, type_id &#10; into :case_note.m_catg_descr, &#10; :case_note.case_note_category_id &#10; from ref_misc_code2 m2 &#10; where m2.misc_code1_type_id = '0004' &#10; and m2.active_ind = 'Y' &#10; and type_id = :case_note.case_note_category_id; &#10; exception when no_data_found then &#10; bell; &#10; message('Invalid Code, Press <LIST>'); &#10; raise form_trigger_failure; &#10; end; &#10;end if;"/>
<Trigger DirtyInfo="true" DisplayInKeyboardHelp="true" FireInQuery="false" Name="KEY-PREV-ITEM" TriggerText="if :system.block_status != 'QUERY' then &#10; go_field('case_note.case_note_text'); &#10;else &#10; previous_field; &#10;end if;"/>
</Item>
<Item Name="CASE_NOTE_TYPE_ID">
<Trigger DirtyInfo="true" DisplayInKeyboardHelp="true" FireInQuery="false" Name="WHEN-VALIDATE-ITEM" TriggerText="if :case_note.case_note_type_id is not null then &#10; begin &#10; select a.descr &#10; into :m_type_descr &#10; from ref_case_note_type a &#10; where a.active_ind = 'Y' &#10; and a.catgry_id = :case_note.case_note_category_id &#10; and a.type_id = :case_note.case_note_type_id; &#10; exception when no_data_found then &#10; bell; &#10; message('Invalid Code, Press <LIST>'); &#10; raise form_trigger_failure; &#10; end; &#10;end if;"/>
</Item>
<Item Name="ACTIVITY_DATE">
<Trigger DirtyInfo="true" DisplayInKeyboardHelp="true" FireInQuery="false" Name="WHEN-VALIDATE-ITEM" TriggerText=":case_note.activity_date := conv_rr_date

case_note.activity_date);"/>
</Item>
<Item Name="ACTIVITY_DURATION">
<Trigger DirtyInfo="true" DisplayInKeyboardHelp="true" FireInQuery="false" Name="KEY-NEXT-ITEM" TriggerText="if :system.record_status = 'QUERY' then &#10; go_field('case_note.created_by'); &#10;else &#10; go_field('case_note.case_note_text'); &#10;end if;"/>
</Item>
<Item Name="CASE_NOTE_TEXT">
<Trigger DirtyInfo="true" DisplayInKeyboardHelp="true" FireInQuery="false" Name="WHEN-NEW-ITEM-INSTANCE" TriggerText="set_field('case_note.case_note_text', echo, attr_on); &#10;Edit_Field(1,10,80,13); &#10;go_block('case_note_text'); &#10;get_case_note_text; &#10;go_field('case_note.case_note_text'); &#10;set_field('case_note.case_note_text', echo, attr_off);"/>
<Trigger DirtyInfo="true" DisplayInKeyboardHelp="true" FireInQuery="false" Name="KEY-EDIT" TriggerText="set_field('case_note.case_note_text', echo, attr_on); &#10;Edit_Field(1,10,80,13); &#10;go_block('case_note_text'); &#10;get_case_note_text; &#10;go_field('case_note.case_note_text'); &#10;set_field('case_note.case_note_text', echo, attr_off);"/>
<Trigger DirtyInfo="true" DisplayInKeyboardHelp="true" FireInQuery="false" Name="KEY-NEXT-ITEM" TriggerText="if :system.block_status != 'QUERY' then &#10; do_key('Commit_Form'); &#10;else &#10; next_field; &#10;end if;"/>
</Item>
<Item Name="CREATED_BY">
<Trigger DirtyInfo="true" DisplayInKeyboardHelp="true" FireInQuery="false" Name="KEY-PREV-ITEM" TriggerText="go_field('case_note.activity_duration');"/>
</Item>
<Trigger DirtyInfo="true" DisplayInKeyboardHelp="true" FireInQuery="false" Name="POST-QUERY" TriggerText="begin &#10; select descr &#10; into :case_note.m_type_descr &#10; from ref_case_note_type &#10; where type_id = :case_note.case_note_type_id &#10; and catgry_id = :case_note.case_note_category_id; &#10;exception when others then &#10; :case_note.m_type_descr := '*** Desc Not Found ***'; &#10;end; &#10;begin &#10; select descr &#10; into :case_note.m_catg_descr &#10; from ref_misc_code2 &#10; where misc_code1_type_id = '0004' &#10; and type_id = :case_note.case_note_category_id; &#10;exception when others then &#10; :case_note.m_catg_descr := '*** Desc Not Found ***'; &#10;end; &#10;get_user_name

case_note.created_by,:case_note.m_create_name); &#10;-- if this is an expanded denial, allow update to category and &#10;-- type in order to correct erroneous note (SUPR/MGMT only) &#10;if (

case_note.case_note_category_id = 'EXPD' and &#10; :case_note.case_note_type_id = 'DENN') or &#10;

case_note.case_note_category_id = 'OPRL' and &#10; :case_note.case_note_type_id = 'OPRN')) and &#10; :global.user_job_id in ('SUPR','MGMT') then &#10; set_field('case_note.case_note_category_id',updateable,attr_on); &#10; set_field('case_note.case_note_type_id',updateable,attr_on); &#10;else &#10; set_field('case_note.case_note_category_id',updateable,attr_off); &#10; set_field('case_note.case_note_type_id',updateable,attr_off); &#10;end if;"/>
<Trigger DirtyInfo="true" DisplayInKeyboardHelp="true" FireInQuery="false" Name="PRE-INSERT" TriggerText=":case_note.case_note_no := null; &#10;begin &#10; select max(case_note_no) &#10; into :case_note.case_note_no &#10; from case_note &#10; where case_no = :case_note.case_no; &#10;exception &#10; when others then &#10; :case_note.case_note_no := null; &#10;end; &#10;if :case_note.case_note_no is null then &#10; :case_note.case_note_no := 1; &#10;else &#10; :case_note.case_note_no := :case_note.case_note_no + 1; &#10;end if; &#10;-- if add CALO/PRO2 note, set indicator for possible update of status &#10;if :case_note.case_note_category_id = 'CALO' and &#10; :case_note.case_note_type_id = 'PRO2' then &#10; if nvl

global.case_status,'NEW') in ('NEW','QCVR','REOP') then &#10; :global.fup_status := 'UTCP'; &#10; end if; &#10;end if;"/>
<Trigger DirtyInfo="true" DisplayInKeyboardHelp="true" FireInQuery="false" Name="KEY-CREREC" TriggerText="go_block('create_note'); &#10;clear_record;"/>
<Trigger DirtyInfo="true" DisplayInKeyboardHelp="true" FireInQuery="false" Name="KEY-DOWN" TriggerText="if :system.cursor_field = 'CASE_NOTE.CASE_NOTE_NO' then &#10; if :system.last_record != 'TRUE' then &#10; down; &#10; go_block('case_note_text'); &#10; get_case_note_text; &#10; go_block('case_note'); &#10; -- if this is an expanded denial, allow update to category and &#10; -- type in order to correct erroneous note (SUPR/MGMT only) &#10; if (

case_note.case_note_category_id = 'EXPD' and &#10; :case_note.case_note_type_id = 'DENN') or &#10;

case_note.case_note_category_id = 'OPRL' and &#10; :case_note.case_note_type_id = 'OPRN')) and &#10; :global.user_job_id in ('SUPR','MGMT') then &#10; set_field('case_note.case_note_category_id',updateable,attr_on); &#10; set_field('case_note.case_note_type_id',updateable,attr_on); &#10; else &#10; set_field('case_note.case_note_category_id',updateable,attr_off); &#10; set_field('case_note.case_note_type_id',updateable,attr_off); &#10; end if; &#10; else &#10; message('At Last Record'); &#10; end if; &#10;else &#10; null; &#10;end if;"/>
<Trigger DirtyInfo="true" DisplayInKeyboardHelp="true" FireInQuery="false" Name="KEY-EXEQRY" TriggerText="go_block('case_note_text'); &#10;clear_block; &#10;go_block('case_note'); &#10;execute_query; &#10;go_block('case_note_text'); &#10;get_case_note_text; &#10;go_block('case_note');"/>
<Trigger DirtyInfo="true" DisplayInKeyboardHelp="true" FireInQuery="false" Name="KEY-EXIT" TriggerText="-- if exit out of create a note, start over, don't exit form &#10;if :system.record_status in ('NEW','INSERT') then &#10; delete_record; &#10; if :global.table_name is not null then &#10; exit_case_notes; &#10; elsif :global.case_note_count > 0 then &#10; do_key('execute_query'); &#10; else &#10; exit_case_notes; &#10; end if; &#10;else &#10; exit_case_notes; &#10;end if;"/>
<Trigger DirtyInfo="true" DisplayInKeyboardHelp="true" FireInQuery="false" Name="KEY-NXTBLK" TriggerText="next_block; &#10;:case_note_text.dummy := :case_note_text.m_case_note_text;"/>
<Trigger DirtyInfo="true" DisplayInKeyboardHelp="true" FireInQuery="false" Name="KEY-UP" TriggerText="if :system.cursor_field = 'CASE_NOTE.CASE_NOTE_NO' then &#10;-- if not at first record &#10; if :case_note.case_note_no != :hcx_case.m_note_count then &#10; up; &#10; go_block('case_note_text'); &#10; get_case_note_text; &#10; go_block('case_note'); &#10; -- if this is an expanded denial, allow update to category and &#10; -- type in order to correct erroneous note (SUPR/MGMT only) &#10; if (

case_note.case_note_category_id = 'EXPD' and &#10; :case_note.case_note_type_id = 'DENN') or &#10;

case_note.case_note_category_id = 'OPRL' and &#10; :case_note.case_note_type_id = 'OPRN')) and &#10; :global.user_job_id in ('SUPR','MGMT') then &#10; set_field('case_note.case_note_category_id',updateable,attr_on); &#10; set_field('case_note.case_note_type_id',updateable,attr_on); &#10; else &#10; set_field('case_note.case_note_category_id',updateable,attr_off); &#10; set_field('case_note.case_note_type_id',updateable,attr_off); &#10; end if; &#10; else &#10; message('At First Record'); &#10; end if; &#10;end if;"/>
</Block>
<Block Name="CASE_NOTE_TEXT">
<Item Name="M_CASE_NOTE_TEXT">
<Trigger DirtyInfo="true" DisplayInKeyboardHelp="true" FireInQuery="false" Name="KEY-NEXT-ITEM" TriggerText="do_key('down');"/>
<Trigger DirtyInfo="true" DisplayInKeyboardHelp="true" FireInQuery="false" Name="KEY-PREV-ITEM" TriggerText="do_key('up');"/>
</Item>
<Trigger DirtyInfo="true" DisplayInKeyboardHelp="true" FireInQuery="false" Name="KEY-CREREC" TriggerText="go_block('create_note'); &#10;clear_record;"/>
<Trigger DirtyInfo="true" DisplayInKeyboardHelp="true" FireInQuery="false" Name="KEY-DOWN" TriggerText="if :case_note_text.dummy != :case_note_text.m_case_note_text then &#10; bell; &#10; :global.present_rec := :system.cursor_record; &#10; get_case_note_text; &#10; go_record

global.present_rec); &#10; message('Cannot Update Case Note Text'); &#10;end if; &#10;if :system.last_record != 'TRUE' then &#10; down; &#10; :case_note_text.dummy := :case_note_text.m_case_note_text; &#10;else &#10; message('At End of Note'); &#10;end if;"/>
<Trigger DirtyInfo="true" DisplayInKeyboardHelp="true" FireInQuery="false" Name="KEY-PRVBLK" TriggerText="if :case_note_text.dummy != :case_note_text.m_case_note_text then &#10; bell; &#10; get_case_note_text; &#10; message('Cannot Update Case Note Text'); &#10;end if; &#10;previous_block; &#10;:case_note_text.dummy := NULL;"/>
<Trigger DirtyInfo="true" DisplayInKeyboardHelp="true" FireInQuery="false" Name="KEY-SCRDOWN" TriggerText="Scroll_down;"/>
<Trigger DirtyInfo="true" DisplayInKeyboardHelp="true" FireInQuery="false" Name="KEY-SCRUP" TriggerText="Scroll_up;"/>
<Trigger DirtyInfo="true" DisplayInKeyboardHelp="true" FireInQuery="false" Name="KEY-UP" TriggerText="if :case_note_text.dummy != :case_note_text.m_case_note_text then &#10; bell; &#10; :global.present_rec := :system.cursor_record; &#10; get_case_note_text; &#10; go_record

global.present_rec); &#10; message('Cannot Update Case Note Text'); &#10;end if; &#10;up; &#10;:case_note_text.dummy := :case_note_text.m_case_note_text;"/>
</Block>

<ProgramUnit Name="CHECK_PACKAGE_FAILURE" ProgramUnitText="-- Procedure to check the status of the form &#10;procedure check_package_failure is &#10;begin &#10; if not form_success then &#10; raise FORM_TRIGGER_FAILURE; &#10; end if; &#10;end;" ProgramUnitType="Procedure"/>
<ProgramUnit Name="CHANGE_CASE" ProgramUnitText="-- Procedure to change Upper/Lower case into init cap &#10;-- IF the name is entered in upper of lower case it is changed into &#10;-- Initial caps. Call change_case

block.field) &#10;-- Possible use in on-validate-field, post-field or key triggers. &#10;-- Author : Dale Hughes. &#10;Procedure change_case(input IN OUT VARCHAR2) is &#10;Begin &#10;IF (input = upper(input)) OR &#10; (input = lower(input)) then &#10; input := initcap(input); &#10; Message('Name has been CHANGED to initial capitalization format'); &#10;END IF; &#10;End;" ProgramUnitType="Procedure"/>
<ProgramUnit Name="ABENDFORM" ProgramUnitText="-- Procedure Abendform is used to display the error report and quit of &#10;-- the process. &#10;Procedure AbendForm IS &#10; query VARCHAR2(2400); &#10; nbr NUMBER; &#10;BEGIN &#10; :global.block_status := :SYSTEM.block_status; &#10; :global.current_block := :SYSTEM.current_block; &#10; :global.current_field := :SYSTEM.current_field; &#10; :global.current_form := :SYSTEM.current_form; &#10; :global.current_value := :SYSTEM.current_value; &#10; :global.cursor_block := :SYSTEM.cursor_block; &#10; :global.cursor_field := :SYSTEM.cursor_field; &#10; :global.cursor_record := :SYSTEM.cursor_record; &#10; :global.cursor_value := :SYSTEM.cursor_value; &#10; :global.form_status := :SYSTEM.form_status; &#10; :global.last_record := :SYSTEM.last_record; &#10; :global.message_level := :SYSTEM.message_level; &#10; :global.record_status := :SYSTEM.record_status; &#10; :global.trigger_block := :SYSTEM.trigger_block; &#10; :global.trigger_field := :SYSTEM.trigger_field; &#10; :global.trigger_record := :SYSTEM.trigger_record; &#10; nbr := ERROR_CODE; &#10; :global.error_code := to_char(nbr); &#10; :global.error_text := ERROR_TEXT; &#10; :global.error_type := ERROR_TYPE; &#10; nbr := MESSAGE_CODE; &#10; :global.message_code := to_char(nbr); &#10; :global.message_text := MESSAGE_TEXT; &#10; :global.message_type := MESSAGE_TYPE; &#10; nbr := SQLCODE; &#10; :global.sqlcode := to_char(nbr); &#10; :global.sqlerrm := SQLERRM; &#10; query := SUBSTR

SYSTEM.LAST_QUERY,1,2400); &#10; :global.last_query := SUBSTR(query,1,240); &#10; query := SUBSTR(query,241); &#10; :global.last_query1 := SUBSTR(query,1,240); &#10; query := SUBSTR(query,241); &#10; :global.last_query2 := SUBSTR(query,1,240); &#10; query := SUBSTR(query,241); &#10; :global.last_query3 := SUBSTR(query,1,240); &#10; query := SUBSTR(query,241); &#10; :global.last_query4 := SUBSTR(query,1,240); &#10; query := SUBSTR(query,241); &#10; :global.last_query5 := SUBSTR(query,1,240); &#10; query := SUBSTR(query,241); &#10; :global.last_query6 := SUBSTR(query,1,240); &#10; query := SUBSTR(query,241); &#10; :global.last_query7 := SUBSTR(query,1,240); &#10; query := SUBSTR(query,241); &#10; :global.last_query8 := SUBSTR(query,1,240); &#10; query := SUBSTR(query,241); &#10; :global.last_query9 := SUBSTR(query,1,240); &#10; CLEAR_FORM(NO_COMMIT); &#10; NEW_FORM('quit_form'); &#10;EXCEPTION &#10; WHEN OTHERS &#10; THEN &#10; CLEAR_FORM(NO_COMMIT); &#10; NEW_FORM('quit_form'); &#10;END;" ProgramUnitType="Procedure"/>
<Trigger DirtyInfo="true" DisplayInKeyboardHelp="false" FireInQuery="false" Name="POST-FORM" TriggerText="-- update global case note count &#10;count_case_notes(to_number

global.case_no),:hcx_case.m_note_count); &#10; :global.case_note_count := :hcx_case.m_note_count;"/>
<Trigger DirtyInfo="true" DisplayInKeyboardHelp="true" FireInQuery="false" Name="KEY-COMMIT" TriggerText="if :system.block_status != 'QUERY' then &#10; if :case_note.case_note_category_id is null then &#10; bell; &#10; go_field('case_note.case_note_category_id'); &#10; message('Case note category has to be entered'); &#10; raise form_trigger_failure; &#10; end if; &#10; if :case_note.case_note_type_id is null then &#10; bell; &#10; go_field('case_note.case_note_type_id'); &#10; message('Case note type has to be entered'); &#10; raise form_trigger_failure; &#10; end if; &#10; if :case_note.case_note_text is null then &#10; bell; &#10; go_field('case_note.case_note_text'); &#10; message('Case note text has to be entered, '|| &#10; ' Press <EDIT> to enter text.'); &#10; raise form_trigger_failure; &#10; end if; &#10; go_block('save_note'); &#10; clear_record; &#10;else &#10; do_key('key_exit'); &#10;end if;"/>

</FormModule>
</Module>

Olaf Doschke · Aug 21, 2011

XMLToCursor is to convert tabular data in XML format to a cursor. No more, no less. The XML you show is an Oracle Form Module, this is not data, this is a form.

This cannot work out, neither with XMLToCursor nor if doing that manually.

A cursor is a table, a table has columns and rows, and the columns of a table can only hold the same type of value, each column has a fieldtype. This general table schema does not fit on this XML o vice versa.

Bye, Olaf.

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

XMLTOCursor Problem

Stan1243

Programmer

Olaf Doschke

Programmer

Similar threads

Part and Inventory Search

Sponsor