Convert Tables into XML using T-SQL

Question

If found several questions about how to convert a table (or query) into XML, but none that showed how to start with one main table and join several one:many satellite tables, and from that generate XML that represents the hierarchical structure of the data. So I thought I'd share this solution now that I've figured it out. If someone else has another way of doing this, please post another answer.

Given this contrived data:

create table #recipe (id int, name varchar(10))
create table #ingredient (recipe_id int, name varchar(30), quantity varchar(20), sort int)
create table #instruction (recipe_id int, task varchar(32), sort int)

insert into #recipe values (1, 'pizza'), (2, 'omelet')
insert into #ingredient values (1, 'pizza dough', '1 package', 1),
                               (1, 'tomato sauce', '1 can', 2),
                               (1, 'favorite toppings', 'you choose', 3),
                               (2, 'eggs', 'three', 1),
                               (2, 'a bunch of other ingredients', 'you choose', 2)
insert into #instruction values (1, 'pre-bake pizza dough', 1),
                                (1, 'add tomato sauce', 2),
                                (1, 'add toppings', 3),
                                (1, 'bake a little longer', 4),
                                (2, 'break eggs into mixing bowl', 1),
                                (2, 'beat yolks and whites together', 2),
                                (2, 'pour into large sauce pan', 3),
                                (2, 'add other ingredients', 4),
                                (2, 'fold in half', 5),
                                (2, 'cook until done', 6)

.

Which looks like this in tabular form:

`#recipe`

id          name
----------- ----------
1           pizza
2           omelet

.

`#ingredient`

recipe_id   name                           quantity             sort
----------- ------------------------------ -------------------- -----------
1           pizza dough                    1 package            1
1           tomato sauce                   1 can                2
1           favorite toppings              you choose           3
2           eggs                           three                1
2           a bunch of other ingredients   you choose           2

.

`#instruction`

recipe_id   task                             sort
----------- -------------------------------- -----------
1           pre-bake pizza dough             1
1           add tomato sauce                 2
1           add toppings                     3
1           bake a little longer             4
2           break eggs into mixing bowl      1
2           beat yolks and whites together   2
2           pour into large sauce pan        3
2           add other ingredients            4
2           fold in half                     5
2           cook until done                  6

.

I want to create an XML document that has one record for each recipe, and within each recipe element, I want a group of ingredients and another group of instructions, like this:

James L. · Accepted Answer

This SQL creates the desired XML verbatim:

select recipe.*,
       (
         select ingredient.name, ingredient.quantity
         from   #ingredient ingredient
         where recipe.id = ingredient.recipe_id
         order by ingredient.sort
         for xml auto, root('ingredients'), type
       ),
       (
         select instruction.task
         from   #instruction instruction
         where  recipe.id = instruction.recipe_id
         order by instruction.sort
         for xml auto, root('instructions'), type
       )
from   #recipe as recipe
order by recipe.name
for xml auto, root('recipes'), type

I aliased the temp table names because using for xml auto on temp tables creates poorly named XML elements. This is how it looks:

.

This SQL creates another version of the XML with all data as values instead of attributes, but in the same basic hierarchical structure:

select recipe.*,
       (
         select ingredient.name, ingredient.quantity
         from   #ingredient ingredient
         where recipe.id = ingredient.recipe_id
         order by ingredient.sort
         for xml path('ingredient'), root('ingredients'), type
       ),
       (
         select instruction.task
         from   #instruction instruction
         where  recipe.id = instruction.recipe_id
         order by instruction.sort
         for xml path('instruction'), root('instructions'), type
       )
from   #recipe as recipe
order by recipe.name
for xml path('recipe'), root('recipes'), type

.

This is how it looks:


  
    2
    omelet
    
      
        eggs
        three
      
      
        a bunch of other ingredients
        you choose
      
    
    
      
        break eggs into mixing bowl
      
      
        beat yolks and whites together
      
      
        pour into large sauce pan
      
      
        add other ingredients
      
      
        fold in half
      
      
        cook until done
      
    
  
  
    1
    pizza
    
      
        pizza dough
        1 package
      
      
        tomato sauce
        1 can
      
      
        favorite toppings
        you choose
      
    
    
      
        pre-bake pizza dough
      
      
        add tomato sauce
      
      
        add toppings
      
      
        bake a little longer

Originally I tried placing the ingredients and instructions in the main query's from clause with an inner join to the recipe table. But the instructions were all nested within the ingredients, which were nested within the recipe. When I moved them up to the select part of the query it straightened out the XML.

Convert Tables into XML using T-SQL

`#recipe`

`#ingredient`

`#instruction`

Answers (1)

Related Questions