return parent and child data using FOR XML in a stored proc

Question

If I have a parent table and a child table where there can be one parent to many children, how can I go about returning the following xml from a stored procedure in a stored procedure?

<Parents>
    <Parent>
       <ID>Integer</ID>
       <Children>
           <Child>
               <ID>Integer</ID>
               <Text>String</Text>
           </Child>
           <Child>
               <ID>Integer</ID>
               <Text>String</Text>
           </Child>
        </Children>
    </Parent>
    <Parent>
       <ID>Integer</ID>
       <Children>
           <Child>
               <ID>Integer</ID>
               <Text>String</Text>
           </Child>
           <Child>
               <ID>Integer</ID>
               <Text>String</Text>
           </Child>
        </Children>
    </Parent>
</Parents>

Answer 1

You can do it using some nesting selects.

select 'a' as "ID",
 (
 select child as "ID"
 from ( select 'integer' as child
   union all
   select 'string' ) a
   for xml path('Child'), type, root('Childrens') 
   ) as "*"
for xml path('Parent'), type, root('Parents')

Opps, I did not saw that it was for Sql-Server-2000.

Answer 2

This is definitely a lot harder with SQL 2000. Below is a sample query that may help you get started with this process. Please understand that it's not exactly in the format you are looking for. The purpose of the query is to help you get started... a nudge in the right direction.

The trick here is to use FOR XML EXPLICIT, and carefully crafting your column aliases to control the positioning of the elements.

Declare @Parent Table(Id Int, Data VarChar(20))
Insert Into @Parent Values(1, 'Fruit')
Insert Into @Parent Values(2, 'Vegetable')

Declare @Child Table(Id Int, ParentId Int, Name VarChar(20))
Insert Into @Child Values(1, 1, 'Apple')
Insert Into @Child Values(2, 1, 'Banana')
Insert Into @Child Values(3, 2, 'Carrot')
Insert Into @Child Values(4, 2, 'Pea')

Select 1 As Tag,
       NULL As Parent,
       Id As [Parent!1!Id!Element],
       Data As [Parent!1!Data!Element],
       NULL As [Child!2!Id!Element],
       NULL As [Child!2!Name!Element]
From   @Parent P

Union

Select  2 As Tag,
        1 As Parent,
        P.Id,
        NULL,
        C.Id,
        Name
From    @Child C
        Inner Join @Parent P
          On C.ParentId = P.Id
Order By [Parent!1!Id!Element]
For XML Explicit