CONNECT xml_demo/xml_demo

/*
 ** +---------------------------------------------+
 ** | CREATE TABLE: xml_documents                 |
 ** +---------------------------------------------+
*/

DROP TABLE xml_documents
/

CREATE TABLE xml_documents (
    docname            VARCHAR2(500)
  , xmldoc             CLOB
  , timestamp          DATE
)
LOB (xmldoc)
STORE AS xml_documents_lob (
  TABLESPACE xml_docs
  STORAGE (
    initial 1M next 1M pctincrease 0 maxextents UNLIMITED
  )
  INDEX xml_documents_idx
)
TABLESPACE xml_demod
STORAGE (
  INITIAL      256K
  NEXT         256K
  MINEXTENTS   1
  MAXEXTENTS   121
  PCTINCREASE  0
)
/

ALTER TABLE xml_documents
ADD CONSTRAINT xml_documents_pk PRIMARY KEY(docname)
    USING INDEX
    TABLESPACE xml_demox
    STORAGE (
      INITIAL     64K
      NEXT        64K
      MINEXTENTS  1
      MAXEXTENTS  121
      PCTINCREASE 0
    )
/

ALTER TABLE xml_documents
MODIFY (   xmldoc       CONSTRAINT xml_documents_nn1  NOT NULL
)
/


/*
 ** +---------------------------------------------+
 ** | CREATE TRIGGER: xml_documents_timestamp     |
 ** +---------------------------------------------+
*/

CREATE OR REPLACE TRIGGER xml_documents_timestamp
  BEFORE INSERT OR UPDATE on xml_documents
  FOR EACH ROW
  BEGIN
    :new.timestamp := sysdate;
  END;
/

/*
 ** +---------------------------------------------+
 ** | CREATE DIRECTORY: xmlfiles                  |
 ** +---------------------------------------------+
*/

DROP DIRECTORY "XMLFILES"
/

CREATE DIRECTORY "XMLFILES" AS '/u01/app/oracle/xml/testing'
/

/*
 ** +---------------------------------------------+
 ** | CREATE PROCEDURE: insertXmlFile             |
 ** |   - Inserting and External XML File into    |
 ** |     a CLOB.                                 |
 ** +---------------------------------------------+
*/

CREATE OR REPLACE PROCEDURE insertXmlFile (
    dir   VARCHAR2
  , file  VARCHAR2
  , name  VARCHAR2 := NULL) IS

  theBFile    BFILE;
  theCLob     CLOB;
  theDocName  VARCHAR2(500) := NVL(name, file);

  BEGIN

    -- (1) Insert a new row into xml_documents with an empty CLOB, and
    -- (2) Retrieve the empty CLOB into a variable with RETURNING ... INTO
    INSERT INTO xml_documents (docname, xmldoc)
      VALUES (theDocName, empty_clob())
      RETURNING xmldoc INTO theCLob;

    DBMS_OUTPUT.PUT_LINE('SETTING: theDocName: ' || theDocName);
    DBMS_OUTPUT.PUT_LINE('SETTING: dir: ' || dir);
    DBMS_OUTPUT.PUT_LINE('SETTING: file: ' || file);

    -- (3) Get a BFile handle to the external file
    theBFile := BFileName(dir, file);

    -- (4) Open the file
    DBMS_LOB.fileOpen(theBFile);

    -- (5) Copy the contents of the BFile into the empty CLOB
    DBMS_LOB.loadFromFile(  dest_lob   => theCLob
                          , src_lob    => theBFile
                          , amount     => DBMS_LOB.getLength(theBFile));

    -- (6) Close the file and commit
    DBMS_LOB.fileClose(theBFile);


    COMMIT;

  END;
/
show errors


/*
 ** +---------------------------------------------+
 ** | CREATE PROCEDURE: checkXMLInCLob            |
 ** |   - Checking the Well-Formedness of XML in  |
 ** |     CLOB.                                   |
 ** +---------------------------------------------+
*/

CREATE OR REPLACE PROCEDURE checkXMLInCLOB (
    c               CLOB
  , wellFormed  OUT BOOLEAN
  , error       OUT VARCHAR2) IS

  parser          xmlparser.Parser;
  xmldoc          xmldom.DOMDocument;
  XMLParserError  EXCEPTION;

  PRAGMA EXCEPTION_INIT( XMLParserError, -20100 );

  BEGIN

    parser := xmlparser.newParser;
    xmlparser.ParseCLOB(parser, c);
    xmlparser.freeParser(parser);
    wellFormed := TRUE;

  EXCEPTION

    WHEN XMLParserError THEN
      xmlparser.freeParser(parser);
      wellFormed := FALSE;
      error := SQLERRM;

  END;
/
show errors


/*
 ** +---------------------------------------------+
 ** | CREATE FUNCTION: idAttributeOfDocElement    |
 ** |   - Parsing XML from a String and Using     |
 ** |     DOM.                                    |
 ** +---------------------------------------------+
*/


CREATE OR REPLACE FUNCTION idAttributeOfDocElement (
    xmldoc  VARCHAR2
  )
  RETURN VARCHAR2 IS

  theXmlDoc      xmldom.DOMDocument;
  theDocElt      xmldom.DOMElement;
  retval         VARCHAR2(400);
  XMLParseError  EXCEPTION;

  PRAGMA EXCEPTION_INIT( XMLParseError, -20100 );

  -- Local parse function keeps code cleaner. Return NULL if parse fails.
  FUNCTION parse (xml VARCHAR2) return xmldom.DOMDocument IS

    retDoc  xmldom.DOMDocument;
    parser  xmlparser.Parser;

    BEGIN

      parser := xmlparser.newParser;
      xmlparser.parseBuffer(parser,xml);
      retDoc := xmlparser.getDocument(parser);
      xmlparser.freeParser(parser);
      RETURN retdoc;

    EXCEPTION
      -- if the parse fails, we'll jump here
      WHEN XMLParseError THEN
        xmlparser.freeParser(parser);
        RETURN retdoc;

  END;

  BEGIN

    -- Parse the XML document passed in the VARCHAR2 argument
    theXmlDoc  := parse(xmldoc);

    -- If the XML Document returned is no NULL...
    IF NOT xmldom.IsNull(theXmlDoc) THEN

      -- Get the outermost enclosing element (aka "Document Element")
      theDocElt := xmldom.getDocumentElement(theXmlDoc);
      -- Get the value of the document element's "id" attribute
      retval := xmldom.getAttribute(theDocElt, 'id');
      -- Free the memory used by the parsed XML Document
      xmldom.freeDocument(theXmlDoc);
      RETURN retval;

    ELSE

      RETURN NULL;

    END IF;


  END;
/
show errors


/*
 ** +---------------------------------------------+
 ** | CREATE TABLE: message                       |
 ** |   - Table to store sample (small) XML Docs. |
 ** +---------------------------------------------+
*/

DROP TABLE message
/

CREATE TABLE message (
    recipient    VARCHAR2(80)
  , xml_message  VARCHAR2(4000)
)
/


INSERT INTO message
  VALUES ('Jeff', '<Message id="101"><From>Montgomery Burns</From><Text>Release the Hounds!</Text></Message>')
/

INSERT INTO message
  VALUES ('Melody', '<Message id="102"><From>Montgomery Burns</From><Text>Release the Hounds!</Text></Message>')
/

INSERT INTO message
  VALUES ('Alex', '<Message id="103"><From>Montgomery Burns</From><Text>Release the Hounds!</Text></Message>')
/


COMMIT;