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

[arijit_das]

Hi Bratt,

In our case, we used an asp page which used to :

1. execute one stored procedure and write the result in an xml file in a fixed format supported in xcelsius.

2. once the xml file is generated, the page used to display the embedded swf created in xcelsius.

We used relative path for the xml file in xml data connection.

Regards,

Arijit

[Former Member]

Hi Arijit,

Many thanks for you fast reply.

So this can not be done directly through an SQL query?

Is this: http://xcelsius.files.wordpress.com/2008/05/xml_data_button.pdf  what you mean by asp script ?

Thanks,

Bart

[arijit_das]

You need some script to write the data to a file. This can be a aspx page or jsp or a web service or something else.

Is it possible for you to write a web service to execute the sql and return the data in response ? If yes, you can consume that web service using web service connection/qaaws in xcelsius.

[Former Member]

Thanks,

I think I am not able to write my own web service, Unfortunately I do not have program skills.

I thought it will save me some time if I export the data from SQL server to XML via SSIS. But the required format is really a problem.

Now that I know, I am thinking of making a universe on top of the SQL server and then connect it via the Query Browser.

Do you agree?

Regards,

Bart

[arijit_das]

Our problem was we had Xcelsius Engage which supports max 2 connections and we did not have BO environment.

If you have BOE, then create a universe and connect to it using query browser/qaaws. That will be easy.