

--------------------------------------------------
-- ORDERS table

if exists (select * from sys.tables where [name] = 'Orders') drop table dbo.Orders;
go


create table dbo.Orders (
	OrderID int identity(1,1) primary key, 
	OrderData xml not null
);
go











--------------------------------------------------
-- ORDERS data...

insert dbo.Orders (OrderData)
values (
	'<Order>
		<OrderDatetime>2008-06-15 14:48:43</OrderDatetime>
		<Customer>5343</Customer>
		<Item>
			<Product>4352</Product>
			<Quantity>5</Quantity>
			<Price>14.95</Price>
			<ExtPrice>78.85</ExtPrice>
			<IsGift>false</IsGift>
		</Item>
		<Item>
			<Product>149</Product>
			<Quantity>1</Quantity>
			<Price>3.49</Price>
			<ExtPrice>3.49</ExtPrice>
			<IsGift>false</IsGift>
		</Item>
	 </Order>'
);






--------------------------------------------------
-- USING the data...

select	OrderID, OrderData
from	dbo.Orders;





	-- Sure.
	-- If you're doing an object persistence layer.


	
	
	-- Good luck with the end of month reporting, by the way.








-- More realistic:
select	OrderID, OrderDatetime, CustomerID, sum(ExtPrice) as OrderTotal
from	(	select	OrderID
				,	O.c.value('OrderDatetime[1]', 'datetime') as OrderDatetime
				,	O.c.value('Customer[1]', 'int') as CustomerID
				,	I.c.value('ExtPrice[1]', 'money') as ExtPrice
			from	dbo.Orders
					cross apply OrderData.nodes('/Order') as O(c)
					cross apply OrderData.nodes('/Order/Item') as I(c)
		) as uselessalias
group by OrderID, OrderDatetime, CustomerID
;
	-- And this is a SIMPLE example!







--------------------------------------------------
-- SAME data, but as relational data,
--	with 1-to-many relationship between Orders and Line-Items...

if exists (select * from sys.tables where [name] = 'Orders')
	drop table Orders;
go

if exists (select * from sys.tables where [name] = 'OrderLines')
	drop table OrderLines;
go

create table Orders (
		OrderID int identity(1,1) primary key
	,	OrderDatetime datetime not null
	,	CustomerID int not null		-- (I'm ignoring the Customer table for now.)
);

create table OrderLines (
	OrderID int not null,
	ProductID int not null,		-- (Likewise ignoring the Product table.)
	Quantity int not null,
	Price money not null,
	ExtPrice as Quantity*Price,	-- First integrity problem in the first example ... (not XML-related).
	IsGift bit not null			-- No 'bool' type and bit is as good as int to me. (Sorry Joe.)
);





-- Now insert some data (same XML as above):
declare @orderData xml;
set @orderData = 	'<Order>
		<OrderDatetime>2008-06-15 14:48:43</OrderDatetime>
		<Customer>5343</Customer>
		<Item>
			<Product>4352</Product>
			<Quantity>5</Quantity>
			<Price>14.95</Price>
			<ExtPrice>78.85</ExtPrice>
			<IsGift>false</IsGift>
		</Item>
		<Item>
			<Product>149</Product>
			<Quantity>1</Quantity>
			<Price>3.49</Price>
			<ExtPrice>3.49</ExtPrice>
			<IsGift>false</IsGift>
		</Item>
	 </Order>'
;

/*
-- Normally we'd have something like:
exec dbo.InsertOrder @OrderID = @oid output, @OrderDatetime = ....
*/

declare @orderID int;
insert	dbo.Orders (OrderDatetime, CustomerID)
select	O.c.value('OrderDatetime[1]', 'datetime')
	,	O.c.value('Customer[1]', 'int')
from	@orderData.nodes('/Order') as O(c);

select @orderID = scope_identity();

insert	dbo.OrderLines (OrderID, ProductID, Quantity, Price, IsGift)
select	@orderID
	,	I.c.value('Product[1]', 'int')
	,	I.c.value('Quantity[1]', 'int')
	,	I.c.value('Price[1]', 'money')
	,	I.c.value('IsGift[1]', 'bit')
from	@orderData.nodes('/Order/Item') as I(c);


select * from dbo.Orders;
select * from dbo.OrderLines;

-- Now our earlier query:
select	o.OrderID, o.OrderDatetime, o.CustomerID, sum(l.ExtPrice) as OrderTotal
from	dbo.Orders o
		inner join dbo.OrderLines l on (o.OrderID = l.OrderID)
group by o.OrderID, o.OrderDatetime, o.CustomerID;




/*--------------------------------------------------
		We introduce non-Relational operators to deal with the XML,
	either on its way in or during query-time.
		If we convert on its way in, the data then is 'normalized'
	to be relational. All other normal querying can happen
	the way we are used to. Only the 'data import' routine needs
	knowledge of XML operators.
--------------------------------------------------*/



	