CREATE OR REPLACE PROCEDURE moveBadXmlDocuments IS wellFormed BOOLEAN; errMessage VARCHAR2(200); BEGIN -- Loop over all documents in the xml_documents table FOR curDoc IN ( SELECT docname,xmldoc FROM xml_documents ) LOOP -- Check the syntax of the current 'xmldoc' CLOB in the loop checkXMLInCLOB( curDoc.xmldoc, wellFormed, errMessage ); IF NOT wellFormed THEN -- Move ill-formed xml document to a bad_xml_documents table INSERT INTO bad_xml_documents(docname,xmldoc,error) VALUES ( curDoc.docname, curDoc.xmldoc, errMessage); DELETE FROM xml_documents WHERE docname = curDoc.docname; COMMIT; END IF; END LOOP; END; |