使用SQL Server FOR XML EXPLICIT

Question

正确嵌套时遇到问题我正在尝试生成配方数据库的xml文件，该配方数据库的配方中含有成分作为子元素。我的查询是这样的：

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 

产生以下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> 

我真正想要的是我的成分，巢这样的：

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

我不能使用XML PATH，因为我需要在描述字段中使用此方法不支持的CDATA声明。

Answer 1

这应该这样做。添加额外的水平，以举行父母成分节点

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