SET LONG 200000

SET LINESIZE 2000

-- Demonstrate the usage of getXMLFromFile() subprogram. Following two PL/SQL Blocks demonstrate 

-- the scenario where we have to first register an XML Schema and then load multiple instances 
-- of XML documents conforming to this schema into the database.

-- Following PL/SQL Block demonstrates the usage of getXMLfromFile() subprogram to register the 

-- 'account.xsd' schema. 


DECLARE


  fileName VARCHAR2(30)      := 'account.xsd';
  directoryName VARCHAR2(30) := 'SOURCE_DIR'; -- Directory Object specifying the file location

BEGIN
  -- In this case as only single instance of XML file is needed to be read hence that getXMLfromFile()

  -- subprogram is called where the CLOB associated with the file is not available. It internally 
  -- creates the temporary CLOB for getting the content.
  dbms_xmlschema.registerSchema('/account.xsd', 
                                xdb_utilities.getXMLfromFile(filename, directoryName));
END;
/

-- Following PL/SQL Block demonstrates the usage of getXMLfromFile() subprogram to read multiple
-- instances of XML files and load them into the database using single temporary CLOB. Here we 
-- call the overloaded getXMLfromFile() function which takes a temporary CLOB as input. As creating
-- a CLOB is an expensive operation, it is only created once and passed every time to the function

DECLARE

  tempCLOB CLOB := NULL;
  xmlFileContent XMLType;
  freeCLOBOnExit BOOLEAN;


  -- Directory Object specifying the file location.
  directoryName VARCHAR2(30) := 'SOURCE_DIR'; 

  TYPE obj_array_t IS VARRAY(3) OF VARCHAR2(200);
  -- Load this array with XML files to be loaded.
  obj_array obj_array_t := obj_array_t ('acct1.xml','acct2.xml','acct3.xml') ;

BEGIN
  freeCLOBOnExit := tempCLOB IS NULL;

  IF (tempCLOB IS NULL) THEN
    -- Create a temporary CLOB to hold the BFILE content.
    DBMS_LOB.createTemporary(tempCLOB, TRUE, DBMS_LOB.SESSION);
  ELSE 
    -- Decrease the length of the CLOB to the value specified in the newlen parameter i.e. to 0
    DBMS_LOB.trim(tempCLOB, 0);
  END IF;

   

  --Delete existing records in the demo table
  DELETE FROM xmlDemoTable;

  -- Since multiple instances of XML files need to be read and loaded, its an expensive
  -- operation to create Temporary CLOB for each of the instances. For such
  -- situations, a temporary CLOB should be created once and the same should be used for
  -- getting the content from all the XML files and after getting the content, the temporary 
  -- CLOB should be freed.
  FOR i IN 1..3 LOOP -- Here, for demonstration purpose, we have assumed that there are only 3 input documents 

    -- Call the function with appropriate parameters to get the XML content

    xmlFileContent:= xdb_utilities.getXMLFromFile(obj_array(i), directoryName, tempCLOB=>tempCLOB);

    -- Insert the XMLType value obtained above in the 'xmlDemoTable' table 
    -- for viewing the file contents later.
    INSERT INTO xmlDemoTable(Name, Content) VALUES( obj_array(i), xmlFileContent ); 
    COMMIT;
  END LOOP;

  --Free the temporary CLOB.
  IF (freeCLOBOnExit) THEN
    -- Free the temporary CLOB.
    dbms_lob.freeTemporary(tempCLOB);
  ELSE

    dbms_lob.trim(tempCLOB, 0);
  END IF;
END;
/


-- View the XML contents.
SELECT name, content FROM xmlDemoTable;