<%@ Language=VBScript %> <% 'Define variables to store SQL query and the recordset object. Dim strSQL, oRs 'Variables to store the top 5 countries data for 3 years Dim strXML1996, strXML1997, strXML1998 'Initialize the recordset object Set oRs = Server.CreateObject("ADODB.Recordset") '--------------------XML Data for 1996 ------------------------- 'Query the database strSQL = "SELECT TOP 5 Country, SUM(ExtendedPrice) As Total, COUNT(DISTINCT OrderID) As orderNumber FROM Invoices WHERE YEAR(OrderDate)=1996 GROUP BY Country ORDER BY SUM(ExtendedPrice) DESC" oRs.Open strSQL, DB_CONNECTION 'Create the element strXML1996 = "" 'Now iterate through each data row intCounter=0 While not oRs.EOF 'Increase the count intCounter=intCounter+1 'Append the value in format strXML1996 = strXML1996 & "" oRs.MoveNext() Wend 'Entire XML - concatenation strXML1996 = strXML1996 & "" oRs.Close() '--------------------XML Data for 1997 ------------------------- 'Query the database strSQL = "SELECT TOP 5 Country, SUM(ExtendedPrice) As Total, COUNT(DISTINCT OrderID) As orderNumber FROM Invoices WHERE YEAR(OrderDate)=1997 GROUP BY Country ORDER BY SUM(ExtendedPrice) DESC" oRs.Open strSQL, DB_CONNECTION 'Create the element strXML1997 = "" 'Now iterate through each data row intCounter=0 While not oRs.EOF 'Increase the count intCounter=intCounter+1 'Append the value in format strXML1997 = strXML1997 & "" oRs.MoveNext() Wend 'Entire XML - concatenation strXML1997 = strXML1997 & "" oRs.Close() '--------------------XML Data for 1998 ------------------------- 'Query the database strSQL = "SELECT TOP 5 Country, SUM(ExtendedPrice) As Total, COUNT(DISTINCT OrderID) As orderNumber FROM Invoices WHERE YEAR(OrderDate)=1998 GROUP BY Country ORDER BY SUM(ExtendedPrice) DESC" oRs.Open strSQL, DB_CONNECTION 'Create the element strXML1998 = "" 'Now iterate through each data row intCounter=0 While not oRs.EOF 'Increase the count intCounter=intCounter+1 'Append the value in format strXML1998 = strXML1998 & "" oRs.MoveNext() Wend 'Entire XML - concatenation strXML1998 = strXML1998 & "" oRs.Close() %>

Click on the buttons below to change data:

<% 'Destroy objects Set oRs = nothing Set oRsYears = nothing %>