/* Formatted on 2001/06/13 10:13 (RevealNet Formatter v4.4.0) */
CREATE OR REPLACE PROCEDURE loaderrata (xmlfile VARCHAR2)
-- Demonstration of use of Xpath syntax to extract specific values
-- from the infoset and move them into an Oracle table.
IS
   doc      xmldom.domdocument;
   l_isbn   book.isbn%TYPE;

   PROCEDURE parse_document (
      file_in   IN       VARCHAR2,
      doc_out   OUT      xmldom.domdocument
   )
   IS
      prsr   xmlparser.parser;
   BEGIN
      prsr := xmlparser.newparser;
      xmlparser.parse (prsr, file_in);
      doc_out := xmlparser.getdocument (prsr);
      xmlparser.freeparser (prsr);
   END parse_document;

   FUNCTION selected_nodes (
      doc_in     IN   xmldom.domdocument,
      xpath_in   IN   VARCHAR2
   )
      RETURN xmldom.domnodelist
   IS
      retval   xmldom.domnodelist;
   BEGIN
      retval :=
            xslprocessor.selectnodes (
               xmldom.makenode (doc_in),
               xpath_in
            );
      RETURN retval;
   END;

   PROCEDURE load_book (
      doc_in     IN       xmldom.domdocument,
      isbn_out   OUT      book.isbn%TYPE
   )
   IS
      book_info   xmldom.domnodelist;
      l_node      xmldom.domnode;
      book_rec    book%ROWTYPE;
   BEGIN
      book_info := selected_nodes (doc_in, '/errata/book');
      l_node := xmldom.item (book_info, 0);
      book_rec.title :=
                     xslprocessor.valueof (l_node, '@title');
      book_rec.author :=
                    xslprocessor.valueof (l_node, '@author');
      book_rec.isbn :=
                      xslprocessor.valueof (l_node, '@isbn');

      INSERT INTO book
                  (title, author,
                   isbn)
           VALUES (book_rec.title, book_rec.author,
                   book_rec.isbn);

      isbn_out := book_rec.isbn;
   END;

   PROCEDURE extract_errata (
      erratas_in       IN       xmldom.domnodelist,
      index_in         IN       PLS_INTEGER,
      errata_rec_out   OUT      errata%ROWTYPE
   )
   IS
      l_node   xmldom.domnode;
   BEGIN
      l_node := xmldom.item (erratas_in, index_in);
      errata_rec_out.reported_by :=
                xslprocessor.valueof (l_node, 'reported_by');
      errata_rec_out.pagenumber :=
                 xslprocessor.valueof (l_node, 'pagenumber');
      errata_rec_out.problem :=
                    xslprocessor.valueof (l_node, 'problem');
      errata_rec_out.solution :=
                   xslprocessor.valueof (l_node, 'solution');
   END;

   PROCEDURE load_errata (
      doc_in    IN   xmldom.domdocument,
      isbn_in   IN   book.isbn%TYPE
   )
   IS
      erratas      xmldom.domnodelist;
      errata_rec   errata%ROWTYPE;
   BEGIN
      erratas :=
               selected_nodes (doc_in, '/errata/book/entry');

      FOR entry_index IN 0 ..   xmldom.getlength (erratas)
                              - 1
      LOOP
         extract_errata (erratas, entry_index, errata_rec);

         INSERT INTO errata
                     (isbn, reported_by,
                      reported_on, pagenumber,
                      problem,
                      solution)
              VALUES (isbn_in, errata_rec.reported_by,
                      SYSDATE, errata_rec.pagenumber,
                      errata_rec.problem,
                      errata_rec.solution);
      END LOOP;
   END;

   PROCEDURE cleanup (doc IN xmldom.domdocument)
   IS
   BEGIN
      COMMIT;
      xmldom.freedocument (doc);
   END;
BEGIN
   parse_document (xmlfile, doc);
   load_book (doc, l_isbn);
   load_errata (doc, l_isbn);
   cleanup (doc);
EXCEPTION
   WHEN xmldom.index_size_err
   THEN
      raise_application_error (-20120, 'INDEX SIZE error');
   WHEN xmldom.domstring_size_err
   THEN
      raise_application_error (-20120, 'String SIZE error');
   WHEN xmldom.hierarchy_request_err
   THEN
      raise_application_error (
         -20120,
         'Hierarchy request error'
      );
   WHEN xmldom.wrong_document_err
   THEN
      raise_application_error (-20120, 'Wrong doc error');
   WHEN xmldom.invalid_character_err
   THEN
      raise_application_error (-20120, 'Invalid CHAR error');
   WHEN xmldom.no_data_allowed_err
   THEN
      raise_application_error (
         -20120,
         'Nod data allowed error'
      );
   WHEN xmldom.no_modification_allowed_err
   THEN
      raise_application_error (
         -20120,
         'No MOD allowed error'
      );
   WHEN xmldom.not_found_err
   THEN
      raise_application_error (-20120, 'NOT FOUND error');
   WHEN xmldom.not_supported_err
   THEN
      raise_application_error (
         -20120,
         'NOT supported error'
      );
   WHEN xmldom.inuse_attribute_err
   THEN
      raise_application_error (-20120, 'IN USE attr error');
END loaderrata;
/