1 |
2
|
jones
|
/*
|
2 |
|
|
* xmltables.sql -- Create or replace tables for storing XML in the db
|
3 |
|
|
*
|
4 |
|
|
* Purpose: creates tables needed for XML database
|
5 |
|
|
*
|
6 |
|
|
* Created: 12 September 1999
|
7 |
|
|
* Author: Matt Jones
|
8 |
|
|
* Organization: National Center for Ecological Analysis and Synthesis
|
9 |
|
|
* Version: '$Id$'
|
10 |
|
|
*
|
11 |
|
|
*/
|
12 |
|
|
|
13 |
|
|
/*
|
14 |
3
|
jones
|
* Documents -- table to store XML document catalog
|
15 |
2
|
jones
|
*/
|
16 |
3
|
jones
|
DROP TABLE documents;
|
17 |
|
|
CREATE TABLE documents (
|
18 |
2
|
jones
|
docid NUMBER(20),
|
19 |
3
|
jones
|
rootnodeid NUMBER(20),
|
20 |
2
|
jones
|
docname VARCHAR2(1000),
|
21 |
3
|
jones
|
doctype VARCHAR2(1000),
|
22 |
2
|
jones
|
date_created DATE,
|
23 |
|
|
date_updated DATE,
|
24 |
3
|
jones
|
PRIMARY KEY document_pk (docid),
|
25 |
|
|
FOREIGN KEY fk_root_node (rootnodeid) REFERENCES elements
|
26 |
2
|
jones
|
);
|
27 |
3
|
jones
|
|
28 |
|
|
/*
|
29 |
|
|
* Elements -- table to store XML Elements
|
30 |
|
|
*/
|
31 |
|
|
DROP TABLE elements;
|
32 |
|
|
CREATE TABLE elements (
|
33 |
|
|
nodeid NUMBER(20),
|
34 |
|
|
nodename VARCHAR2(2000),
|
35 |
|
|
nodedata VARCHAR2(2000),
|
36 |
|
|
date_created DATE,
|
37 |
|
|
date_updated DATE,
|
38 |
|
|
PRIMARY KEY elements_pk (nodeid)
|
39 |
|
|
);
|
40 |
|
|
|
41 |
|
|
/*
|
42 |
|
|
* Attributes -- table to store XML Attributes
|
43 |
|
|
*/
|
44 |
|
|
DROP TABLE attributes;
|
45 |
|
|
CREATE TABLE attributes (
|
46 |
|
|
attributeid NUMBER(20),
|
47 |
|
|
nodeid NUMBER(20),
|
48 |
|
|
attributenumber NUMBER(20),
|
49 |
|
|
attributename VARCHAR2(2000),
|
50 |
|
|
attributevalue VARCHAR2(2000),
|
51 |
|
|
date_created DATE,
|
52 |
|
|
date_updated DATE,
|
53 |
|
|
PRIMARY KEY attributes_pk (attributeid),
|
54 |
|
|
FOREIGN KEY fk_parent_node (nodeid) REFERENCES elements
|
55 |
|
|
);
|