XML Output

Hi,

I have posted this in the SQL forum however plenty of views and no replies so I am hoping someone here may be able to shed some light.

I have been trying to tailor a query which will return XML data back ready for use with the 'fusion charts'.

I have so far been 90% successful but a certain additional piece of info needs to be added for this to completely work.

the goal is for the result to appear as follows:

<chart caption='Calls Closed Today'
xAxisName='Specialist' yAxisName='Closed'>
<Set label="JOEB" Value="1" />
<Set label="JANEB" Value="2" />
<Set label="JACKB" Value="1" />
<Set label="JILLB" Value="2" />
<Set label="JOHNB" Value="5" />
<Set label="JULIEB" Value="5" />
<Set label="JAMESB" Value="5" />
</chart>

Now, by running the following query:

SELECT SpecialistID AS label, COUNT(1) AS Value FROM dbo.Request
WHERE ResolutionActual >= (DATEADD(D, 0, DATEDIFF(D, 0, GETDATE())))
AND SpecialistID IN ('JOEB', 'JANEB', 'JACKB', 'JILLB', 'JOHNB', 'JULIEB', 'JAMESB')
GROUP BY SpecialistID
FOR XML RAW ('set'), ROOT ('chart');

This produces the following result:

<chart>
<Set label="JOEB" Value="1" />
<Set label="JANEB" Value="2" />
<Set label="JACKB" Value="1" />
<Set label="JILLB" Value="2" />
<Set label="JOHNB" Value="5" />
<Set label="JULIEB" Value="5" />
<Set label="JAMESB" Value="5" />
</chart>

The item I am missing (as you will see) is the additional code stored within the <chart> element.

How am I able to work this code in? I have briefly looked at EXPLICIT XML output which is more controllable but there is little out there which would assist me with this particular instance.

I would appreciate any help given,
Thanks!

I'm pretty sure this is not "standard" SQL. What database server / version are you using?

Actually that is a perfectly acceptable RAW XML output,

FOR XML RAW ('set'), < here I am re-labelling the rows to display as 'set'.

ROOT ('chart');

the default for 'root' is to appear as <root> xml_code </root>

I have simply specified this to appear as 'chart'.

My guess is that RAW output may not be the way forward for me, it may be that EXPLICIT or PATH might need to be used but that involves re-writing everything and there is suprisingly small ammount of information about on this subject.

EDIT: to answer your question - MS SQL server 2008

I was able to infer what it meant, but that doesn't mean it's standard SQL. Your previous thread was posted in the MySQL forum, and since MySQL doesn't have anything like "FOR XML" it's not surprising that you didn't get any replies there.

I've never used MSSQL before, but some googling revealed that you might be able to do it with a nested query:
http://www.sqlservercentral.com/For...05934-21-1.aspx