SET LONG 200000
SET LINESIZE 2000



-- Demonstrate the usage of getXMLfromBFILE() subprogram. This PL/SQL block reads the content of an XML file named
-- 'poOrder.xml' and stores it into the database table named 'xmlDemoTable'.



DECLARE

  -- XML file whose content needs to be read.

  fileName       VARCHAR2(30) := 'poOrder.xml';    

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


  -- BFILE Handle for the XML file. 
  fileOnOS       BFILE;     

  -- XMLType object to hold the the XML content of the file.                       
  xmlFileContent XMLType;                          

BEGIN

  -- Get the BFILE object from the filename and the directory specified.
  -- Note: The below mentioned subprogram call will throw an error if Directory 
  -- Object specified does not exists.
  FileOnOS := bfilename(directoryName , fileName);

  -- Get the content of the file as XMLType
  xmlFileContent := xdb_utilities.getXMLfromBFile(fileOnOS );

  -- Delete any existing data from the table 'xmlDemoTable' and 
  -- insert the XMLType value obtained above in the 'xmlDemoTable' table 

  -- for viewing the file contents later.
  DELETE FROM xmlDemoTable;
  INSERT INTO xmlDemoTable(Name, Content) VALUES( fileName, xmlFileContent );
  COMMIT;
END;
/

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