--***************************************************************************
-- Licensed Materials - Property of IBM
--
-- Governed under the terms of the International
-- License Agreement for Non-Warranted Sample Code.
--
-- (C) COPYRIGHT International Business Machines Corp. 2006
-- All Rights Reserved.
--
-- US Government Users Restricted Rights - Use, duplication or
-- disclosure restricted by GSA ADP Schedule Contract with IBM Corp.
--***************************************************************************


-- sample script for setting up a db2 database for xml on a windows 2000 system
-- to invoke, open a db2 command window and type "db2 -tvf tutorial.sql"

-- this script presumes the db2 "sample" database exists.  
-- if not, uncomment the following line. 
-- create database sample using codeset utf-8 territory us; 

CONNECT TO sample;

-- the following line will fail on the first run because no "clients" table exists.
-- ignore the failure.  or, alternately, comment out this line.  
drop table clients;

create table clients(id int primary key not null, name varchar(50), status varchar(10), contactinfo xml);

-- insert sample data 
insert into clients values (3227, 'Ella Kimpton', 'Gold', 
'<Client> 
	<Address>
		<street>5401 Julio Ave.</street>
		<city>San Jose</city>
		<state>CA</state>
		<zip>95116</zip>
	</Address>
	<phone>
		<work>4084630000</work>
		<home>4081111111</home>
		<cell>4082222222</cell>
	</phone>
	<fax>4087776666</fax>
	<email>love2shop@yahoo.com</email>
</Client>'
);


insert into clients values (8877, 'Chris Bontempo', 'Gold', 
'<Client>
	<Address>
		<street>1204 Meridian Ave.</street>
		<apt>4A</apt>
		<city>San Jose</city>
		<state>CA</state>
		<zip>95124</zip>
	</Address>
	<phone>
		<work>4084440000</work>
	</phone>
	<fax>4085555555</fax>
</Client>'
);


insert into clients values (9077, 'Lisa Hansen', 'Silver', 
'<Client>
	<Address>
		<street>9407 Los Gatos Blvd.</street>
		<city>Los Gatos</city>
		<state>CA</state>
		<zip>95032</zip>
	</Address>
	<phone>
		<home>4083332222</home>
	</phone>
</Client>'
);


insert into clients values (9177, 'Rita Gomez', 'Standard', 
'<Client>
	<Address>
		<street>501 N. First St.</street>
		<city>Campbell</city>
		<state>CA</state>
		<zip>95041</zip>
	</Address>
	<phone>
		<home>4081221331</home>
		<cell>4087799881</cell>
	</phone>
	<email>golfer12@yahoo.com</email>
</Client>'
);


insert into clients values (5681, 'Paula Lipenski', 'Standard', 
'<Client>
	<Address>
		<street>1912 Koch Lane</street>
		<city>San Jose</city>
		<state>CA</state>
		<zip>95125</zip>
	</Address>
	<phone>
		<cell>4085430091</cell>
	</phone>
	<email>beatlesfan36@hotmail.com</email>
	<email>lennonfan36@hotmail.com</email>
</Client>'
); 


insert into clients values (4309, 'Tina Wang', 'Standard', 
'<Client>
	<Address>
		<street>4209 El Camino Real</street>
		<city>Mountain View</city>
		<state>CA</state>
		<zip>95033</zip>
	</Address>
	<phone>
		<home>6503310091</home>
	</phone>
</Client>'
);