SQL Server FOR XML adding in a tag

Question

Heya,

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

Answer 1

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.

Answer 2

I know in 2005 using nested selects allows you to embed multiple results in one core tag (multiple Product in Products) with the ensted seelect also being declared for XML AUTO.

You could also use this technique to wrap everything in a root tag by using one large nested select.