SQL Server FOR XML adding in a tag

So I am pulling data from a SQL Server 2000 DB then converting it to XML using FOR XML AUTO.

The XML I get back looks like this.

<Order OrderNumber="2000004" DeliveryPickupCharge="5.9900" SalesTaxTotal="0.0000"      SubTotal="0.0000" Total="5.9900">
  <Customer FirstName="Anthony" LastName="Caporale">
  <Product ProductName="Paper Towels">
  <OrderItem ItemQuantity="1" ItemPrice="8.5900" Total="8.5900" />
</Product>
<Product ProductName="Bathroom Tissue - Giant Roll">
  <OrderItem ItemQuantity="2" ItemPrice="7.1500" Total="14.3000" />
</Product>
<Product ProductName="Napkins - 1-Ply">
  <OrderItem ItemQuantity="1" ItemPrice="3.4900" Total="3.4900" />
</Product>
<Product ProductName="Facial Tissues - 2-Ply - White">
  <OrderItem ItemQuantity="2" ItemPrice="2.0500" Total="4.1000" />
</Product>
<Product ProductName="Anti-Perspirant &amp; Deodorant - Ultra DryCool Rush">
  <OrderItem ItemQuantity="2" ItemPrice="3.5900" Total="7.1800" />
</Product>
<Product ProductName="Flushable Wipes - Fresh Mates Cloths - Refill">
  <OrderItem ItemQuantity="2" ItemPrice="2.9500" Total="5.9000" />
</Product>

Firstly, in 2000 there is no Root('whatever'), so is there any way to add a root node in the T-SQL? Also, is there a way to wrap all the products in a products tag? I don't quite get why the XML returned is in such bad form. I could fix it up a little if I was in SQL 2005. Unfortunately, I am stuck with 2000 at the moment.

Thanks

Solution

You can manipulate the XML to your wish with the FOR XML EXPLICIT mode. I reckon is way way more complex than AUTO (or the 2k5 PATH) but it works fine once you get it going.