Article ID: 286212 - Last Review: February 22, 2007 - Revision: 2.7

HOWTO: Use an XML DataSource with the Office Chart Component

View products that this article applies to.

This article was previously published under Q286212

SUMMARY

This article illustrates how you can bind the Office Chart component to an ActiveX Data Objects (ADO) recordset that is persisted in Extensible Markup Language (XML).

Back to the top

MORE INFORMATION

You can bind to a recordset that is persisted in XML in one of two ways:

Use the Microsoft OLEDB Persistence Provider to bind to an XML stream that contains the persisted recordset. This technique is useful when the XML is generated with Active Server Pages (ASP) or when the XML is stored in a file. -or-
Set the DataSource property for the ChartSpace to an ADO recordset that is opened from an ADO stream that contains the XML data. You can use this approach to access a persisted recordset that is stored in an XML data island on the same Web page that hosts the Chart component.

Both of these techniques are demonstrated in the following samples.

Using XML Data Generated with ASP for a Chart DataSource

Save the following code as GetXMLData.asp in the virtual root folder of your Web server. By default, the virtual root folder is C:\Inetpub\Wwwroot. If needed, modify the path to the sample Northwind.mdb Access database to match your installation of Office.

<% Language="VBScript" %>

<%
    Response.ContentType = "text/xml"
    Response.Buffer = true
    
    Dim oRS, XMLDom
    Set oRS = CreateObject("ADODB.Recordset")
    oRS.Open "select * from [Category Sales for 1997]", _
             "Provider=Microsoft.Jet.OLEDB.4.0;" & _
             "Data Source=c:\program files\microsoft office\office10\samples\northwind.mdb", 3, 1
    set XMLDom = CreateObject("MSXML.DOMDocument")
    oRS.Save XMLDom, 1 
    Response.Write XMLDom.XML
    oRS.Close

    Response.Flush
    Response.End
%>

Save the following HTML code as ChartXML1.htm in the virtual root folder of your Web server. Change YourWebServer in the script to the name of your Web server.

<html>

<body>

<object classid="clsid:0002E556-0000-0000-C000-000000000046" id="CSpace" width="576" height="384">
</object>

</body>

<script language="VBScript">

   'Bind the chart to the recordset.
   Dim c
   Set c = CSpace.Constants
   CSpace.ConnectionString = "Provider=MSPersist"
   CSpace.CommandText = "http://YourWebServer/getxmldata.asp"
   CSpace.HasMultipleCharts = False
   CSpace.PlotAllAggregates = c.chPlotAggregatesSeries
   CSpace.SetData c.chDimCategories, c.chDataBound, "CategoryName"
   CSpace.SetData c.chDimValues, c.chDataBound, "CategorySales"

</script>
             
</html>

Start Microsoft Internet Explorer and browse to http://YourWebServer/ChartXML1.htm, where YourWebServer is the name of your Web server. When the Web page loads, the script binds the chart to the XML stream that is returned from GetXMLData.asp. The resulting chart contains one series that plots category sales information from Northwind.mdb.

Using an XML Data Island for a Chart DataSource

Save the following code as ChartXML2.htm:

<html>

<body>

<object classid="clsid:0002E556-0000-0000-C000-000000000046" id="CSpace" width="576" height="384">
</object>

<xml xmlns:s='uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882'
	xmlns:dt='uuid:C2F41010-65B3-11d1-A29F-00AA00C14882'
	xmlns:rs='urn:schemas-microsoft-com:rowset'
	xmlns:z='#RowsetSchema'>
<s:Schema id='RowsetSchema'>
  <s:ElementType name='row' content='eltOnly'>
    <s:AttributeType name='CategoryName' rs:number='1' rs:maydefer='true' rs:writeunknown='true'>
      <s:datatype dt:type='string' dt:maxLength='15'/>
    </s:AttributeType>
    <s:AttributeType name='CategorySales' rs:number='2' rs:nullable='true' rs:maydefer='true' rs:writeunknown='true'>
      <s:datatype dt:type='number' rs:dbtype='currency' dt:maxLength='8' rs:precision='19' rs:fixedlength='true'/>
    </s:AttributeType>
    <s:extends type='rs:rowbase'/>
  </s:ElementType>
</s:Schema>
<rs:data>
  <z:row CategoryName='Beverages' CategorySales='102074.29'/>
  <z:row CategoryName='Condiments' CategorySales='55277.56'/>
  <z:row CategoryName='Confections' CategorySales='80894.11'/>
  <z:row CategoryName='Dairy Products' CategorySales='114749.75'/>
  <z:row CategoryName='Grains/Cereals' CategorySales='55948.82'/>
  <z:row CategoryName='Meat/Poultry' CategorySales='81338.06'/>
  <z:row CategoryName='Produce' CategorySales='53019.98'/>
  <z:row CategoryName='Seafood' CategorySales='65544.19'/>
</rs:data>
</xml>

</body>

<script language="VBScript">

   'Obtain a string that represents the XML data island.
   Dim oXML, sXML
   Set oXML = document.body.getElementsByTagName("xml")
   sXML = oXML(0).OuterHTML

   'Load the XML "string" into an ADO recordset.
   Dim oRS, oStream
   Set oRS = CreateObject("ADODB.Recordset")
   Set oStream = CreateObject("ADODB.Stream")
   oStream.Open
   oStream.WriteText sXML
   oStream.Position = 0
   oRS.Open oStream

   'Bind the chart to the recordset.
   Dim c
   Set c = CSpace.Constants
   Set CSpace.DataSource = oRS   
   CSpace.HasMultipleCharts = False
   CSpace.PlotAllAggregates = c.chPlotAggregatesSeries
   CSpace.SetData c.chDimCategories, c.chDataBound, "CategoryName"
   CSpace.SetData c.chDimValues, c.chDataBound, "CategorySales"

</script>
             
</html>

Start Internet Explorer and browse to ChartXML2.htm. When the Web page loads, the script binds the chart to the recordset that is persisted in the XML data island and plots one series for category sales.

Back to the top

REFERENCES

For additional information, click the following article numbers to view the articles in the Microsoft Knowledge Base:

288907 (http://support.microsoft.com/kb/288907/ ) INFO: Binding the Office XP Chart Component to a Data Source

286278 (http://support.microsoft.com/kb/286278/ ) HOWTO: Create an Interactive Office XP Chart Server-Side Using ASP

For more information, see the following Microsoft Web sites:

Using Office Web Components
http://support.microsoft.com/ofd (http://support.microsoft.com/ofd)

Microsoft Office Developer Center
http://msdn.microsoft.com/office/ (http://msdn.microsoft.com/office/)

Saving ADO Recordsets in XML Format

For additional information about using an XML data source with the Office 2000, click the following article number to view the article in the Microsoft Knowledge Base:

249805 (http://support.microsoft.com/kb/249805/ ) HOWTO: Use XML Data with the Chart Component

Back to the top