Tearing my hair out FOR XML EXPLICIT root

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Tearing my hair out FOR XML EXPLICIT root

Author

Topic

Cowboy
Yak Posting Veteran

72 Posts

Posted - 2009-01-20 : 07:34:09

Hi All,

I have the following query:

	SELECT DISTINCT
			1           tag,
			NULL        parent,
			s.shoid     [showname!1!id!hide],
			s.shoname   [showname!1!name],
			NULL        [performancedate!2!date!xml],
			NULL        [performancedate!2!venue!xml]
	FROM showname s JOIN performance p
	ON p.shoid = s.shoid
	WHERE year(pertime) = '2009' AND p.pertime IS NOT NULL
	UNION ALL

	SELECT DISTINCT
			2,
			1,
			s.shoid,
			s.shoname,
			pertime,
			venid
	FROM performance p JOIN showname s
	ON p.shoid = s.shoid
	WHERE year(pertime) = '2009'AND p.venid IS NOT NULL

	ORDER BY 4,1
	FOR XML EXPLICIT

which generates xml like this:

<showname name="Murray">
<performancedate>
<date>2009-04-04T19:30:00</date>
<venue>ZEF</venue>
</performancedate>
<performancedate>
<date>2009-04-17T20:00:00</date>
<venue>A07</venue>
</performancedate>
</showname>
<showname name="CAMPBELL">
<performancedate>
<date>2009-06-21T19:30:00</date>
<venue>SCC</venue>
</performancedate>
</showname>
<showname name="Candi">
<performancedate>
<date>2009-02-27T20:00:00</date>
<venue>ZRF</venue>
</performancedate>
</showname>

this is fine but when I try to add a root element it skews my XML:

SELECT DISTINCT
1 tag,
NULL parent,
NULL [root!1],
s.shoid [showname!2!id!hide],
s.shoname [showname!2!name],
NULL [performancedate!3!date!xml],
NULL [performancedate!3!venue!xml]
FROM showname s JOIN performance p
ON p.shoid = s.shoid
WHERE year(pertime) = '2009' AND p.pertime IS NOT NULL
UNION ALL

SELECT DISTINCT
2,
1,
NULL,
s.shoid,
s.shoname,
pertime,
venid
FROM performance p JOIN showname s
ON p.shoid = s.shoid
WHERE year(pertime) = '2009'AND p.venid IS NOT NULL

ORDER BY 5
FOR XML EXPLICIT

xml:

<root>
  <showname name="Murray" />
  <showname name="Murray" />
</root>
<root>
  <showname name="CAMPBELL" />
</root>
<root>
  <showname name="Candi" />
</root>

I want to build a spaceship with ligthspeed capabilities and I don't even know what a wrench is.

Cowboy
Yak Posting Veteran

72 Posts

Posted - 2009-01-23 : 06:10:11

Ok I worked out the problem I needed to create a new union for the root element populated by NULL values:

SELECT DISTINCT

	1 as Tag,
	NULL as Parent,
	NULL AS [PerformancesByDate!1], -- empty root element
	NULL AS [name!2!showname],
	NULL AS [name!2!showfullname],
	NULL AS [performance!3!date],
	NULL AS [performance!3!venue]
	UNION ALL

SELECT DISTINCT

	2 AS Tag,
	1 AS Parent,
	NULL,
	s.shoname,
	s.shofullname,
	NULL,
	NULL
	FROM showname s JOIN performance p
	ON p.shoid = s.shoid
	WHERE year(pertime) = '2009' AND p.pertime IS NOT NULL
	UNION ALL

 SELECT DISTINCT

	3 AS Tag,
	2 AS Parent,
	NULL,
	s.shoname,
	s.shofullname,
	pertime,
	venid
	FROM performance p JOIN showname s
	ON p.shoid = s.shoid
	WHERE year(pertime) = '2009'AND p.venid IS NOT NULL

ORDER BY 4, Parent

FOR XML EXPLICIT

Hope this helps someone

I want to build a spaceship with ligthspeed capabilities and I don't even know what a wrench is.

Sajesh.kumar
Starting Member

3 Posts

Posted - 2009-01-29 : 02:09:41

<Career>
<Details>
<FirstName>FName</FirstName>
<LastName>Lanme</LastName>
<DateofBirth>8888</DateofBirth>
<EmailAddress>sombody@email.com</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 want to generate XMl in the above mentioned Format

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 .
Can any body help me out .Thanks in advance.

Sajesh Kumar

Cowboy
Yak Posting Veteran

72 Posts

Posted - 2009-01-29 : 04:36:37

Hi Sajesh, Can you post your output so we can see what is happening?

I want to build a spaceship with ligthspeed capabilities and I don't even know what a wrench is.

Sajesh.kumar
Starting Member

3 Posts

Posted - 2009-02-02 : 06:01:57

This is the out put i am getting

<Career>
<Details>
<FirstName>FName</FirstName>
<LastName>Lanme</LastName>
<DateofBirth>8888</DateofBirth>
<EmailAddress>sombody@email.com</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>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources