SQL query to XML in row/column format

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

Author

Topic

Bart111ac
Starting Member

7 Posts

Posted - 2014-01-29 : 05:09:27

Hi Experts,

I need to make a interface between SQL server 2008 R2 and SAP Business Objects Dashboards (former Xcelsius) via XML.

The XML file needs to be in a special format:

<data>
<variable name="Headcount">
<row>
<column>Sales</column>
<column>31</column>
</row>
<row>
<column>Production</column>
<column>18</column>
</row>
<row>
<column>Marketing</column>
<column>27</column>
</row>
</variable>
</data>

This is my output:

<data>
<variable name="Headcount">
<row>
<column>Sales</column>
<column>31</column>
</row>
</variable>
<variable name="Headcount">
<row>
<column>Production</column>
<column>18</column>
</row>
</variable>
<variable name="Headcount">
<row>
<column>Marketing</column>
<column>27</column>
</row>
</variable>

My query:

SELECT [Cluster_Name] '@name'
,[Cell_Name] as'row/column'
,NULL as 'row/tmp'
,[Headcount]as 'row/column'
FROM CDM_Fact_Organization
INNER JOIN CDM_Dim_Organization on CDM_Fact_Organization.Organization_Key = CDM_Dim_Organization.Organization_Key
for XML PATH('variable'),root('data')

The tag <variable name="Headcount"> repeats for each row, which I can't have.

Does somebody have an idee to fix this?

Thanks in advance,

Regards Bart

Bart111ac
Starting Member

7 Posts

Posted - 2014-01-29 : 05:12:16

I have also this query:

SELECT [Cell_Name] as'column'
,NULL as 'row/tmp'
,[Headcount]as 'column'
FROM CDM_Fact_Organization
INNER JOIN CDM_Dim_Organization on CDM_Fact_Organization.Organization_Key = CDM_Dim_Organization.Organization_Key
for XML PATH('row'),root('data')

Result:

<data>
<row>
<column>Sales</column>
<column>31</column>
</row>
<row>
<column>Production</column>
<column>18</column>
</row>
<row>
<column>marketing</column>
<column>27</column>
</row>
</data>

As you can see the variable is missing..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-29 : 06:41:47

see this illsutration



declare @x table
(
[Cluster_Name] varchar(100),
[Cell_Name]  varchar(50),
[Headcount] int
)

insert @x
values ('Headcount','Sales',31),
('Headcount','Production',18),
('Headcount','Marketing',27)

select [Cluster_Name] AS [@name],
(select [Cell_Name] as [column],
null as tmp,
HeadCount as [column]
from @x
where [Cluster_Name] = t.[Cluster_Name]
for xml path('row'),type) as [*]
from (select distinct [Cluster_Name] from @x)t
for xml path('variable'),root('data')


output
-----------------------------------
<data>
  <variable name="Headcount">
    <row>
      <column>Sales</column>
      <column>31</column>
    </row>
    <row>
      <column>Production</column>
      <column>18</column>
    </row>
    <row>
      <column>Marketing</column>
      <column>27</column>
    </row>
  </variable>
</data>

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-29 : 06:43:50

so in your case it would be this


;With CTE
AS
(
SELECT [Cluster_Name] 
,[Cell_Name] 
,[Headcount]
FROM CDM_Fact_Organization
INNER JOIN CDM_Dim_Organization 
on CDM_Fact_Organization.Organization_Key = CDM_Dim_Organization.Organization_Key
)

select [Cluster_Name] AS [@name],
(select [Cell_Name] as [column],
null as tmp,
HeadCount 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')

also see this

http://visakhm.blogspot.com/2013/12/generating-nested-xml-structures-with.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Bart111ac
Starting Member

7 Posts

Posted - 2014-01-29 : 07:15:04

Woow, many thanks! This is very nice!!!!!

I had never figured it out without you!

Many thanks!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-30 : 06:50:11

you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Bart111ac
Starting Member

7 Posts

Posted - 2014-01-30 : 08:04:07

Hi Visakh16,

The query is perfectly working in SQL Management Studio.
But now I am trying to execute the query in a package in SSIS by this article:
http://www.codeproject.com/Articles/635956/How-to-export-data-from-database-tables-to-an-XML

The SELECT statement must be enclosed in SELECT (...) AS COL_XML to make the output to be text. Otherwise it will look like: System.Byte[]

The problem is that I can't get your query enclosed in SELECT (...) AS COL_XML..

Can you help me out?

Thanks in advance

Regards, Bart

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-31 : 07:30:49

you mean this?


;With CTE
AS
(
SELECT [Cluster_Name] 
,[Cell_Name] 
,[Headcount]
FROM CDM_Fact_Organization
INNER JOIN CDM_Dim_Organization 
on CDM_Fact_Organization.Organization_Key = CDM_Dim_Organization.Organization_Key
)
select
(
select [Cluster_Name] AS [@name],
(select [Cell_Name] as [column],
null as tmp,
HeadCount 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

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Bart111ac
Starting Member

7 Posts

Posted - 2014-01-31 : 08:49:42

Hi visakh16,

Exactly like that! Sorry for being such of a noob

Many thanks! I appreciated

Thanks again!!!!!

Have a nice day

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources