Help required for Using FOR XML EXPLICIT

[Sajesh]

Can any body help me out in using FOR XML EXPLICIT clause in sql 2000

I want to generate XMl in the below mentioned Format

<Career>
<Details>
<FirstName>FName</FirstName>
<LastName>Lanme</LastName>
<DateofBirth>8888</DateofBirth>
<EmailAddress>[e-mail]</EmailAddress>
<City>Bangalore</City>
<State>Karnataka</State>
<Country>India</Country>
<Pincode>560017</Pincode>
<ContactNumbers>
<Phone>12312312</Phone>
<Mobile>9789789</Mobile>
</ContactNumbers>
<ResumeSummary>
<TotalExperience>8</TotalExperience>
<AnnualSalary>8</AnnualSalary>
<FunctionalArea>FranchiseDevelopment </FunctionalArea>
<Industry>Manufacturing</Industry>
<KeySkills>StrategicPlanning,ProfitCenterHead</KeySkills>
</ResumeSummary>
<EducationDetails >
<BasicEducation>B.Sc</Basic Education>
<HigherEducation>PhD</Basic Education>
<UploadResume>sajesh.cv.doc</UploadResume>
</EducationDetails >
</Details>



I am Using Query as Follows
SELECT 1 AS Tag,
null AS Parent,
carrer_firstname AS [Details!1!FirstName!element],
carrer_lastname AS [Details!1!LastName!element],
convert(varchar(50),dateOfBirth,103) AS [Details!1!DateofBirth!element],
null AS [ContactNumbers!2!Phone!Element],
null AS [ContactNumbers!2!Mobile!Element],
null AS [ResumeSummary!3!TotalExperience!element],
null AS [ResumeSummary!3!AnnualSalary!element],
null AS [ResumeSummary!3!FunctionalArea!element],
null AS [ResumeSummary!3!Industry!element],
null AS [ResumeSummary!3!KeySkills!element],
null AS [EducationDetails!4!BasicEducation!element],
null AS [EducationDetails!4!HigherEducation!element],
null AS [EducationDetails!4!UploadResume!element]
FROM tbl_carrerDetails as Career

union all

SELECT 2 AS Tag,
1 AS Parent,
carrer_firstname,
carrer_lastname,
convert(varchar(50),dateOfBirth,103),
Phonenumber,
mobileno ,
totalexp, annualsalry, funcationalarea, industries, keyskills, basic_education, highereducation, carrer_doc
FROM tbl_carrerDetails

union all

SELECT 3 AS Tag,
2 AS Parent,
carrer_firstname,
carrer_lastname,
convert(varchar(50),dateOfBirth,103),
Phonenumber ,
mobileno ,
totalexp ,
annualsalry ,
funcationalarea,
industries ,
keyskills,
basic_education, highereducation, carrer_doc
FROM tbl_carrerDetails


union all


SELECT 4 AS Tag,
3 AS Parent,
carrer_firstname,
carrer_lastname,
convert(varchar(50),dateOfBirth,103),
Phonenumber ,
mobileno ,
totalexp ,
annualsalry ,
funcationalarea ,
industries ,
keyskills ,
basic_education ,
highereducation ,
carrer_doc
FROM tbl_carrerDetails

order by [Details!1!FirstName!element]
for xml explicit

But i am not geting desired result .

This is the output i am getting

<Career>
<Details>
<FirstName>FName</FirstName>
<LastName>Lanme</LastName>
<DateofBirth>8888</DateofBirth>
<EmailAddress>[e-mail]</EmailAddress>
<City>Bangalore</City>
<State>Karnataka</State>
<Country>India</Country>
<Pincode>560017</Pincode>
<ContactNumbers>
<Phone>12312312</Phone>
<Mobile>9789789</Mobile>

<ResumeSummary>
<TotalExperience>8</TotalExperience>
<AnnualSalary>8</AnnualSalary>
<FunctionalArea>FranchiseDevelopment </FunctionalArea>
<Industry>Manufacturing</Industry>
<KeySkills>StrategicPlanning,ProfitCenterHead</KeySkills>

<EducationDetails >
<BasicEducation>B.Sc</Basic Education>
<HigherEducation>PhD</Basic Education>
<UploadResume>sajesh.cv.doc</UploadResume>

</EducationDetails >
</ResumeSummary>
</ContactNumbers>
</Details>
</Career>

Thanks in advance.

[Akash]

Hi,

Use the below query... You have specified wrong parentnode in the code. I have marked the changes to be done with red color.

SELECT 1 AS Tag,
null AS Parent,
carrer_firstname AS [Details!1!FirstName!element],
carrer_lastname AS [Details!1!LastName!element],
convert(varchar(50),dateOfBirth,103) AS [Details!1!DateofBirth!element],
null AS [ContactNumbers!2!Phone!Element],
null AS [ContactNumbers!2!Mobile!Element],
null AS [ResumeSummary!3!TotalExperience!element],
null AS [ResumeSummary!3!AnnualSalary!element],
null AS [ResumeSummary!3!FunctionalArea!element],
null AS [ResumeSummary!3!Industry!element],
null AS [ResumeSummary!3!KeySkills!element],
null AS [EducationDetails!4!BasicEducation!element],
null AS [EducationDetails!4!HigherEducation!element],
null AS [EducationDetails!4!UploadResume!element]
FROM tbl_carrerDetails as Career

union all

SELECT 2 AS Tag,
1 AS Parent,
carrer_firstname,
carrer_lastname,
convert(varchar(50),dateOfBirth,103),
Phonenumber,
mobileno ,
totalexp, annualsalry, funcationalarea, industries, keyskills, basic_education, highereducation, carrer_doc
FROM tbl_carrerDetails

union all

SELECT 3 AS Tag,
1 AS Parent,
carrer_firstname,
carrer_lastname,
convert(varchar(50),dateOfBirth,103),
Phonenumber ,
mobileno ,
totalexp ,
annualsalry ,
funcationalarea,
industries ,
keyskills,
basic_education, highereducation, carrer_doc
FROM tbl_carrerDetails


union all


SELECT 4 AS Tag,
1 AS Parent,
carrer_firstname,
carrer_lastname,
convert(varchar(50),dateOfBirth,103),
Phonenumber ,
mobileno ,
totalexp ,
annualsalry ,
funcationalarea ,
industries ,
keyskills ,
basic_education ,
highereducation ,
carrer_doc
FROM tbl_carrerDetails

[Sajesh]

Thanks Akash,Query is working as expected.