SQL query to XML in row/column format in pivot

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 2008 Forums

Transact-SQL (2008)

SQL query to XML in row/column format in pivot

Author

Topic

Bart111ac
Starting Member

7 Posts

Posted - 2014-02-05 : 07:50:48

Hi,

Thanks reading.

I need to export data via SQL to XML. But I need the XML format in Row/column and the output must be a Pivot table. I know that it can be done with AS pivot. But I don't know how to use it in the below script

I've already got a fantastic script from visakh16.

:

use Cluster_Data_Mart_NEW
;With CTE
AS
(
SELECT [Cluster_Name]
,[Cell_Name]
,[Client_Name]
,[Assigned]
FROM CDM_Fact_Personnel_Assigned
INNER JOIN CDM_Dim_Organization on CDM_Fact_Personnel_Assigned.Organization_Key = CDM_Dim_Organization.Organization_Key
INNER JOIN CDM_Dim_Client on CDM_Fact_Personnel_Assigned.Client_Key = CDM_Dim_Client.Client_Key
)
select
(
select [Cluster_Name] AS [@name],
(select [Cell_Name] as [column],
null as tmp,
Client_Name as [column],
null as tmp,
Assigned as [column]
from CTE
where [Cluster_Name] = t.[Cluster_Name]
for xml path('row'),type) as [*]
from (select distinct [Cluster_Name] from CTE)t
for xml path('variable'),root('data')
) as col_xml

the above query needs to be in the same XML format(Row/Clolumn) but then in a Pivot table structure.

The desired outcome:

................CellA...CellB
companyA..4.......3
companyB..0.......4
companyC..1.......2

(whithout the ..............

)

The desired outcome in XML:

<data>
<variable name="Cluster_Name">
<row>
<column></column>
<column>CellA</column>
<column>CellB</column>
</row>
<row>
<column>companyA</column>
<column>4</column>
<column>3</column>
</row>
<row>
<column>companyB</column>
<column>0</column>
<column>4</column>
</row>
<row>
<column>companyC</column>
<column>1</column>
<column>2</column>
</row>
</variable>
</data>

Is this possible?

Thanks in advance.

Regards,

Bart

Bart111ac
Starting Member

7 Posts

Posted - 2014-02-07 : 08:21:42

;With CTE
AS
(
SELECT [Cluster_Name]
,[Cell_Name]
,[Client_Name]
,[Assigned]
FROM CDM_Fact_Personnel_Assigned
INNER JOIN CDM_Dim_Organization on
CDM_Fact_Personnel_Assigned.Organization_Key =
CDM_Dim_Organization.Organization_Key
INNER JOIN CDM_Dim_Client on CDM_Fact_Personnel_Assigned.Client_Key =
CDM_Dim_Client.Client_Key
) , pvtClust as
(
select [Cluster_Name], [Client_Name],
[CellA], [CellB]
from CTE
pivot (Sum(Assigned) for
[Cell_Name] in ([CellA], [CellB])) pvt

)
select
(
select [Cluster_Name] AS [@name],
(SELECT '' as [column]
, null as [tmp]
,'CellA' as [column]
, null as [tmp]
, 'CellB' as [column]
for xml path ('row'), type) as [*],
(select [Client_Name] as [column],
null as tmp,
ISNULL([CellA], 0) as [column],
null as tmp,
ISNULL([CellB], 0) as [column]
from pvtClust
where [Cluster_Name] = t.[Cluster_Name]
for xml path('row'),type) as [*]
from (select distinct [Cluster_Name] from CTE) t
for xml path('variable'),root('data')
) as col_xml

Credits to:

visakh16

and

Russel Loski, MCT, MCSE Data Platform/Business Intelligence. Twitter: @sqlmovers; blog: www.sqlmovers.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources