-- using xml query

use xmldb
go


select invoice.query('
    (: XQuery program :)
    declare namespace inv="urn:www-SQLskills-com:invoices";
	for $invid in /inv:Invoice
    for $invitem in $invid/inv:LineItems/inv:LineItem
    return
      <InvoiceItems>
		<InvoiceID> { data($invid/inv:InvoiceID[1]) } </InvoiceID>		
        <Sku> { data($invitem/inv:Sku[1]) }</Sku>
      </InvoiceItems>
  ') as xmldoc
 from xmlinvoice
go

select invoice.query('
    (: XQuery program :)
    declare namespace inv="urn:www-SQLskills-com:invoices";
    declare namespace pmt="urn:www-develop-com:payments";
    for $invitem in //inv:Invoice
    return
      <pmt:Payment>
        <pmt:InvoiceID> { data($invitem/inv:InvoiceID) } </pmt:InvoiceID>
        <pmt:CustomerName> { data($invitem/inv:CustomerName) }</pmt:CustomerName>
        <pmt:PayAmt> { data(sum($invitem/inv:LineItems/inv:LineItem/inv:Price)) }</pmt:PayAmt>
      </pmt:Payment>
  ') as xmldoc
 from xmlinvoice
go