declare @v_CurrentDate datetime
set @v_CurrentDate = '2003-07-17'

if exists (select 1
from Sales.SalesOrderHeader
where OrderDate = @v_CurrentDate)
begin

select top 10 oh.OrderDate,
(select round(sum(TotalDue), 2)
from Sales.SalesOrderHeader
where OrderDate = @v_CurrentDate) as DayTotal,
p.ProductID, p.Name,
round(sum(oh.TotalDue), 2) as ProductSubtotal
from Sales.SalesOrderHeader oh
join Sales.SalesOrderDetail od
on od.SalesOrderID = oh.SalesOrderID
join Production.Product p
on p.ProductID = od.ProductID
where oh.OrderDate = @v_CurrentDate
group
by oh.OrderDate, p.ProductID, p.Name
order
by 5 desc, p.ProductID asc
for xml auto, elements, type, root('Order')

end
else
select cast('<NoRecords>No sales records available for this date.</NoRecords>' as xml)