Handling XML

This is how you import the XML from file system into oracle table as a xmltype field.

INSERT INTO pc_xmltable (my_id, my_xml)
VALUES (2, xmltype (BFILENAME ('ECHELON_DIR', 'xml.txt'), NLS_CHARSET_ID ('AL32UTF8')));

These are some examples as to how you can extract records from the XML

SELECT xt.*
FROM pc_xmltable pc,
XMLTABLE ('/PaymentOutput/DocumentInfo' PASSING pc.my_xml COLUMNS filecreationdate VARCHAR2 (20) PATH 'FileCreationDate') xt
WHERE pc.my_id = 2;

SELECT xt.*
FROM pc_xmltable pc,
XMLTABLE (
'/PaymentOutput/EOBDetails/SBClaimPredLines'
PASSING pc.my_xml
COLUMNS claimlinesequencenumber NUMBER (10) PATH 'ClaimLineSequenceNumber',
submittedclaimline VARCHAR2 (20) PATH 'SubmittedClaimLine',
certificatenumber VARCHAR2 (20) PATH 'CertificateNumber',
dateofservice DATE PATH 'DateOfService',
totalsubmittedamount NUMBER (14, 4) PATH 'TotalSubmittedAmount',
totalamountpaid NUMBER (14, 4) PATH 'TotalAmountPaid') xt
WHERE pc.my_id = 2;