Databases
StackOverflow2013
Postsdbo
POTrouble with correct nesting using SQL Server FOR XML EXPLICIT
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POTrouble with correct nesting using SQL Server FOR XML EXPLICIT
    text
    copied!<p>I am trying to generate an xml file of a recipe database that has recipes with ingredients as sub elements. My query is this:</p> <pre><code>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 </code></pre> <p>which generates the following XML:</p> <pre><code>&lt;item&gt; &lt;title&gt;3-D Cookie Packages&lt;/title&gt; &lt;description&gt;&lt;![CDATA[]]&gt;&lt;/description&gt; &lt;recipe_id&gt;52576&lt;/recipe_id&gt; &lt;ingredients&gt; &lt;ingredient&gt;Assorted candy decorations, if desired&lt;/ingredient&gt; &lt;/ingredients&gt; &lt;ingredients&gt; &lt;ingredient&gt;cup butter or margarine, softened&lt;/ingredient&gt; &lt;/ingredients&gt; &lt;ingredients&gt; &lt;ingredient&gt;cup sugar&lt;/ingredient&gt; &lt;/ingredients&gt; &lt;/item&gt; </code></pre> <p>What I really want is my ingredients to nest like this:</p> <pre><code> &lt;ingredients&gt; &lt;ingredient&gt;Assorted candy decorations, if desired&lt;/ingredient&gt; &lt;ingredient&gt;cup butter or margarine, softened&lt;/ingredient&gt; &lt;ingredient&gt;cup sugar&lt;/ingredient&gt; &lt;/ingredients&gt; </code></pre> <p>I can't use FOR XML PATH because I need the CDATA declaration in the description field, which is not supported using this method. </p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload