For XML Explicit

Question

For XML Explicit

michanne

SSCrazy

Points: 2329
More actions
January 31, 2005 at 4:07 pm

#76228

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?

Thanks!

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Andrew Ebert SSC Veteran Points: 230 More actions · Answer 1

The XML is represented in a table. The data returned is spit out in the order that it is in the table. If you remove the FOR XML EXPLICIT statement and run the query you will see the order in which the records are returned. Get these records in the right order and then the returned XML will be ordered likewise.

To get the data out in the order that you want you need to include ORDER BY statements just before the FOR XML EXPLICIT.

In this case I think it should be ORDER BY [ReportData!1!Recid]

This way all records with ReportData!1!Recid = '1111' are first, then all records with ReportData!1!Recid ='2222' follow.

Good luck.

michanne SSCrazy Points: 2329 More actions · Answer 2

good tips, thank you.

I found i had to order it by something in the second union to get them "nested".