Retriving data from database to xml

Hi,
I'm using SQL Server 2000 and I'm having some dificulties to produce the
rigth xml document using the FOR XML clause of SELECT.
The SELECT that I'm using is the following:
select 1 as Tag, NULL as Parent, Header.TaxRegistrationNumber as
[Header!1!TaxRegistrationNumber!element], Header.CompanyName as
[Header!1!CompanyName!element], NULL as
[CompanyAddress!2!BuildingNumber!element], NULL as
[CompanyAddress!2!AddressDetail!element], Header.FiscalYear as
[Header!1!FiscalYear!element]
from Header LEFT JOIN CompanyAddress
UNION
select 2, 1, Header.TaxRegistrationNumber, Header.CompanyName,
CompanyAddress.BuildingNumber, CompanyAddress.AddressDetail, Header.FiscalYear
from from Header LEFT JOIN CompanyAddress
FOR XML EXPLICIT
and the xml result is:
<Header>
<TaxRegistrationNumber>123456787</TaxRegistrationNumber>
<CompanyName>dese</CompanyName>
<FiscalYear>2007</FiscalYear>
<CompanyAddress>
<BuildingNumber></BuildingNumber>
<AddressDetail>250 APT 751</AddressDetail>
</CompanyAddress>
</Header>
The problem is with the <FiscalYear> tag that appears before the
<CompanyAddress> tag and I want that the <FiscalYear> tag appears after the
end of the </CompanyAddress> tag, like this:
<Header>
<TaxRegistrationNumber>123456787</TaxRegistrationNumber>
<CompanyName>dese</CompanyName>
<CompanyAddress>
<BuildingNumber></BuildingNumber>
<AddressDetail>250 APT 751</AddressDetail>
</CompanyAddress>
<FiscalYear>2007</FiscalYear>
</Header>
Is there any way to build this kind of xml result using FOR XML clause?
Thanks.
Hello Ferreira,
You will probably need to make a it level 3 value (e.g., [header!3!FiscalYear!Ement)
where 3 is a child of 1.
kt