In this section we create several tables. These tables are designed to contain data from XML Order documents, so that we can demonstrate the technique for element data storage.
The following SQL 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))
We create these tables to accommodate XML Order documents, but they are ordinary IQ tables.
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 the Order 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 |
