Tables for element storage

The following statements create SQL tables customers, orders, and items, whose columns correspond with the elements of the XML Order documents.

create table customers 
 	(customer_id varchar(5) not null unique, 
 	customer_name varchar(50) not null)

create table orders 
 	(customer_id varchar(5) not null, 
 	order_date datetime not null, 
 	item_id varchar(5) not null, 
 	quantity int not null,  
 	unit smallint default 1)

create table items 
 	(item_id varchar(5) unique, 
 	item_name varchar(20))

These tables need not have been specifically created to accommodate XML Order documents.

The following SQL statements populate the tables with the data in the example XML Order document (see “A sample XML document”):

insert into customers values("123", "Acme Alpha")

insert into orders values ("123", "1999/05/07",
 	"987", 5, 1)

insert into orders values ("123", "1999/05/07",
 	"654", 3, 12)

insert into orders values ("123", "1999/05/07",
 	"579", 1, 1)

insert into items values ("987", "Widget")

insert into items values ("654", "Medium connecter")

insert into items values ("579", "Type 3 clasp")

Use select to retrieve data from the tables:

select order_date as Date, c.customer_id as CustomerId,
 	customer_name as CustomerName,
 	o.item_id as ItemId, i.item_name as ItemName, 
 	quantity as Quantity, o.unit as unit 
 from customers c, orders o, items i
 	where c.customer_id=o.customer_id and 
	o.item_id=i.item_id

Date	CustomerId	CustomerName	ItemId	ItemName	Quantity	Unit
July 4 1999	123	Acme Alpha	987	Coupler	5	1
July 4 1999	123	Acme Alpha	654	Connector	3	12
July 4 1999	123	Acme Alpha	579	Clasp	1	1