-- queries against the xmlinvoice table

use xmldb
go

select id, invoice.value('
    (: XQuery program :)
    declare namespace inv="urn:www-SQLskills-com:invoices";
    /inv:Invoice[1]/inv:InvoiceID[1]
  ',
  -- SQL data type
  'int') as invoiceid
 from xmlinvoice
go

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 id, invoice.value('
    (: XQuery program :)
    declare namespace inv="urn:www-SQLskills-com:invoices";
    count(//inv:Invoice/inv:LineItems/inv:LineItem/inv:Price)
  ',
  -- SQL data type
  'money') as itemcount,

  invoice.value('
    (: XQuery program :)
    declare namespace inv="urn:www-SQLskills-com:invoices";
    avg(//inv:Invoice/inv:LineItems/inv:LineItem/inv:Price)
  ',
  -- SQL data type
  'money') as avgprice
 from xmlinvoice
go