FOR XML EXPLICIT formatting issue

I am trying to use FOR XML EXPLICIT to group records that are related.
I want to achieve something like
<Header Id="1" ... >
<Child1 Child1Id="1" Name="Fred" .../>
<Child1 Child1Id="2" Name="Tom" .../>
<Child2 Child2Id="3" Name="Dick" .../>
<Child2 Child2Id="4" Name="Harry" .../>
</Header>
<Header Id="2" ... >
<Child1 Child1Id="5" Name="Fred" .../>
<Child1 Child1Id="6" Name="Tom" .../>
<Child2 Child2Id="7" Name="Dick" .../>
<Child2 Child2Id="8" Name="Harry" .../>
</Header>
<Header Id="3" ... >
<Child1 Child1Id="9" Name="Fred" .../>
<Child1 Child1Id="10" Name="Tom" .../>
<Child2 Child2Id="11" Name="Dick" .../>
<Child2 Child2Id="12" Name="Harry" .../>
</Header>
I keep getting
<Header Id="1" ... >
<Header Id="2" ... />
<Header Id="3" ... />
<Child1 Child1Id="1" Name="Fred" .../>
<Child1 Child1Id="2" Name="Tom" .../>
<Child1 Child1Id="5" Name="Fred" .../>
<Child1 Child1Id="6" Name="Tom" .../>
<Child1 Child1Id="9" Name="Fred" .../>
<Child1 Child1Id="10" Name="Tom" .../>
<Child2 Child2Id="3" Name="Dick" .../>
<Child2 Child2Id="4" Name="Harry" .../>
<Child2 Child2Id="7" Name="Dick" .../>
<Child2 Child2Id="8" Name="Harry" .../>
<Child2 Child2Id="11" Name="Dick" .../>
<Child2 Child2Id="12" Name="Harry" .../>
The query looks something like
SELECT
1 AS Tag
, NULL AS Parent
, header AS 'Header!1!Id'
::
::
, NULL AS 'Child1!2!Child1Id'
, NULL AS 'Child1!2!Name'
, NULL AS 'Child2!3!Child1Id'
, NULL AS 'Child2!3!Name'
FROM
blah, blah, blah
UNION ALL
SELECT
1 AS Tag
, NULL AS Parent
, NULL AS 'Header!1!Id'
::
::
, ChildId AS 'Child1!2!Child1Id'
, Name AS 'Child1!2!Name'
, NULL AS 'Child2!3!Child1Id'
, NULL AS 'Child2!3!Name'
FROM
blah, blah, blah
UNION ALL
SELECT
1 AS Tag
, NULL AS Parent
, NULL AS 'Header!1!Id'
::
::
, NULL AS 'Child1!2!Child1Id'
, NULL AS 'Child1!2!Name'
, ChildId AS 'Child2!3!Child1Id'
, Name AS 'Child2!3!Name'
FROM
blah, blah, blah
Any help with either of these is gratefully appreciated.
Let me know if I am on the wrong path also, as I wouldn't be surprised
Thanks
SteveYou're missing an ORDER BY. Try this
create table #Header(HeaderId int)
insert into #Header(HeaderId) values(1)
insert into #Header(HeaderId) values(2)
insert into #Header(HeaderId) values(3)
create table #Child1(HeaderId int,Child1Id int,Name varchar(5))
create table #Child2(HeaderId int,Child2Id int,Name varchar(5))
insert into #Child1(HeaderId,Child1Id,Name) values(1,1,'Fred')
insert into #Child1(HeaderId,Child1Id,Name) values(1,2,'Tom')
insert into #Child2(HeaderId,Child2Id,Name) values(1,3,'Dick')
insert into #Child2(HeaderId,Child2Id,Name) values(1,4,'Harry')
insert into #Child1(HeaderId,Child1Id,Name) values(2,5,'Fred')
insert into #Child1(HeaderId,Child1Id,Name) values(2,6,'Tom')
insert into #Child2(HeaderId,Child2Id,Name) values(2,7,'Dick')
insert into #Child2(HeaderId,Child2Id,Name) values(2,8,'Harry')
insert into #Child1(HeaderId,Child1Id,Name) values(3,9,'Fred')
insert into #Child1(HeaderId,Child1Id,Name) values(3,10,'Tom')
insert into #Child2(HeaderId,Child2Id,Name) values(3,11,'Dick')
insert into #Child2(HeaderId,Child2Id,Name) values(3,12,'Harry')
SELECT 1 AS Tag
, NULL AS Parent
, h.HeaderId AS 'Header!1!Id'
, NULL AS 'Child1!2!Child1Id'
, NULL AS 'Child1!2!Name'
, NULL AS 'Child2!3!Child1Id'
, NULL AS 'Child2!3!Name'
FROM #Header h
UNION ALL
SELECT 2 AS Tag
, 1 AS Parent
, h.HeaderId AS 'Header!1!Id'
, c.Child1Id AS 'Child1!2!Child1Id'
, c.Name AS 'Child1!2!Name'
, NULL AS 'Child2!3!Child1Id'
, NULL AS 'Child2!3!Name'
FROM #Header h
INNER JOIN #Child1 c ON c.HeaderId=h.HeaderId
UNION ALL
SELECT 3 AS Tag
, 1 AS Parent
, h.HeaderId AS 'Header!1!Id'
, NULL AS 'Child1!2!Child1Id'
, NULL AS 'Child1!2!Name'
, c.Child2Id AS 'Child2!3!Child1Id'
, c.Name AS 'Child2!3!Name'
FROM #Header h
INNER JOIN #Child2 c ON c.HeaderId=h.HeaderId
ORDER BY h.HeaderId,Tag
FOR XML EXPLICIT
drop table #Child2
drop table #Child1
drop table #Header|||
"markc600@.hotmail.com" wrote:

