Metacat

set pagesize 100

column entity_name format a10

column entity_type format a5

column source_doctype format a15

column target_doctype format a15

column public_id format a20

column system_id format a20

select entity_type,source_doctype,target_doctype,public_id,system_id

from xml_catalog;

INSERT INTO xml_catalog (entity_type, source_doctype, target_doctype,

     public_id, system_id)

     VALUES ('XSL', '-//NCEAS//eml-dataset//EN', '-//W3C//HTML//EN',

     '-//NCEAS//eml-dataset-display.xsl',

     'file:///home/httpd/html/xmltodb/xsqltest/eml-dataset-display.xsl');

INSERT INTO xml_catalog (entity_type, source_doctype, target_doctype,

     public_id, system_id)

     VALUES ('XSL', '-//NCEAS//eml-variable//EN', '-//W3C//HTML//EN',

     '-//NCEAS//eml-variable-display.xsl',

     'file:///home/httpd/html/xmltodb/xsqltest/eml-variable-display.xsl');

INSERT INTO xml_catalog (entity_type, source_doctype, target_doctype,

     public_id, system_id)

     VALUES ('XSL', '-//NCEAS//eml-file//EN', '-//W3C//HTML//EN',

     '-//NCEAS//eml-file-display.xsl',

     'file:///home/httpd/html/xmltodb/xsqltest/eml-file-display.xsl');

set pagesize 100

column docid format 999

column docname format a15

column doctype format a25

column doctitle format a20

select docid,docname,doctype,rootnodeid,doctitle from xml_documents;

column nodedata format a60

select nodeid,nodedata from xml_nodes where nodedata like '&qstr';

/*

 * xmltables.sql -- Create or replace tables for storing XML in the db

 *

 *      Purpose: creates tables needed for XML database

 * 

 *      Created: 12 September 1999

 *       Author: Matt Jones

 * Organization: National Center for Ecological Analysis and Synthesis

 *      Version: '$Id$'

 *

 */

/*

 * Drop all of the objects in proper order

 */

set echo off

DROP SEQUENCE xml_nodes_id_seq;

DROP SEQUENCE xml_entity_id_seq;

DROP SEQUENCE xml_documents_id_seq;

DROP TRIGGER xml_nodes_before_insert;

DROP TRIGGER xml_documents_before_insert;

DROP TRIGGER xml_catalog_before_insert;

DROP TABLE xml_catalog;

DROP TABLE xml_documents;

DROP TABLE xml_nodes;

/* 

 * Nodes -- table to store XML Nodes (both elements and attributes)

 */

CREATE TABLE xml_nodes (

	nodeid		NUMBER(20),

	parentnodeid	NUMBER(20),

	nodeindex	NUMBER(10),

	nodetype	VARCHAR2(20),

	nodename	VARCHAR2(100),

	nodedata	VARCHAR2(4000),

	date_created	DATE,

	date_updated	DATE,

   CONSTRAINT xml_nodes_pk PRIMARY KEY (nodeid),

   CONSTRAINT xml_nodes_parent_fk 

		FOREIGN KEY (parentnodeid) REFERENCES xml_nodes

);

CREATE SEQUENCE xml_nodes_id_seq;

CREATE TRIGGER xml_nodes_before_insert

BEFORE INSERT ON xml_nodes FOR EACH ROW

BEGIN

  SELECT xml_nodes_id_seq.nextval

    INTO :new.nodeid

    FROM dual;

END;

/

/* 

 * Documents -- table to store XML documents

 */

CREATE TABLE xml_documents (

	docid		NUMBER(20),

	rootnodeid	NUMBER(20),

	docname		VARCHAR2(100),

	doctype		VARCHAR2(100),

	doctitle	VARCHAR2(1000),

	date_created	DATE,

	date_updated	DATE,

   CONSTRAINT xml_documents_pk PRIMARY KEY (docid),

   CONSTRAINT xml_documents_root_fk 

		FOREIGN KEY (rootnodeid) REFERENCES xml_nodes

);

CREATE SEQUENCE xml_documents_id_seq;

CREATE TRIGGER xml_documents_before_insert

BEFORE INSERT ON xml_documents FOR EACH ROW

BEGIN

  SELECT xml_documents_id_seq.nextval

    INTO :new.docid

    FROM dual;

END;

/

/* 

 * XML Catalog -- table to store all external sources for XML documents

 */

CREATE TABLE xml_catalog (

	entity_id	NUMBER(20),

	entity_name	VARCHAR2(100),

	entity_type	VARCHAR2(20),

	source_doctype	VARCHAR2(100),

	target_doctype	VARCHAR2(100),

	public_id	VARCHAR2(100),

	system_id	VARCHAR2(1000),

	date_created	DATE,

	date_updated	DATE,

   CONSTRAINT xml_catalog_pk PRIMARY KEY (entity_id),

   CONSTRAINT xml_catalog_uk UNIQUE (entity_type, source_doctype, target_doctype, public_id)

);

CREATE SEQUENCE xml_entity_id_seq;

CREATE TRIGGER xml_catalog_before_insert

BEFORE INSERT ON xml_catalog FOR EACH ROW

BEGIN

  SELECT xml_entity_id_seq.nextval

    INTO :new.entity_id

    FROM dual;

END;

/