Handling XML Files in SQR

Nice little example of reading an XML file, {input}, and using it to update values in a Table. Note that it first inserts the XML file's data into a CLOB field at the end of the Table it is updating. Field.RAWXML.

!*******************************************************************************
! Procedure:  Read-Input-File                                                  *
! Description: Loads Request File data into Suspense tables                    *
!              using Oracle XML DB functions to extract data from the XML      *      
!*******************************************************************************
!
begin-procedure Read-Input-File    

#debugp show ''
#debugp show 'Enter procedure Read-Input-File'

  ! Read file in 
  open $Request_Filename as {INPUT} for-reading record=32767:fixed_nolf status=#result
  if #result <> 0
    show {REQUEST_FILE_OPEN_ERR_MSG}
    stop quiet
  end-if

  let $SQL-STATEMENT = 'amssr197.sqr, Read-Input-File, insert into PS_AMS_CART_RQ_HDR' 
  
begin-sql on-error=Sql-Error
INSERT INTO PS_AMS_CART_RQ_HDR (
        PRCSINSTANCE,
        AMS_SYSTEM_CODE,
        AMS_CART_TIME_STMP,
        AMS_ABN_NBR,
        AMS_CART_USER_ID,
        AMS_CART_RECS,
        RAWXML
   ) values ( 
        #prcs_process_instance,
        ' ',
        0,
        0,
        ' ',
        0,
        empty_clob()
    )        
end-sql

  
  while 1=1
    let $xml = ''
    read {INPUT} into $xml:32767 status=#result
    
    if #result <> 0
        show {SUSPENSE_TABLE_ERR_MSG}
        stop quiet
    end-if
    if #end-file
        break
    end-if
    
        let #xml = length($xml)
        
    ! Begin: QC1555
    ! The given XML file may have Microsoft Byte Order Mark at the beginning. If so just trim it off else can't be read.
    ! http://en.wikipedia.org/wiki/UTF-8#Byte_order_mark
    let $checkBOM = substr($xml, 1,4) 
    if $checkBOM = '0xEF' or $checkBOM = '0xBB' or $checkBOM = '0xBF'
        let $xml = substr($xml, 5, #xml - 4)
    end-if 
    let $checkBOM = substr($xml, 1,3) 
    if $checkBOM = '' 
        let $xml = substr($xml, 4, #xml - 3)
    end-if
    let #xml = length($xml)
    ! End: QC1555
    
#ifdef debugx
         show '$checkBOM= ' $checkBOM
        show '    xml : ' $xml
#endif 

    let $SQL-STATEMENT = 'amssr197.sqr, Read-Input-File, update PS_AMS_CART_RQ_HDR(RAWXML)' 
  
begin-sql on-error=Sql-Error
declare
    cartXml CLOB;;
begin
    SELECT rawXML INTO cartXML
    FROM PS_AMS_CART_RQ_HDR 
    WHERE PRCSINSTANCE = #prcs_process_instance
    FOR UPDATE;;
    
    DBMS_LOB.WRITEAPPEND(cartXml, #xml, $xml);;
end;;
end-sql
#ifdef debugx
    commit
#endif

  end-while
  
  let $SQL-STATEMENT = 'amssr197.sqr, Read-Input-File, select PS_AMS_CART_RQ_HDR' 
  
begin-select on-error=Sql-Error
hdr.systemCode
hdr.cartTimeStamp
hdr.instAbn
hdr.userId
hdr.numRecords

   FROM 
   XMLTABLE('/studentStudyCircRequest005'
      PASSING (SELECT XMLTYPE.createxml(rawXML) FROM PS_AMS_CART_RQ_HDR WHERE PRCSINSTANCE = #prcs_process_instance) 
      COLUMNS
        systemCode      varchar2(4)  PATH 'header/system',
        cartTimeStamp   number(14,0) PATH 'header/timestamp',
        instAbn         number(11,0) PATH 'header/instAbn',
        userId          varchar2(12) PATH 'header/userId',
        numRecords      number(38,0) PATH 'trailer/numRecords'
        ) hdr
end-select

    let $SQL-STATEMENT = 'amssr197.sqr, Read-Input-File, update PS_AMS_CART_RQ_HDR' 
  
begin-sql !on-error=Sql-Error
UPDATE PS_AMS_CART_RQ_HDR 
        set AMS_SYSTEM_CODE    = &hdr.systemCode,
            AMS_CART_TIME_STMP = &hdr.cartTimeStamp,
            AMS_ABN_NBR        = &hdr.instAbn,
            AMS_CART_USER_ID   = &hdr.userId,
            AMS_CART_RECS      = &hdr.numRecords
   WHERE PRCSINSTANCE = #prcs_process_instance 
end-sql