Touble with correct nesting using SQL Server FOR XML EXPLICIT

Question

I am trying to generate an xml file of a recipe database that has recipes with ingredients as sub elements. My query is this:

select 
    1 as 'Tag'
,null as 'parent'
,replace(r.recipe_name, '/', '') as 'item!1!title!element'
,isnull(replace(r.description, '/', ''), '') as 'item!1!description!cdata'
,r.recipe_id as 'item!1!recipe_id!element'
,null as 'ingredients!2!ingredient!element'
from recipe r
union all
select
    2 as 'Tag'
    ,1 as 'parent'
    ,null as 'item!1!title!element'
    ,null as 'item!1!description!cdata'
    ,r.recipe_id as 'item!1!recipe_id!element'
    ,i.full_ingredient_txt as 'ingredients!2!ingredient!element'
from
    recipe r, ingredient i
    where r.recipe_id = i.recipe_id
order by 'item!1!recipe_id!element'
for xml explicit

which generates the following XML:

<item>
  <title>3-D Cookie Packages</title>
  <description><![CDATA[]]></description>
  <recipe_id>52576</recipe_id>
  <ingredients>
    <ingredient>Assorted candy decorations, if desired</ingredient>
  </ingredients>
  <ingredients>
    <ingredient>cup butter or margarine, softened</ingredient>
  </ingredients>
  <ingredients>
    <ingredient>cup sugar</ingredient>
  </ingredients>
</item>

What I really want is my ingredients to nest like this:

    <ingredients>
        <ingredient>Assorted candy decorations, if desired</ingredient>
        <ingredient>cup butter or margarine, softened</ingredient>
        <ingredient>cup sugar</ingredient>
    </ingredients>

I can't use FOR XML PATH because I need the CDATA declaration in the description field, which is not supported using this method.

Can anyone recommend a solution? Thanks.

Answer 1

This should do it. Added additional level to hold the parent ingredients node

select 
1 as Tag
,null as Parent
,replace(r.recipe_name, '/', '') as 'item!1!title!element'
,isnull(replace(r.description, '/', ''), '') as 'item!1!description!cdata'
,r.recipe_id as 'item!1!recipe_id!element'
,null as 'ingredients!2!'
,null as 'ingredient!3!'
from recipe r
union all
  select
    2 as Tag
    ,1 as Parent
    ,null
    ,null
    ,r.recipe_id
    ,''
    ,null
from recipe r
union all
  select
    3 as Tag
    ,2 as Parent
    ,null
    ,null
    ,r.recipe_id
    ,null
    ,i.full_ingredient_txt
from  
    recipe r, ingredient i
    where r.recipe_id = i.recipe_id
order by 'item!1!recipe_id!element'
for xml explicit