for xml explicit

Hello,
I have been using for xml Explicit for a little while but this one has got
me stumped. I am return several tables that will each end up on a different
excel worksheet. A portion of the query is:
SELECT
1 as Tag,--metadata
Null as Parent,
isnull(@.project,'MISC') as [ReportData!1!Project],
Recid as [ReportData!1!Recid],
tnum as [ReportData!1!tnum],
null as [Metadata!2!WorksheetName!Element], --optional
Null AS [Metadata!2!Title!Element],
Null as [Metadata!2!FirstSubTitle!Element], --optional
Null as [Metadata!2!SecondSubTitle!Element], --optional
Null as [Metadata!2!Asofdate!Element],
Null as [Metadata!2!Rundate!Element]
from @.tblrecid as ReportData
union all
SELECT
2 as tag, --metadata
1 as parent, -- subset of Reportdata
Null, --Project
Reportdata.Recid as [ReportData!1!Recid],
Reportdata.tnum as [ReportData!1!tnum],
isnull(lu.worksheetname,left(rtrim(l.type1),8)+'_'+left(rtrim(l.type2),10)),
l.Title,
lu.Title2 as FirstSubTitle,
lu.Subtitle1 as SecondSubTitle,
convert(char(10),l.Asofdate,121) as Asofdate,
convert(char(10),l.rundatetime,121) as Rundate
FROM tblReportLog l join tblReportLU lu
on l.tnum = lu.tnum join @.tblRecid Reportdata
on l.recid = Reportdata.recid
for xml explicit
The recid is the identifyer for each table.
what i get is:
<ReportData Project="MISC" Recid="1111" tnum="11">
<Metadata>
<WorksheetName></WorksheetName>
<Title></Title>
<FirstSubTitle></FirstSubTitle>
<SecondSubTitle></SecondSubTitle>
<Asofdate>2004-09-30</Asofdate>
<Rundate>2004-10-14</Rundate>
</Metadata>
<Metadata>
<WorksheetName>R_Freq</WorksheetName>
<Title></Title>
<FirstSubTitle></FirstSubTitle>
<SecondSubTitle></SecondSubTitle>
<Asofdate>2004-09-30</Asofdate>
<Rundate>2004-11-01</Rundate>
</Metadata>
</ReportData>
<ReportData Project="MISC" Recid="2222" tnum="22"/>
What I want is (the root is added later):
<ReportData Project="MISC" Recid="1111" tnum="11">
<Metadata>
<WorksheetName></WorksheetName>
<Title></Title>
<FirstSubTitle></FirstSubTitle>
<SecondSubTitle></SecondSubTitle>
<Asofdate>2004-09-30</Asofdate>
<Rundate>2004-10-14</Rundate>
</Metadata>
</ReportData>
<ReportData Project="MISC" Recid="2222" tnum="22">
<Metadata>
<WorksheetName></WorksheetName>
<Title></Title>
<FirstSubTitle></FirstSubTitle>
<SecondSubTitle></SecondSubTitle>
<Asofdate></Asofdate>
<Rundate></Rundate>
</Metadata>
</ReportData>
Any ideas?Are you missing the order by that will group the children rows to its parent
row? Your excerpt does not show one...
Adding something like
order by [ReportData!1!Recid]
should help.
Best regards
Michael
PS: Another good case where using FOR XML PATH in SQL Server 2005 will make
writing such queries so much easier...
"michanne" <michanne@.discussions.microsoft.com> wrote in message
news:6D0DDE55-012A-4287-96C6-56C483880857@.microsoft.com...
> Hello,
> I have been using for xml Explicit for a little while but this one has got
> me stumped. I am return several tables that will each end up on a
> different
> excel worksheet. A portion of the query is:
> SELECT
> 1 as Tag,--metadata
> Null as Parent,
> isnull(@.project,'MISC') as [ReportData!1!Project],
> Recid as [ReportData!1!Recid],
> tnum as [ReportData!1!tnum],
> null as [Metadata!2!WorksheetName!Element], --optional
> Null AS [Metadata!2!Title!Element],
> Null as [Metadata!2!FirstSubTitle!Element], --optional
> Null as [Metadata!2!SecondSubTitle!Element], --optional
> Null as [Metadata!2!Asofdate!Element],
> Null as [Metadata!2!Rundate!Element]
> from @.tblrecid as ReportData
> union all
> SELECT
> 2 as tag, --metadata
> 1 as parent, -- subset of Reportdata
> Null, --Project
> Reportdata.Recid as [ReportData!1!Recid],
> Reportdata.tnum as [ReportData!1!tnum],
> isnull(lu.worksheetname,left(rtrim(l.type1),8)+'_'+left(rtrim(l.type2),10)
),
> l.Title,
> lu.Title2 as FirstSubTitle,
> lu.Subtitle1 as SecondSubTitle,
> convert(char(10),l.Asofdate,121) as Asofdate,
> convert(char(10),l.rundatetime,121) as Rundate
> FROM tblReportLog l join tblReportLU lu
> on l.tnum = lu.tnum join @.tblRecid Reportdata
> on l.recid = Reportdata.recid
> for xml explicit
> The recid is the identifyer for each table.
> what i get is:
> <ReportData Project="MISC" Recid="1111" tnum="11">
> <Metadata>
> <WorksheetName></WorksheetName>
> <Title></Title>
> <FirstSubTitle></FirstSubTitle>
> <SecondSubTitle></SecondSubTitle>
> <Asofdate>2004-09-30</Asofdate>
> <Rundate>2004-10-14</Rundate>
> </Metadata>
> <Metadata>
> <WorksheetName>R_Freq</WorksheetName>
> <Title></Title>
> <FirstSubTitle></FirstSubTitle>
> <SecondSubTitle></SecondSubTitle>
> <Asofdate>2004-09-30</Asofdate>
> <Rundate>2004-11-01</Rundate>
> </Metadata>
> </ReportData>
> <ReportData Project="MISC" Recid="2222" tnum="22"/>
> What I want is (the root is added later):
> <ReportData Project="MISC" Recid="1111" tnum="11">
> <Metadata>
> <WorksheetName></WorksheetName>
> <Title></Title>
> <FirstSubTitle></FirstSubTitle>
> <SecondSubTitle></SecondSubTitle>
> <Asofdate>2004-09-30</Asofdate>
> <Rundate>2004-10-14</Rundate>
> </Metadata>
> </ReportData>
> <ReportData Project="MISC" Recid="2222" tnum="22">
> <Metadata>
> <WorksheetName></WorksheetName>
> <Title></Title>
> <FirstSubTitle></FirstSubTitle>
> <SecondSubTitle></SecondSubTitle>
> <Asofdate></Asofdate>
> <Rundate></Rundate>
> </Metadata>
> </ReportData>
> Any ideas?
>|||I had an order clause just like that but i took it out in one of the
iterations. I just tested it again to be sure but the result was the same. :
-(
As much as i'd prefer 2005, it isn't going to be available to me for a long
time.
"Michael Rys [MSFT]" wrote:

> Are you missing the order by that will group the children rows to its pare
nt
> row? Your excerpt does not show one...
> Adding something like
> order by [ReportData!1!Recid]
> should help.
> Best regards
> Michael
> PS: Another good case where using FOR XML PATH in SQL Server 2005 will mak
e
> writing such queries so much easier...
> "michanne" <michanne@.discussions.microsoft.com> wrote in message
> news:6D0DDE55-012A-4287-96C6-56C483880857@.microsoft.com...
>
>|||Ok - i needed to also order by one of the fields in tag 2.
Thanks!
"michanne" wrote:
> I had an order clause just like that but i took it out in one of the
> iterations. I just tested it again to be sure but the result was the same.
:-(
> As much as i'd prefer 2005, it isn't going to be available to me for a lon
g
> time.
> "Michael Rys [MSFT]" wrote:
>