--This script creates the example data used for
--my September/October Oracle Magazine article 
--on XQuery. 

--Execute this script from SQL*Plus. 

--Warn readers of the DROPs to come...
PROMPT
PROMPT This script creates example data for an article on XQuery.
PROMPT It will drop and recreate two tables: coc and county_census.
PROMPT The script will also drop and recreate several XML resources
PROMPT in XML DB, in /public. 
PROMPT
PROMPT You should (of course) not run this script in a production schema.
PROMPT
PAUSE Press ENTER to continue, ctrl-C to abort.
PAUSE Are you sure? Press ENTER one, last time; or press ctrl-C to abort.

--Let readers watch
SET ECHO ON

--Some of my data contains ampersands
SET DEFINE OFF

--Drop objects prior to creating, to avoid any issues 
--with duplicate rows.
DROP TABLE chamber_of_commerce;
DROP TABLE county_census;

BEGIN
   IF DBMS_XDB.existsResource('/public/PicturedRocks.xml') 
   THEN
      DBMS_XDB.deleteResource('/public/PicturedRocks.xml',DBMS_XDB.DELETE_FORCE);
   END IF;

   IF DBMS_XDB.existsResource('/public/GrandIsland.xml') 
   THEN
      DBMS_XDB.deleteResource('/public/GrandIsland.xml',DBMS_XDB.DELETE_FORCE);
   END IF;

   IF DBMS_XDB.existsResource('/public/SkiHallFame.xml') 
   THEN
      DBMS_XDB.deleteResource('/public/SkiHallFame.xml',DBMS_XDB.DELETE_FORCE);
   END IF;

   IF DBMS_XDB.existsResource('/public/McCormickWilderness.xml') 
   THEN
     DBMS_XDB.deleteResource('/public/McCormickWilderness.xml',DBMS_XDB.DELETE_FORCE);
   END IF;

   IF DBMS_XDB.existsResource('/public/IsleRoyale.xml') 
   THEN
      DBMS_XDB.deleteResource('/public/IsleRoyale.xml',DBMS_XDB.DELETE_FORCE);
   END IF;

   IF DBMS_XDB.existsResource('/public/SeamanMineralMuseum.xml') 
   THEN
      DBMS_XDB.deleteResource('/public/SeamanMineralMuseum.xml',DBMS_XDB.DELETE_FORCE);
   END IF;
END;
/



--Chamber of Commerce data goes into a table
CREATE TABLE chamber_of_commerce (
   coc_id NUMBER,
   coc_name VARCHAR2(50),
   coc_phone VARCHAR2(15),
   coc_county VARCHAR2(15)
);

INSERT INTO chamber_of_commerce VALUES 
   (1, 'Alger County Chamber of Commerce', '(906) 387-2138', 'Alger');
INSERT INTO chamber_of_commerce VALUES 
   (2, 'Marquette Area Chamber of Commerce', '(906) 226-6591', 'Marquette');
INSERT INTO chamber_of_commerce VALUES
   (3, 'Keweenaw Convention & Visitors Bureau', '(906) 337-4579', 'Houghton');



--Tourist attractions go into XML DB files (called resources)
DECLARE
   res BOOLEAN; 
BEGIN
   res := DBMS_XDB.createResource('/public/PicturedRocks.xml',
      '
       
          Pictured Rocks National Lakeshore
          
             
                Headquarters
                (906) 387-2607
             
             
                Visitor Center
                (906) 387-3700
             
          
          Alger
       
   ');

   res := DBMS_XDB.createResource('/public/GrandIsland.xml',
      '
       
          Grand Island National Recreation Area
          
             
                Munising Ranger District
                (906) 387-2512
             
             
                Passenger Ferry Service
                (906) 387-3503
             
          
          Alger
       
   ');

   res := DBMS_XDB.createResource('/public/SkiHallFame.xml',
      '
       
          National Ski Hall of Fame
          
             
                Voice
                (906) 485-6323
             
          
          Marquette
       
   ');

   res := DBMS_XDB.createResource('/public/McCormickWilderness.xml',
      '
       
          McCormick Wilderness
          
             
                Kenton Ranger District
                (906) 852-3500
              
          
          Marquette
       
   ');

   res := DBMS_XDB.createResource('/public/IsleRoyale.xml',
      '
       
          Isle Royale National Park
          
             
                Voice
                (906) 482-0984
             
             
                Fax
                (906) 487-7170
             
          
          Houghton
       
   ');

   res := DBMS_XDB.createResource('/public/SeamanMineralMuseum.xml',
      '
       
          A. E. Seaman Mineral Museum
          
             
                Museum
                (906) 487-2572
             
          
          Houghton
       
   ');

END;
/



--Census data goes into an XMLType column
CREATE TABLE county_census (
   cc_county VARCHAR2(15),
   cc_city_populations XMLType
);

INSERT INTO county_census VALUES ('Alger',
   '
    
       
          Munising
          2539
       
       
          Chatham
          231
       
    
    ');

INSERT INTO county_census VALUES ('Marquette',
   '
    
       
          Marquette
          19661
       
       
          Ishpeming
          6686
       
       
          Negaunee
          4576
       
    
    ');

INSERT INTO county_census VALUES ('Houghton',
   '
    
       
          Houghton
          7010
       
       
          Hancock
          4323
       
       
          Calumet
          879
       
    
    ');


--Don't leave echo on, because it'll mess up the next run of the script.
SET ECHO OFF