Problem with xml select in tsql - can't seem to orgenize multi nodes in the right order

Question

this is my code, i am trying to make a simple 3 nodes select in XML and it is not working, i am getting the parent, the second nodes (all of them) and then the third nodes (all of them) (categories->category(all)->products(all) and not in the right order (categories->category->all products for that category)

    select  1       as  tag,
        null    as  parent,

        null    as  [Categories!1],

        null    as  [Category!2!ID],
        null    as  [Category!2!Name],
        null    as  [Product!3!ID],
        null    as  [Product!3!Name],
        null    as  [Product!3!Price]

union all

select  2       as  tag,
        1       as  parent,

        null,
        CategoryID,
        CategoryName,
        NULL,
        NULL,
        NULL

from    dbo.Categories

union all

select  3       as  tag,
        2       as  parent,

        null,
        null,
        null,
        ProductID,
        ProductName,
        UnitPrice

from    dbo.Products
where   CategoryID=CategoryID

for xml explicit

If any one has an idea what am i doing wrong it would be great. Thank you, Erez

Answer 1

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:

• What's new in FOR XML in Microsoft SQL Server 2005
• Using XML Serialization with SQL's FOR XML PATH
• FOR XML PATH - a new mode in FOR XML in SQL Server 2005

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" />