troubleshooting Question

SQL Server 2005 Cross Tab To XML For Xcelsius

Avatar of GoCubs
GoCubsFlag for United States of America asked on
Microsoft SQL Server 2005
3 Comments1 Solution384 ViewsLast Modified:
Ok. I have a specific question here from which I will have to adapt more complex situations but I am hoping that this will get me started....
I am running SQL Server 2005 and need to generate XML in a precise format for Xcelsius.
I have a table of that contains many fields, one of which is Sex. I need to generate the data in a crosstab fashion. Therefor Select Sex, Count(*) SexCount from MyTable group by Sex will not work because that will generate:
               Sex     SexCount
Row1      Male     1234
Row2      Female 2345
when what I need is:
               Column1     Column2
Row1      Male           Female
Row2     1234           2345
I was looking for a nice clean 2005 solution that will pivot instead of a SQL 2000 solution such as this:
create table #TempTable (Column1 varchar(100), Column2 varchar(100))
insert into #Temp Table SELECT 'Male' AS Column1, 'Female' AS Column2
insert into #Temp Table SELECT CONVERT(varchar, SUM(CASE WHEN inmatesexcode = 'M' THEN 1 ELSE 0 END)) , CONVERT(varchar, SUM(CASE WHEN inmatesexcode = 'F' THEN 1 ELSE 0 END))
FROM MyTable
This solution was provided. If there is no clean SQL 2005 way then I can work with this.

Next, I need to get this data into a specific XML format for Xcelcius. The format is:
<data>
<variable name="gender">
<row>
<column>Male</column>
<column>Female</column>
</row>
<row>
<column>1234</column>
<column>2345</column>
</row>
</variable>
<data>
I have gotten close using SQL Server 2000 and one of those ugly queries:
select 1 as 'TAG', null as 'parent'..... For XML EXPLICIT
I have been able to get:
<data><variable name="gender">
<row>
<column>2345</column><column>1234</column>
<column>Female</column><column>Male</column>
</row>
</variable></data>
As you can see, it is not in the right format because the row should close after the second column and open a new row before the third.
I PRAY that there is an easier method in 2005 but I am not knowledgeable in 2005. I will include my code in the code section:
NEEDED FORMAT:
<data>
<variable name="gender">
<row>
<column>Male</column> 
<column>Female</column>
</row>
<row>
<column>1234</column>
<column>2345</column>
</row>
</variable>
<data>
 
HERE IS THE SQL THAT GENERATES THE XML IN SQL 2000:
BEGIN
set nocount on
create table #TempTable (ID int Identity (1,1), Column1 varchar(100), Column2 varchar(100))
insert into #TempTable SELECT 'Male' AS Column1, 'Female' AS Column2
insert into #TempTable SELECT CONVERT(varchar, SUM(CASE WHEN Sex = 'M' THEN 1 ELSE 0 END)) , 
      CONVERT(varchar, SUM(CASE WHEN Sex = 'F' THEN 1 ELSE 0 END)) 
FROM MyTable
 
select	
	1 as 'TAG'
	, null	as 'parent'
	, null	as 'data!1'
	, null	as 'variable!2!name'
	, null as 'row!3'
	, null as 'column!4'
union all
select
	2 as 'TAG'
	, 1 as 'parent'
	, null as 'data!1'
	, 'gender' as 'variable!2!name'
	, null as 'row!3'
	, null as 'column!4'
union all
select	3 as 'TAG'
	, 2 as 'parent'
	, null as 'data!1'
	, null as 'variable!2!name'
	, '' as 'row!3'
	, null as 'column!4'
union all
select	4 as 'TAG'
	, 3 as 'parent'
	, null as 'data!1'
	, null as 'variable!2!name'
	, null as 'row!3'
	,	Column1 as 'column!4'
from #TempTable
union
select	4 as 'TAG'
	, 3 as 'parent'
	, null as 'data!1'
	, null as 'variable!2!name'
	, null as 'row!3'
	,	Column2 as 'column!4'
from #TempTable 
order by 'column!4' for XML explicit
drop table #TempTable 
END
 
HERE IS THE OUTPUT THAT IT GENERATES:
<data><variable name="gender"><row><column>2345</column><column>1234</column><column>Female</column><column>Male</column></row></variable></data>
ASKER CERTIFIED SOLUTION
Muhammad Kashif
Development Manager

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 3 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros