/* * 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: xmltables.sql 10 1999-09-13 22:56:17Z jones $' * */ /* * Drop all of the tables in proper order */ DROP TABLE xml_documents; DROP TABLE xml_attributes; DROP TABLE xml_elements; /* * Elements -- table to store XML Elements */ CREATE TABLE xml_elements ( nodeid NUMBER(20), parentnodeid NUMBER(20), nodename VARCHAR2(2000), nodedata VARCHAR2(2000), date_created DATE, date_updated DATE, CONSTRAINT xml_elements_pk PRIMARY KEY (nodeid), CONSTRAINT xml_elements_parent_fk FOREIGN KEY (parentnodeid) REFERENCES xml_elements ); /* * Documents -- table to store XML document catalog */ CREATE TABLE xml_documents ( docid NUMBER(20), rootnodeid NUMBER(20), docname VARCHAR2(1000), doctype 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_elements ); /* * Attributes -- table to store XML Attributes */ CREATE TABLE xml_attributes ( attributeid NUMBER(20), nodeid NUMBER(20), attributenumber NUMBER(20), attributename VARCHAR2(2000), attributevalue VARCHAR2(2000), date_created DATE, date_updated DATE, CONSTRAINT xml_attributes_pk PRIMARY KEY (attributeid), CONSTRAINT xml_attributes_parent_node FOREIGN KEY (nodeid) REFERENCES xml_elements );