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
Posts
