GenerateXML using sql query

See more:

SQL-Server

SQL-Server-2008

Hi,

I am trying to generate a XML file using sql query.

I want my output like this:

<chart caption="Sample caption" showLegend="1" yAxisName="% of Compliance and non compliance" showNames="1" decimalPrecision="0" formatNumberScale="0">
  <categories>
    <category label="2001" />
    <category label="2002" />
    <category label="2003" />
    <category label="2004" />
    <category label="2005" />
  </categories>
  <dataset seriesName="Compliant">
    <set value="25601" />
    <set value="25601" />
    <set value="25601" />
    <set value="25601" />
    <set value="25601" />
  </dataset>
  <dataset seriesName=" Non_compliant">
    <set value="20148" />
    <set value="20148" />
    <set value="20148" />
    <set value="20148" />
    <set value="20148" />
  </dataset>

</chart>

Below is the query I have used

select 
( 
select 
cast(DAY(report_date) as varchar(30)) + '-' + cast(month(report_date) as varchar(10)) + '-' + cast(YEAR(report_date) as varchar(10)) as 'category/@label' 
from mssp_eps_compliance_report
for xml path('categories'),type),

(select COMPLIANT as 'set/@value'
from mssp_eps_compliance_report 
for xml path('dataset'),type),

(select non_compliant as 'set/@value' 
from mssp_eps_compliance_report
for xml path('dataset'),type)

for xml path(''),
Root('chart')

I am getting my output like this.

<chart>
  <categories>
    <category label="9-12-2013" />
  </categories>
  <categories>
    <category label="9-12-2013" />
  </categories>
  <categories>
    <category label="9-12-2013" />
  </categories>
  <categories>
    <category label="9-12-2013" />
  </categories>
  <dataset>
    <set value="82" />
  </dataset>
  <dataset>
    <set value="15653" />
  </dataset>
  <dataset>
    <set value="15446" />
  </dataset>
  <dataset>
    <set value="13486" />
  </dataset>