<%@ Language=VBScript %> <% 'Define database objects 'oRs - Recordset object 'oRsYears - To store the list of years 'strSQL - String variable to contain the SQL query 'intCounter - Numeric value to keep a track of number of records 'intYear - Numeric value to keep index of year Dim oRs, oRsYears, strSQL, intCounter intCounter = 0 'Initialize the database objects Set oRs = Server.CreateObject("ADODB.Recordset") Set oRsYears = Server.CreateObject("ADODB.Recordset") 'strXMLData - String variable to contain the entire XML data document for the chart 'strCategories - String variable to store the element and it's child elements. 'strDataSet - String variable to store the and Dim strXMLData, strCategories, strDataSet 'Initialize the element strXMLData = "" & vbCrlf 'Generate the category elements - category elements are the x-axis labels (5 countries in our example) strCategories = "" strDataSet = "" 'Get a list of all the years strSQL = "SELECT DISTINCT YEAR(OrderDate) As Year FROM Orders ORDER BY 1" oRsYears.Open strSQL, DB_CONNECTION While not oRsYears.EOF 'Increment the counter intCounter = intCounter + 1 'Get the year intYear = oRsYears("Year") 'Generate the string for this year. strDataSet = strDataSet & "" & vbCrlf 'Generate the SQL Query to retrieve data for this year. strSQL = "SELECT Country, SUM(ExtendedPrice) As Total, COUNT(DISTINCT OrderID) As orderNumber FROM Invoices WHERE Country In ('USA','Germany','Austria','Brazil','France') and Year(OrderDate)=" & intYear & " GROUP BY Country ORDER BY Country" 'Retrieve the recordset oRs.Open strSQL, DB_CONNECTION 'Now generate the elements for this dataset in the format While not oRs.EOF strDataSet = strDataSet & "" & vbCrLf oRs.MoveNext() Wend 'Close this oRs oRs.Close() 'Close the dataset strDataSet = strDataSet & "" & vbCrLf oRsYears.MoveNext() Wend 'Generate the XML document by concatenating the strings we've generated. Also, close the element. strXMLData = strXMLData & strCategories & strDataSet & "" 'Destroy the recordset objects Set oRsYears = nothing Set oRs = nothing 'Write the XML data to output stream Response.Write(strXMLData) %>