SET QUOTED_IDENTIFIER OFF
go

use wroxdb
go
--
--   Name:  update_order
--
--   Description: update an order and all child tables using XML
--
--
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'update_order')
BEGIN
    DROP PROCEDURE update_order
END
GO
 
PRINT 'update_order'
GO
CREATE PROCEDURE update_order @orderxml varchar(4000), @debug tinyint = 0 
AS 
DECLARE @hDoc int
DECLARE @ord  int
DECLARE @ret  int

	SET NOCOUNT ON

	EXEC sp_xml_preparedocument @hDoc OUTPUT, @orderxml
	IF @debug=1
		PRINT 'hDoc ' + convert(varchar,@hDoc)

	SELECT @ord = x.order_no
	FROM  OPENXML (@hDoc, '/order', 1) 
	WITH ( order_no                       int) AS x

	IF @debug=1
		PRINT 'Updating Order ' + convert(varchar, @ord)

	BEGIN TRAN

		UPDATE orders
		SET order_date = 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=x.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 x.order_no = orders.order_no
		AND xact = 'U'
		IF @@error != 0 GOTO fail

		DELETE lines
		WHERE order_no = @ord
		IF @@error != 0 GOTO fail

		INSERT lines
		SELECT order_no, line_no, prod_id, qty, unit_price, unit_cost, commission
		FROM  OPENXML (@hDoc, '/order/lines/row', 1)
		WITH ( xact                         char(1), 
				order_no                       int,
				line_no                        smallint,
				prod_id                        int,
				qty                            int,
				unit_price                     money,
				unit_cost                      money,
				commission                     money) AS x
		WHERE xact <> 'D'
		IF @@error != 0 GOTO fail

		DELETE payments
		WHERE order_no = @ord
		IF @@error != 0 GOTO fail

		INSERT payments
		SELECT order_no, 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
	IF @debug=1
		PRINT 'COMMITTED'
	GOTO quit

fail:
	ROLLBACK TRAN
	IF @debug=1
		PRINT 'ROLLED BACK'

quit:
	IF @debug=1
		PRINT 'Releasing hDoc'
	EXEC sp_xml_removedocument @hDoc   

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

/* test

DECLARE @xml varchar(4000)

SELECT @xml = "<order xact='U' order_no='1000' order_date='2001-03-10' emp_no='2' customer='Alfred Lord Tennyson' deliver='1' shipping_charge='15.00' adm_rev_no='1' adm_time='12 Mar 2001 13:38:04'>
	<lines>
		<row xact='-' order_no='1000' 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='1000' 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='1000' pmt_no='1' pmt_type='C' descr='Cash' amount='185.99'/>
		<row xact='I' order_no='1000' pmt_no='2' pmt_type='R' descr='Credit Card' amount='74.50'/>
	</payments>
</order>"

EXEC update_order @xml, 1

SELECT * FROM orders
WHERE order_no=1000

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

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


** reset data

DELETE payments where order_no=1000
DELETE lines where order_no=1000
DELETE orders where order_no=1000

SET IDENTITY_INSERT orders ON
INSERT ORDERS (order_no, order_date,emp_no,customer,deliver, shipping_charge, adm_rev_no,adm_time)
VALUES (1000, '2001-03-10', 1, 'Mrs Jane Smith', 1, $10, 2, getdate())

SET IDENTITY_INSERT orders OFF

INSERT lines
VALUES(1000, 1, 101, 1, 36.99, 24.55, round(36.99*0.02,2))
INSERT lines
VALUES(1000, 2, 201, 2, 69.5, 48.12, round(69.5*0.02,2))

INSERT payments
VALUES(1000, 1, 'C', 185.99)

*/