> You're missing an ORDER BY. Try this
>
> create table #Header(HeaderId int)
> insert into #Header(HeaderId) values(1)
> insert into #Header(HeaderId) values(2)
> insert into #Header(HeaderId) values(3)
> create table #Child1(HeaderId int,Child1Id int,Name varchar(5))
> create table #Child2(HeaderId int,Child2Id int,Name varchar(5))
> insert into #Child1(HeaderId,Child1Id,Name) values(1,1,'Fred')
> insert into #Child1(HeaderId,Child1Id,Name) values(1,2,'Tom')
> insert into #Child2(HeaderId,Child2Id,Name) values(1,3,'Dick')
> insert into #Child2(HeaderId,Child2Id,Name) values(1,4,'Harry')
> insert into #Child1(HeaderId,Child1Id,Name) values(2,5,'Fred')
> insert into #Child1(HeaderId,Child1Id,Name) values(2,6,'Tom')
> insert into #Child2(HeaderId,Child2Id,Name) values(2,7,'Dick')
> insert into #Child2(HeaderId,Child2Id,Name) values(2,8,'Harry')
> insert into #Child1(HeaderId,Child1Id,Name) values(3,9,'Fred')
> insert into #Child1(HeaderId,Child1Id,Name) values(3,10,'Tom')
> insert into #Child2(HeaderId,Child2Id,Name) values(3,11,'Dick')
> insert into #Child2(HeaderId,Child2Id,Name) values(3,12,'Harry')
> SELECT 1 AS Tag
> , NULL AS Parent
> , h.HeaderId AS 'Header!1!Id'
> , NULL AS 'Child1!2!Child1Id'
> , NULL AS 'Child1!2!Name'
> , NULL AS 'Child2!3!Child1Id'
> , NULL AS 'Child2!3!Name'
> FROM #Header h
> UNION ALL
> SELECT 2 AS Tag
> , 1 AS Parent
> , h.HeaderId AS 'Header!1!Id'
> , c.Child1Id AS 'Child1!2!Child1Id'
> , c.Name AS 'Child1!2!Name'
> , NULL AS 'Child2!3!Child1Id'
> , NULL AS 'Child2!3!Name'
> FROM #Header h
> INNER JOIN #Child1 c ON c.HeaderId=h.HeaderId
> UNION ALL
> SELECT 3 AS Tag
> , 1 AS Parent
> , h.HeaderId AS 'Header!1!Id'
> , NULL AS 'Child1!2!Child1Id'
> , NULL AS 'Child1!2!Name'
> , c.Child2Id AS 'Child2!3!Child1Id'
> , c.Name AS 'Child2!3!Name'
> FROM #Header h
> INNER JOIN #Child2 c ON c.HeaderId=h.HeaderId
> ORDER BY h.HeaderId,Tag
> FOR XML EXPLICIT
>
> drop table #Child2
> drop table #Child1
> drop table #Header
>
Excellent, so close yet so far.
Also, in my actual code I had not propogated the Header Id into the other
parts of the union, it din't actually know what the relationships were.
And now BizTalk seems to like it.
Thanks