Beautiful XML from SQL without Explicit

The syntax is a little funny, but easy to learn.

You will see that a sub select is what results in a child collection of nodes. Note the TYPE keyword you have to include children.

Enjoy:

drop table tb_parent
drop table tb_child

GO

create table tb_parent 
    (Id int, Name varchar(50))
create table tb_child 
    (Id int, Name varchar(50), parentFk int)

GO

insert into tb_parent
    select 1, 'Colorado'
    union
    select 2, 'Missouri'
    union
    select 3, 'Kansas'

insert into tb_child
    select 1, 'Denver', 1
    union
    select 2, 'Conifer', 1
    union
    select 3, 'Evergreen', 1
    union
    select 4, 'Columbia', 2
    union
    select 5, 'Springfield', 2
    union
    select 6, 'Monett', 2
    union
    select 7, 'Salina', 3
    union
    select 8, 'Topeka', 3
    union
    select 9, 'Wichita', 3

GO
    
select 
     tb_Parent.Id as '@ParentId'
    ,tb_Parent.Name as 'ParentName'
    ,(select 
             tb_child.Id as '@ChildId'
            ,tb_child.Name as '@ChildName' 
        from tb_child
        where tb_child.ParentFk = tb_parent.Id 
        FOR XML PATH('Child'), TYPE) 
from tb_parent 
FOR XML PATH('Parent')

/*

RESULTS

<Parent ParentId="1">
  <ParentName>Colorado</ParentName>
  <Child ChildId="1" ChildName="Denver" />
  <Child ChildId="2" ChildName="Conifer" />
  <Child ChildId="3" ChildName="Evergreen" />
</Parent>
<Parent ParentId="2">
  <ParentName>Missouri</ParentName>
  <Child ChildId="4" ChildName="Columbia" />
  <Child ChildId="5" ChildName="Springfield" />
  <Child ChildId="6" ChildName="Monett" />
</Parent>
<Parent ParentId="3">
  <ParentName>Kansas</ParentName>
  <Child ChildId="7" ChildName="Salina" />
  <Child ChildId="8" ChildName="Topeka" />
  <Child ChildId="9" ChildName="Wichita" />
</Parent>

*/