<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>
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')
<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>
The following code retrieves a list of customer first and last names and stores the data in XML: SELECT (select first_name, last_name) FOR XML PATH('customers'), ROOT('CustomerList')
var
This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)