Good article - thanks Hugh.  A shame about the download, but I'm sure you'll get that fixed.

In case anyone's not familiar with the FOR XML clause, here's how you can get XML results equivalent to the results in the article.  As is so often the case, the method you choose will depend on your requirements, and the method in the article is a very useful one to add to the toolbox.

Okay, here we go.  Both of these stored procedure calls...

exec dbo.XmlAuto_EmployeeActivity 6, '1 Jan 1997', '31 Jan 1997'

exec dbo.XmlExplicit_EmployeeActivity 6, '1 Jan 1997', '31 Jan 1997'

...give...

<Employee EmployeeID="6" LastName="Suyama" FirstName="Michael">

  <Customer CustomerID="GOURL" CompanyName="Gourmet Lanchonetes">

    <Order OrderID="10423" OrderDate="1997-01-23T00:00:00">

      <Item ProductID="31" UnitPrice="10.0000" Quantity="14" Discount="0.0000000e+000"/>

      <Item ProductID="59" UnitPrice="44.0000" Quantity="20" Discount="0.0000000e+000"/>

    </Order>

  </Customer>

  <Customer CustomerID="LAMAI" CompanyName="La maison d&apos;Asie">

    <Order OrderID="10425" OrderDate="1997-01-24T00:00:00">

      <Item ProductID="55" UnitPrice="19.2000" Quantity="10" Discount="2.5000000e-001"/>

      <Item ProductID="76" UnitPrice="14.4000" Quantity="20" Discount="2.5000000e-001"/>

    </Order>

  </Customer>

</Employee>

And here's the code for the 2 stored procedures...

create procedure dbo.XmlAuto_EmployeeActivity

  @employeeID  int

, @startDate   smalldatetime

, @endDate     smalldatetime as

select

  Employee.EmployeeID, LastName, FirstName,

  Customer.CustomerID, CompanyName,

  [Order].OrderID, OrderDate,

  ProductID, UnitPrice, Quantity, Discount

from

  Employees as Employee

  inner join Orders as [Order] on Employee.EmployeeID = [Order].EmployeeID

  inner join Customers as Customer on Customer.CustomerID = [Order].CustomerID

  inner join [Order Details] as Item on Item.OrderID = [Order].OrderID

where

  Employee.EmployeeID = @employeeID and orderDate between @startDate and @endDate

for xml auto

---------------------------------------------------------------------------------------------

create procedure dbo.XmlExplicit_EmployeeActivity

  @employeeID  int

, @startDate   smalldatetime

, @endDate     smalldatetime as

--Put relevant orders into a table variable

declare @tblOrders table (OrderID int primary key)

insert @tblOrders

select orderID from orders

where employeeId = @employeeId and orderDate between @startDate and @endDate

--Header

select

  null as 'tag', null as 'parent', --

  null as 'Employee!1!EmployeeID', null as 'Employee!1!LastName', null as 'Employee!1!FirstName', --Employee

  null as 'Customer!2!CustomerID', null as 'Customer!2!CompanyName', --Customer

  null as 'Order!3!OrderID', null as 'Order!3!OrderDate', --Order

  null as 'Item!4!ProductID', null as 'Item!4!UnitPrice', null as 'Item!4!Quantity', null as 'Item!4!Discount' --Item

where 0 = 1

union

--Employee

select 1, null, /**/ EmployeeID, LastName, FirstName, /**/ null, null, /**/ null, null, /**/ null, null, null, null

from Employees where employeeID = @employeeId

union

--Customer

select 2, 1, /**/ @employeeId, null, null, /**/ CustomerID, CompanyName, /**/ null, null, /**/ null, null, null, null

from Customers where customerID in (select customerID from orders where orderID in (select orderID from @tblOrders))

union

--Order

select 3, 2, /**/ @employeeId, null, null, /**/ CustomerID, null, /**/ OrderID, OrderDate, /**/ null, null, null, null

from orders where orderID in (select orderID from orders where orderID in (select orderID from @tblOrders))

union

--Item

select 4, 3, /**/ @employeeId, null, null, /**/ CustomerID, null, /**/ d.OrderID, null, /**/ ProductID, UnitPrice, Quantity, Discount

from [Order Details] d inner join Orders o on o.orderID = d.orderID where d.orderID in (select orderID from @tblOrders)

order by 'Employee!1!EmployeeID', 'Customer!2!CustomerID', 'Order!3!OrderID', 'Item!4!ProductID'

for xml explicit

---------------------------------------------------------------------------------------------

Regards,

Ryan