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 :)
    declare namespace inv="urn:www-SQLskills-com:invoices";
    delete //inv:Invoice/inv:LineItems/inv:LineItem[inv:Price gt sql:variable("@price")]
  ') 
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: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 :)
    declare namespace inv="urn:www-SQLskills-com:invoices";
    insert (
           <inv:LineItem> 
              <inv:Sku> { sql:variable("@sku") }</inv:Sku>
              <inv:Description> { sql:variable("@desc") } </inv:Description>
              <inv:Price> { sql:variable("@price") } </inv:Price>
           </inv:LineItem>
           )
      into /inv:Invoice[1]/inv:LineItems[1]
  ') 
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 :)
    declare namespace inv="urn:www-SQLskills-com:invoices";
    insert (
           <inv:LineItem> 
              <inv:Sku> { sql:variable("@sku") }</inv:Sku>
              <inv:Description> { sql:variable("@desc") } </inv:Description>
              <inv:Price> { sql:variable("@price") } </inv:Price>
           </inv:LineItem>
           )
      as last into /inv:Invoice[1]/inv:LineItems[1]
  ') 
from xmlinvoice
where id = @theid

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

-- check your work
select id, invoice.value('
    (: XQuery program :)
    declare namespace inv="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 float)
as
update xmlinvoice set 
  invoice.modify('
    (: XQuery program :)
    declare namespace inv="urn:www-SQLskills-com:invoices";
    replace value of
       (/inv:Invoice[1]/inv:LineItems[1]/inv:LineItem[inv:Sku[1] eq sql:variable("@sku")]/inv:Price[1])[1]
    with
    sql:variable("@price")
  ') 
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: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
