Jerry Nixon on Windows: Beautiful XML from SQL without Explicit

Jerry Nixon on Windows

Wednesday, January 9, 2008

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>

*/