— A BUNCH OF XML QUERY EXAMPLES

–select invoice.productId , productName, custid, quan, QuantityOnHand,Backordered from invoice join product on invoice.ProductId = product.productId order by ProductName
–for xml auto, root (‘Invoices’)

— all invoices for monkeys
–select * from product join invoice on invoice.productid in (select productid from product as p union select productId from invoice as i)
–for xml auto, root (‘Products’)

–Select InvoiceId, custId, ProductId from invoice for XML Path(‘Invoice’), Root(‘Invoices’)

–***********************************************************************
— Nested XML
— Note the invoice is wrapped by product inside it’s respective product
–***********************************************************************
select ProductId as [@productId], ProductName as [@ProductName] ,
(Select InvoiceId as [@InvoiceId], custId as[@Custid], quan as [@Quanity]
from invoice I
where I.ProductId = PT.productId for XML Path(‘Invoice’), Root(‘Invoices’),Type
) from Product as PT
For xml path(‘Product’), root(‘Products’)
–select productid from product as p union select productId from invoice as i
–select * from invoice

–***************************************
–sales per invoice (XML)
–****************************************
–select invoiceid as [@id],

— i.custid as ‘Customer_Id’,
— salesdate,
— Name,
— p.productId as [SaleInfo/ProductId],
— quan as [SaleInfo/Quantity],
— cost as [SaleInfo/Cost],
— productName as [SaleInfo/ProductName],
— quan * cost as [SaleInfo/TotalSale]
— from invoice as i
— join customer as c
— on i.custid = c.custid
— join product as p
— on i.productId = p.productId
— For xml path(‘invoice’), root(‘invoices’)