use wroxdb
go
--
--   Name:  insert_order
--
--   Description: creat a new order, with all child tables, using XML
--
--
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'insert_order')
BEGIN
    DROP PROCEDURE insert_order
END
GO
 
PRINT 'insert_order'
GO
CREATE PROCEDURE insert_order @orderxml varchar(4000) 
AS 
DECLARE @hDoc int
DECLARE @ord  int
DECLARE @ret  int
DECLARE @err  int
DECLARE @rows int

	SET NOCOUNT ON

	EXEC sp_xml_preparedocument @hDoc OUTPUT, @orderxml
	
	BEGIN TRAN

		INSERT orders (order_date, emp_no, customer, deliver, shipping_charge, adm_time, adm_rev_no)
		SELECT x.order_date,
				emp_no = x.emp_no,
				customer = x.customer,
				deliver = x.deliver,
				shipping_charge = x.shipping_charge,
				adm_time=getdate(), 
				adm_rev_no=1
		FROM  OPENXML (@hDoc, '/order', 1) 
		WITH ( xact                         char(1), 
				order_no                       int,
				order_date                     datetime,
				emp_no                         int,
				customer                       varchar(30),
				deliver                        tinyint,
				shipping_charge                money,
				ship_date                      datetime,
				adm_rev_no                     int) AS x
		WHERE xact = 'I'
		SELECT @ord = @@IDENTITY, @err = @@ERROR, @rows = @@ROWCOUNT
		IF @err != 0 GOTO fail
		IF @rows = 0 GOTO fail

		INSERT lines
		SELECT @ord, line_no, prod_id, qty, unit_price, unit_cost, commission
		FROM  OPENXML (@hDoc, '/order/lines/row', 1)
		WITH lines
		IF @@error != 0 GOTO fail

		INSERT payments
		SELECT @ord, pmt_no, pmt_type, amount
		FROM  OPENXML (@hDoc, '/order/payments/row', 1)
		WITH payments
		IF @@error != 0 GOTO fail

		-- now check the sums
		EXEC @ret = validate_order @ord
		IF @ret <> 10 GOTO fail

	COMMIT TRAN
	PRINT 'COMMITTED'
	GOTO quit

fail:
	ROLLBACK TRAN
	PRINT 'ROLLED BACK'

quit:
	PRINT 'Releasing hDoc'
	EXEC sp_xml_removedocument @hDoc   

GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'insert_order')
   PRINT  '>>> Created procedure insert_order <<<'
GO

/* test

DECLARE @xml varchar(4000)

SELECT @xml = "<order xact='I' order_no='-1' order_date='2001-03-27' emp_no='2' customer='Alfred Lord Tennyson' deliver='1' shipping_charge='15.00' adm_rev_no='2' adm_time='12 Mar 2001 13:38:04'>
	<lines>
		<row xact='-' order_no='-1' line_no='1' prod_id='101' descr='Black trousers' qty='1' unit_price='36.99' unit_cost='24.55' commission='0.74'/>
		<row xact='U' order_no='-1' line_no='2' prod_id='201' descr='Men&apos;s trainers' qty='3' unit_price='69.50' unit_cost='48.12' commission='1.39'/>
	</lines>
	<payments>
		<row xact='-' order_no='-1' pmt_no='1' pmt_type='C' descr='Cash' amount='185.99'/>
		<row xact='I' order_no='-1' pmt_no='2' pmt_type='R' descr='Credit Card' amount='74.50'/>
	</payments>
</order>"

EXEC insert_order @xml


SELECT * FROM orders
WHERE order_no= (SELECT max(order_no) FROM orders)

SELECT line_no, prod_id, qty, unit_price, qty*unit_price as total, qty*commission as commission
FROM lines
WHERE order_no= (SELECT max(order_no) FROM orders)
COMPUTE SUM(qty * unit_price)

SELECT * FROM payments
WHERE order_no= (SELECT max(order_no) FROM orders)
COMPUTE SUM(amount)


*/

