SQL Server 2005 Cross Tab To XML For Xcelsius

GoCubs
GoCubs used Ask the Experts™
on
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>

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Muhammad KashifDevelopment Manager

Commented:
according to me your output format should be (My recommended output)

<data>
  <variable name="gender">
    <row Type="Female">
      <column>2345</column>
    </row>
    <row Type="Male">
      <column> 1234</column>
    </row>
  </variable>
</data>

FOR this
you need to add a column RowType where you can group the records.



BEGIN
set nocount on
create table #TempTable (ID int Identity (1,1), RowType varchar(10), Column1 varchar(100))
insert into #TempTable 
	SELECT 'Male' AS RowType, CONVERT(varchar, SUM(CASE WHEN Sex = 'M' THEN 1 ELSE 0 END))
FROM MyTable
UNION ALL
	SELECT 'Female' AS RowType, 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!Type'
	, 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!Type'
	, null as 'column!4'
union all
select	3 as 'TAG'
	, 2 as 'parent'
	, null as 'data!1'
	, null as 'variable!2!name'
	, RowType as 'row!3!Type'
	, null as 'column!4'
from #TempTable
union all
select	4 as 'TAG'
	, 3 as 'parent'
	, null as 'data!1'
	, null as 'variable!2!name'
	, RowType as 'row!3!Type'
	, Column1 as 'column!4'
from #TempTable
order by 'row!3!Type', 'column!4'
for XML explicit 
drop table #TempTable  
END

Open in new window

Development Manager
Commented:
If you want your own desired output then you also need to add a column RowType where you can group the records.

Following query gives you your required output.
BEGIN
set nocount on
create table #TempTable (ID int Identity (1,1), RowType varchar(10), Column1 varchar(100), Column2 varchar(100))
insert into #TempTable 
	SELECT '0' AS RowType, 'Male' AS Column1, 'Female' AS Column2
	union all
SELECT '1' AS RowType, 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!Type'
	, 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!Type'
	, null as 'column!4'
union all
select	3 as 'TAG'
	, 2 as 'parent'
	, null as 'data!1'
	, null as 'variable!2!name'
	, RowType as 'row!3!Type'
	, null as 'column!4'
from #TempTable
union all
select	4 as 'TAG'
	, 3 as 'parent'
	, null as 'data!1'
	, null as 'variable!2!name'
	, RowType as 'row!3!Type'
	,	Column1 as 'column!4'
from #TempTable
union
select	4 as 'TAG'
	, 3 as 'parent'
	, null as 'data!1'
	, null as 'variable!2!name'
	, RowType as 'row!3!Type'
	,	Column2 as 'column!4'
from #TempTable 
order by 'row!3!Type' for XML explicit 
drop table #TempTable  
END

Open in new window

Author

Commented:
GREAT! Thanks. I will accept this solution since it will work although I am not sure if Xcelsius will choke on the Type="0" designation. I was hoping for something slick in SQL 2005 but alas I don't think that there is. Great work.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial