use xmldb
go

drop procedure DeleteOverPriced
go

-- delete overpriced items
create procedure DeleteOverPriced(@price money, @theid int)
as
update xmlinvoice set 
  invoice::modify('
    (: XQuery program :)
 
  ') 
from xmlinvoice
where id = @theid

-- try it out
exec DeleteOverPriced 9.99, 2

-- check your work
select id, invoice::value('
    (: XQuery program :)
    declare namespace inv="urn:urn:www-SQLskills-com:invoices"
    sum(//inv:Invoice/inv:LineItems/inv:LineItem/inv:Price)
  ',
  -- SQL data type
  'money') as itemsum
 from xmlinvoice
go

-- insert new elements
create procedure NewItem(@theid int, @sku int, @desc varchar(20), @price money)
as
update xmlinvoice set 
  invoice::modify('
    (: XQuery program :)

  ') 
from xmlinvoice
where id = @theid

-- insert new elements
create procedure NewItemAtEnd(@theid int, @sku int, @desc varchar(20), @price money)
as
update xmlinvoice set 
  invoice::modify('
    (: XQuery program :)
 ') 
from xmlinvoice
where id = @theid

exec NewItems 1, 123, 'some desc', 8.88
go

-- check your work
select id, invoice::value('
    (: XQuery program :)
    declare namespace inv="urn:urn:www-SQLskills-com:invoices"
    sum(//inv:Invoice/inv:LineItems/inv:LineItem/inv:Price)
  ',
  -- SQL data type
  'money') as itemsum
 from xmlinvoice
go

drop procedure UpdatePriceForItem
go

create procedure UpdatePriceForItem(@theid int, @sku int, @price money)
as
update xmlinvoice set 
  invoice::modify('
  ') 
from xmlinvoice
where id = @theid

--test it
UpdatePriceForItem 1, 134, 2.00

-- check your work
select id, invoice::value('
    (: XQuery program :)
    declare namespace inv="urn:urn:www-SQLskills-com:invoices"
    sum(//inv:Invoice/inv:LineItems/inv:LineItem/inv:Price)
  ',
  -- SQL data type
  'money') as itemsum
 from xmlinvoice
go

select * from xmlinvoice
