For XML Explicit Syntax to Normalize a table

Question

Post reply

For XML Explicit Syntax to Normalize a table

Brandon Forest

SSCommitted

Points: 1977
More actions
January 13, 2008 at 1:01 am

#185429

I am trying to write a FOR XML EXPLICIT query for the following table:
GameName Count1 Count2 CountTotal Percent1 Percent2 PercentTotal
---------- ----------- ----------- ----------- ----------- ----------- ------------
Clinker 44 55 99 45 55 100
Skullcap 12 88 100 10 90 100
What I want to create is:
Using the following SQL query:
Select 1 As Tag,
Null As Parent,
GameName As [Games!1!GameName],
Null AS [Games!2!Count1],
Null AS [Games!2!Count2],
Null AS [Games!2!CountTotal],
Null AS [Games!3!Percent1],
Null As [Games!3!Percent2],
Null AS [Games!3!PercentTotal]
From Games
UNION ALL
Select
2,
1,
Null,
Count1,
Count2,
CountTotal,
Null,
Null,
Null
From Games
UNION ALL
Select
3,
2,
Null,
Null,
Null,
Null,
Percent1,
Percent2,
PercentTotal
From Games
FOR XML Explicit
What I am getting is:
Which of course isn't well formed XML.
Can anyone give me a clue on how to write this query?
Thanks!
Brandon_Forest@sbcglobal.net

Viewing 5 posts - 1 through 4 (of 4 total)

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

Mark Cowne One Orange Chip Points: 26954 More actions · Answer 1

Brandon,

Few things...

Your XML hasn't appeared so it's not possible to tell what you're after. You'll need to repost.

FOR XML EXPLICIT requires an ORDER BY clause to work reliably.

If you are using SQL Server 2005, you can use FOR XML PATH which is much simpler.

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 2

Youre XML is being mistaken for HTML formatting - of the site is "truncating" it.

Before posting it - drop the XML into notepad, and run a find/replace to put in the HTML version of the left and right brackets.

That is:

< needs to be replaced with &lt;

> needs to be replaced with &gt;

Once you do that - the XML should show up (although it might need some formatting to make it readable).

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 3

I second the vote for XML PATH. It will make a huge difference.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Brandon Forest SSCommitted Points: 1977 More actions · Answer 4

Thanks guys. I normalized the data and used the FOR XML PATH syntax to create Elements instead of Attributes. Then I used the following conditional (thanks Shane) to conditionally fill the two tables.

Brandon_Forest@sbcglobal.net