SQL Server 2005 and up have a very powerful new command - FOR XML PATH
- which is a lot easier to use than the FOR XML EXPLICIT of olden days.
I don't know what exactly you want, but you could do something like:
SELECT
cat.CategoryID AS '@CategoryID',
cat.CategoryName AS 'Category/Name',
pr.ProductID AS '@ProductID',
pr.ProductName 'Product/Name',
pr.UnitPrice
FROM
dbo.Categories cat
INNER JOIN
dbo.Products pr ON cat.CategoryID = pr.CategoryID
FOR XML PATH('ProductCategory'), ROOT('Root')
This should give you something like:
<Root>
<ProductCategory CategoryID="5" ProductID="66">
<Category>
<Name>YourCategory Nr. 5</Name>
</Category>
<Product>
<Name>Your Product Nr. 66</Name>
</Product>
<UnitPrice>50.50</UnitPrice>
</ProductCategory>
</Root>
See some of those resources for more information on FOR XML PATH
:
Marc
UPDATE: ok, now that we know what you really want, I can provide the right answer :-)
SELECT
cat.CategoryID AS '@ID',
cat.CategoryName AS '@Name',
(SELECT
pr.ProductID AS '@ID',
pr.ProductName AS '@Name',
pr.UnitPrice AS '@Price'
FROM
dbo.T_Product pr
WHERE
cat.CategoryID = pr.CategoryID
FOR XML PATH('product'), TYPE
)
FROM
dbo.Categories cat
FOR XML PATH('category'), ROOT('Categories')
That gives me the output (from Northwind
):
<Categories>
<category ID="1" Name="Beverages">
<product ID="1" Name="Chai" Price="18.0000" />
<product ID="2" Name="Chang" Price="19.0000" />
<product ID="24" Name="Guaraná Fantástica" Price="4.5000" />
<product ID="34" Name="Sasquatch Ale" Price="14.0000" />
<product ID="35" Name="Steeleye Stout" Price="18.0000" />
<product ID="38" Name="Côte de Blaye" Price="263.5000" />
<product ID="39" Name="Chartreuse verte" Price="18.0000" />
<product ID="43" Name="Ipoh Coffee" Price="46.0000" />
<product ID="67" Name="Laughing Lumberjack Lager" Price="14.0000" />
<product ID="70" Name="Outback Lager" Price="15.0000" />
<product ID="75" Name="Rhönbräu Klosterbier" Price="7.7500" />
<product ID="76" Name="Lakkalikööri" Price="18.0000" />
</category>
<category ID="2" Name="Condiments">
<product ID="3" Name="Aniseed Syrup" Price="10.0000" />
<product ID="4" Name="Chef Anton's Cajun Seasoning" Price="22.0000" />
<product ID="5" Name="Chef Anton's Gumbo Mix" Price="21.3500" />
<product ID="6" Name="Grandma's Boysenberry Spread" Price="25.0000" />
<product ID="8" Name="Northwoods Cranberry Sauce" Price="40.0000" />
<product ID="15" Name="Genen Shouyu" Price="15.5000" />
<product ID="44" Name="Gula Malacca" Price="19.4500" />