SQL Query for XML (row/column format)

[Former Member]

Hi Experts,

Can somebody help me out with my XML file format?

For consuming it in Xcelsius it needs to be in a row/column format.

I would like to use a SQL query that I can run on my SQL Server 2008 R2 database with the outcome that Xcelsius can read..

Has anyone experience with this?

Thanks in advance.

Regards,

Bart

[Former Member · Accepted solution]

For the ones who have the same problem, here is a SQL script to the desired XML format (variable, row / column)

USE <DATABASENAME>

;With CTE

AS

(

SELECT [ColumnA]

,[ColumnB]

,[ColumnC]

FROM TABEL

)

select [ColumnA] AS [@name],

(select [ColumnB] as [column],

null as tmp,

ColumnC as [column],

from CTE

where [ColumnA] = t.[ColumnA]

for xml path('row'),type) as [*]

from (select distinct [ColumnA] from CTE)t

for xml path('variable'),root('data')

In this script the variable is named by the value of ColumnA.

Regards,

Bart