XML question - regarding inserting into tables 1

[RJSHA1]

Hi there,
I working on a project where we get an XML file and we have to put EACH field in the XML schema in a cloumn of a table. All the solutions I have seen invloved created a CLOB parsing the schema into the CLOB and then querying the CLOB.
This is not really what I want - does any one have any hints or tips on how to do this.


Thaks Bob....

 

[HimanB]

Hi,
For this you can simply make use of DBDOM Package.

Here is a sample procedure which is reading the Data into Variabels from an Input XML file & then inserting it into Tables.

Procedure XX(P_XML_IN VARCHAR2) IS
Begin
L_FILE_NAME := P_XML_IN;
    
    L_ERROR_DESC := 'ERROR IN OPENING DOM DOCUMENT ';
    L_ERROR_CODE := '50';
    L_DOM := DBDOM.NEWDOMDOCUMENT;

    L_ERROR_DESC := 'ERROR IN LOADING XML DOCUMENT ';
    L_ERROR_CODE := '60';
    DBDOM.LOADFROMFILE(L_DOM , L_DOWNLOAD_DIR ,L_FILE_NAME);
		
    L_ERROR_DESC := 'ERROR IN SELECTING SINGLE NODE OF RECORD';
    L_ERROR_CODE := '70';
    L_RECORD_NODE := DBDOM.SELECTNODES(DBDOM.DOCUMENTELEMENT(L_DOM),'RECORD');
    
    /** READ THE INPUT XML FILE, READ ONLY THOSE TAGS THAT ARE REQUIRED TO BE INSERTED INTO THE DATABASE **/

    FOR I IN 0..L_RECORD_NODE.COUNT-1
    LOOP
        L_ERROR_CODE := '14';
        L_ERROR_DESC  := 'ERROR IN SELECTING SINGLE NODE ' || 'APSM_DT_SENT';
        L_APSM_DT_SENT := TO_DATE(DBDOM.TEXT(DBDOM.SELECTSINGLENODE(L_RECORD_NODE(I),'APSM_DT_SENT')),'MM/DD/YYYY HH24:MI:SS');

        L_ERROR_CODE := '15';
        L_ERROR_DESC  := 'ERROR IN SELECTING SINGLE NODE ' || 'APSM_SEQ_NO';
        L_APSM_SEQ_NO := DBDOM.TEXT(DBDOM.SELECTSINGLENODE(L_RECORD_NODE(I),'APSM_SEQ_NO'));


        L_ERROR_CODE := '01';
        L_ERROR_DESC  := 'ERROR IN SELECTING SINGLE NODE ' || 'APSM_PROVIDER_NO';
        L_APSM_PROVIDER_NO := DBDOM.TEXT(DBDOM.SELECTSINGLENODE(L_RECORD_NODE(I),'APSM_PROVIDER_NO'));
			
        L_ERROR_CODE := '02';
	  L_ERROR_DESC := 'ERROR IN SELECTING SINGLE NODE ' || 'APSM_MESSAGE_TEXT';
   	  L_APSM_MESSAGE_TEXT := REPLACE(REPLACE(DBDOM.TEXT(DBDOM.SELECTSINGLENODE(L_RECORD_NODE(I),'APSM_MESSAGE_TEXT1')),'"','"'),'&','&');
        L_APSM_MESSAGE_TEXT := L_APSM_MESSAGE_TEXT||REPLACE(REPLACE(DBDOM.TEXT(DBDOM.SELECTSINGLENODE(L_RECORD_NODE(I),'APSM_MESSAGE_TEXT2')),'"','"'),'&','&');
        L_APSM_MESSAGE_TEXT := L_APSM_MESSAGE_TEXT||REPLACE(REPLACE(DBDOM.TEXT(DBDOM.SELECTSINGLENODE(L_RECORD_NODE(I),'APSM_MESSAGE_TEXT3')),'"','"'),'&','&');
   	
    	L_ERROR_CODE := '03';
    	L_ERROR_DESC := 'ERROR IN SELECTING SINGLE NODE ' || 'APSM_TRACKING_AREA';
    	L_APSM_TRACKING_AREA := DBDOM.TEXT(DBDOM.SELECTSINGLENODE(L_RECORD_NODE(I),'APSM_TRACKING_AREA'));
      
        L_ERROR_CODE := '04';
        L_ERROR_DESC := 'ERROR IN SELECTING SINGLE NODE ' || 'APSM_USER_TEXT';
    	L_APSM_USER_TEXT := DBDOM.TEXT(DBDOM.SELECTSINGLENODE(L_RECORD_NODE(I),'APSM_USER_TEXT'));
      
        L_ERROR_CODE := '05';
        L_ERROR_DESC := 'ERROR IN SELECTING SINGLE NODE ' || 'APSM_PROBLEM_TYPE';
    	L_APSM_PROBLEM_TYPE := DBDOM.TEXT(DBDOM.SELECTSINGLENODE(L_RECORD_NODE(I),'APSM_PROBLEM_TYPE'));
      
        L_ERROR_CODE := '06';
        L_ERROR_DESC := 'ERROR IN SELECTING SINGLE NODE ' || 'APSD_TYPE';
    	L_APSD_TYPE  := DBDOM.TEXT(DBDOM.SELECTSINGLENODE(L_RECORD_NODE(I),'APSD_TYPE'));
      
        L_ERROR_CODE := '07';
        L_ERROR_DESC := 'ERROR IN SELECTING SINGLE NODE ' || 'APSD_CD';
    	L_APSD_CD  := DBDOM.TEXT(DBDOM.SELECTSINGLENODE(L_RECORD_NODE(I),'APSD_CD'));
      
        L_ERROR_CODE := '08';
        L_ERROR_DESC := 'ERROR IN SELECTING SINGLE NODE ' || 'APSD_INV_UP_FROM';
    	L_APSD_INV_UP_FROM := TO_DATE(DBDOM.TEXT(DBDOM.SELECTSINGLENODE(L_RECORD_NODE(I),'APSD_INV_UP_FROM')),'MM/DD/YYYY');
      
        L_ERROR_CODE := '09';
        L_ERROR_DESC := 'ERROR IN SELECTING SINGLE NODE ' || 'APSD_INV_UP_TO';
    	L_APSD_INV_UP_TO := TO_DATE(DBDOM.TEXT(DBDOM.SELECTSINGLENODE(L_RECORD_NODE(I),'APSD_INV_UP_TO')),'MM/DD/YYYY');
      
        L_ERROR_CODE := '10';
        L_ERROR_DESC := 'ERROR IN SELECTING SINGLE NODE ' || 'APSD_INV_ITEM_TYPE';
    	L_APSD_INV_ITEM_TYPE := DBDOM.TEXT(DBDOM.SELECTSINGLENODE(L_RECORD_NODE(I),'APSD_INV_ITEM_TYPE'));
      
        L_ERROR_CODE := '11';
        L_ERROR_DESC := 'ERROR IN SELECTING SINGLE NODE ' || 'APSD_INV_QTY';
    	L_APSD_INV_QTY := DBDOM.TEXT(DBDOM.SELECTSINGLENODE(L_RECORD_NODE(I),'APSD_INV_QTY'));
      
        L_ERROR_CODE := '12';
        L_ERROR_DESC := 'ERROR IN SELECTING SINGLE NODE ' || 'APSD_TRN_MATL_NO';
    	L_APSD_TRN_MATL_NO := DBDOM.TEXT(DBDOM.SELECTSINGLENODE(L_RECORD_NODE(I),'APSD_TRN_MATL_NO'));
      
        L_ERROR_CODE := '13';
        L_ERROR_DESC := 'ERROR IN SELECTING SINGLE NODE ' || 'APSD_DCOMM_PHONE_NO';
    	L_APSD_DCOMM_PHONE_NO := DBDOM.TEXT(DBDOM.SELECTSINGLENODE(L_RECORD_NODE(I),'APSD_DCOMM_PHONE_NO'));

     /** INSERT INTO THE DATABASE **/
        INSERT_RECORDS_HELPDESK(L_APSM_DT_SENT, L_APSM_SEQ_NO,
                                L_APSM_PROVIDER_NO,L_APSM_MESSAGE_TEXT,L_APSM_TRACKING_AREA,
                                L_APSM_USER_TEXT,L_APSM_PROBLEM_TYPE,L_APSD_TYPE,L_APSD_CD, 
				L_APSD_INV_UP_FROM,L_APSD_INV_UP_TO,L_APSD_INV_ITEM_TYPE,
                                L_APSD_INV_QTY,L_APSD_TRN_MATL_NO,L_APSD_DCOMM_PHONE_NO);
END LOOP;  
COMMIT;