SQL 2000 Server provided T-SQL language extensions to operate bi-directionally with relational and XML sources. It also provided two system stored procedures, sp_XML_preparedocument and sp_XML_removedocument, that assist the XML to Relational transformation. This support for returning XML data from relational data using the For XML clause is continued in SQL Server 2005 and SQL Server 2008 although the support for XML is lot more extensive. The shape of the data returned by the For XML clause is further modified by choosing the following modes, raw, auto, explicit, or path. As a preparation for this article we will be creating an XML document starting from the PrincetonTemp table used in a previous article, Binding MS Chart Control to LINQ Data Source Control, on this site.
Creating an XML document from an SQL Table
Open the SQL Server Management and create a new query [SELECT * from PrincetonTemp for XML auto]. You can use the For XML Auto clause to create a XML document (actually what you create is a fragment - a root-less XML without a processing directive) as shown in Figure 01.
Figure 01: For XML Auto clause of a SELECT statement
The result shown in a table has essentially two columns with the second column containing the document fragment shown in the next listing.
<PrincetonTemp Id="1" Month="Jan " Temperature="4.000000000000000e+001" RecordHigh="6.000000000000000e+001"/>
<PrincetonTemp Id="2" Month="Feb " Temperature="3.200000000000000e+001" RecordHigh="5.000000000000000e+001"/>
<PrincetonTemp Id="3"Month="Mar " Temperature="4.300000000000000e+001" RecordHigh="6.500000000000000e+001"/>
<PrincetonTemp Id="4" Month="Apr " Temperature="5.000000000000000e+001" RecordHigh="7.000000000000000e+001"/>
<PrincetonTemp Id="5" Month="May " Temperature="5.300000000000000e+001" RecordHigh="7.400000000000000e+001"/>
<PrincetonTemp Id="6" Month="Jun " Temperature="6.000000000000000e+001" RecordHigh="7.800000000000000e+001"/>
<PrincetonTemp Id="7" Month="Jul " Temperature="6.800000000000000e+001" RecordHigh="7.000000000000000e+001"/>
<PrincetonTemp Id="8" Month="Aug " Temperature="7.100000000000000e+001" RecordHigh="7.000000000000000e+001"/>
<PrincetonTemp Id="9" Month="Sep " Temperature="6.000000000000000e+001" RecordHigh="8.200000000000000e+001"/>
<PrincetonTemp Id="10" Month="Oct " Temperature="5.500000000000000e+001" RecordHigh="6.700000000000000e+001"/>
<PrincetonTemp Id="11" Month="Nov " Temperature="4.500000000000000e+001" RecordHigh="5.500000000000000e+001"/>
<PrincetonTemp Id="12" Month="Dec " Temperature="4.000000000000000e+001" RecordHigh="6.200000000000000e+001"/>
This result is attribute-centric as each row of data corresponds to a row in the relational table with each column represented as an XML attribute.
The same data can be extracted in an element centric manner by using the directive elements in the SELECT statement as shown in the next figure.
Figure 02: For XML auto, Elements clause of a Select statement
This would still give us an XML fragment but now it is displayed with element nodes as shown in the next listing (only two nodes 1 and 12 are shown).
To make a clear distinction between the results returned by the two select statements the first row of data is shown in blue. This has returned elements and not attributes. As you can see the returned XML still lacks a root element as well as the XML processing directive.
To continue with displaying this data in MS Chart Save Listing 2 as PrincetonXMLDOC.xml to a location of your choice.
Create a Framework 3.5 Web Site project
Let us create a web site project and display the chart on the Default.aspx page. Open Visual Studio 2008 from its shortcut on the desktop. Click File New | Web Site...|(or Shift+Alt+N) to open the New Web Site window. Change the default name of the site to a name of your choice (herein Chart_XMLWeb) as shown. Make sure you are creating a .NET Framework 3.5 web site as shown here.
Figure 03: New Framework 3.5 Web Site Project
Click on APP_Data folder in the solution explorer as shown in the next figure and click on Add Existing Item… menu item.
Figure 04: Add an existing item to the web site folder
In the interactive window that gets displayed browse to the location where you saved the PrincetonXMLDOC.xml file and click Add button. This will add the XML file to the ADD_Data folder of the web site project.
Double click PrincetonXMLDOC.xml in the web site project folder to display and verify its contents as shown in the next figure. Only nodes 1 and 12 are shown expanded. As mentioned previously this is an XML fragment.
Figure 05: Imported PrincetonXMLDOC.xml
Modify this document by adding the <root/> as well as the XML processing instruction as shown in the next figure. Build the project.
Figure 06: Modified PrincetonXMLDOX.xml (valid XML document)
Binding the chart to XML data
Drag and drop a Microsoft Chart control from Toolbox under Data to the Default.aspx page as in the previous cited article. Drag and drop a button and change its Text property to Display Chart as shown.
Figure 07: Chart1 on Default.aspx page
Double click the button and to the button's click event insert the listing shown in the page's code.
Listing 03: Code for the button's click event
Partial Class _Default
Protected Sub Button1_Click(ByVal sender As Object, _
ByVal e As System.EventArgs) Handles Button1.Click
Dim ds As New DataSet
'Read xml data to the dataset
ds.ReadXml("C:Documents and SettingsJayaram Krishnaswamy" & _
"My DocumentsVisual Studio 2008Chart_XMLWeb" & _
'code used for verifying necessary strings
Chart1.DataSource = ds
Chart1.Series("Series1").XValueMember = _
Chart1.Series("Series1").YValueMembers = _
Chart1.Series("Series2").XValueMember = _
Chart1.Series("Series2").YValueMembers = _
Chart1.Series(0).Color = Drawing.Color.DarkRed
Chart1.Series(1).Color = Drawing.Color.RoyalBlue
Chart1.ChartAreas("ChartArea1").AxisX.Interval = 1
Build the web site project and browse the page on the browser. In the web page that gets displayed click on the Display Chart button. The chart gets displayed as shown in the next figure.
The dataset gets the XML document using ReadXML(). All you need to do is to point to the correct location of the XML Document. The information is parsed and provided to the proper components of the chart(Series and Columns). The commented code was used to verify that the proper association is made to the chart components.
Figure 08: PrincetonTemp Chart
While modifying the imported XML file make sure that you add the <root/> element otherwise you will encounter an XML exception as shown.
Figure 09: XML Exception
Although we started off with an element-centric XML the ReadXML() method provides the proper nodes of the XML for the chart even for attribute-centric XML Documents.
Adding titles to chart
Although data is central to a chart,titles are absoutely necessary. They can be added at design time using the properties of the chart. They can be added at run time so as to customize them. In the next subsections we will add a title to the chart and the axes as well as set the minimum and maximum values for the Y-axis.
Adding a title to the chart
The chart's title belongs to the titles collection. You first need to add the title element and then format the same providing a text; positional and size information. The following listing shows code used to add a title to the chart. The chart size was increased to 400x400 from its default 300x300 size.
Listing 4: Adding X and Y axes titles to basic chart
Chart1.Height = 400
Chart1.Width = 400
Chart1.ChartAreas("ChartArea1").AxisY.Minimum = 30
Chart1.ChartAreas("ChartArea1").AxisY.Maximum = 90
.Text = "Princeton Temperature"
.ForeColor = Drawing.Color.BlueViolet
.TextStyle = DataVisualization.Charting.TextStyle.Emboss
.Font = New Drawing.Font("Broadway", 14, Drawing.FontStyle.Bold)
.Alignment = Drawing.ContentAlignment.TopCenter
.Position.Width = 100
.Position.Height = 25
Adding titles to X and Y axis (shown for X axis)
Chart axes titles are essential for any chart. The chart axes are a property of the chart areas in the MS Chart Control. All axes properties are easily accessible as shown in the next figure. For a Column type chart there are two X and two Y axes.
Figure 10: Chart axes properties
Code for adding and formatting the axes
The code shown in the following listing will add the titles for the X and Y axes for the basic columnar chart of Figure 09. The code snippet can be inserted in the click event of the button.
Listing 5: Adding X and Y axes titles to basic chart
.AxisX.Title = "Month"
.AxisX.TitleFont = New Drawing.Font("Broadway", 12, Drawing.FontStyle.Bold)
.AxisX.TitleForeColor = Drawing.Color.DarkMagenta
.AxisY.Title = "Temperature in Deg F"
.AxisY.TitleFont = New Drawing.Font("Verdana", 12, Drawing.FontStyle.Bold)
.AxisY.TitleForeColor = Drawing.Color.DarkMagenta
Setting the maximum and minimum values for an axis
Sometimes it may be necessary to change the displayed maximum and minimum values for the axes in order to better bring out the details and reduce the chart size. For example the program has used the default of 0 and 100 as the minimum and maximum values for the graph in Figure 08. This could be changed using the Maximum and Minimum properties of the axes as shown in the next listing.
Listing 6:Maximum and Minimum of the axes
Chart1.ChartAreas("ChartArea1").AxisY.Minimum = 30
Chart1.ChartAreas("ChartArea1").AxisY.Maximum = 90
Adding all the titles and setting the minimum and maximum as above the chart gets rendered as shown in the next figure.
Figure 11: Chart with axes and chart titles added
Basic Chart Code in C#
Add a web page SharpChart.aspx with language attribute for the page as C#. Use the existing PrincetonXMLDOC.xml file as the source of data. Add a MS Chart control from the Toolbox on to the SharpChart.aspx page. Add a button control as well. To the click event of the button add the code shown in Listing 6.
public partial class SharpChart : System.Web.UI.Page
protected void Page_Load(object sender, EventArgs e)
protected void Button1_Click(object sender, EventArgs e)
DataSet ds = new DataSet();
ds.ReadXml("C:Documents and SettingsJayaram KrishnaswamyMy DocumentsVisual Studio 2008Chart_XMLWebApp_DataPrincetonXMLDOC.xml");
string colname = Convert.ToString(ds.Tables.Columns.ColumnName);
Chart1.Series["Series1"].XValueMember = colname;
Chart1.Series["Series2"].XValueMember = colname;
Chart1.Series["Series1"].YValueMembers = Convert.ToString(ds.Tables.Columns.ColumnName);
Chart1.Series["Series2"].YValueMembers = Convert.ToString(ds.Tables.Columns.ColumnName);
Chart1.ChartAreas.AxisX.Interval = 1;
Chart1.ToolTip=("Princeton's current year and record high temperatures");
Chart1.DataSource = ds;
The extra item you see in this code is Chart's tooltip property that displays "Princeton's current year and record high temperatures" when you hover over the chart.
Binding the MS Chart Control to XML data was carried out using code. Creating a dataset is all that is necessary. The XML data is obtained from an XML Document which is read with the ReadXML() method. Chart area's properties were explored while adding X and Y axes titles to the chart. Also the chart title and the range of Y axis values were set using code.
If you have read this article you may be interested to view :
- Binding MS Chart Control to LINQ Data Source Control
- Displaying SQL Server Data using a Linq Data Source
- MySQL Linked Server on SQL Server 2008
- Displaying MySQL data on an ASP.NET Web Page
- Exporting data from MS Access 2003 to MySQL
- Transferring Data from MS Access 2003 to SQL Server 2008