RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


Build an XML Data Extension for SQL Server Reporting Services : Page 4

Get to know the main features of SQL Server Reporting Services and learn how to extend it with an XML Data Provider Extension.

Implementation Details
The source code download that accompanies this article provides a fully functional XML data provider extension. Most of the data extension provider implementation consists of boilerplate code, so let's focus on the most relevant implementation details.

The XMLConnection class implements the IDBConnectionExtension interface. The connection string syntax I've defined requires only an XMLDIRPATH parameter where the directory containing the XML files is specified (see Figure 3).

Figure 3. Define the XML Data Provider Connection String: The first step to setting up a report with the XML Data Provider is to define a connection string pointing to a file system directory.

In the XMLCommand class, I defined a command-text syntax that lets you define the XML file name containing the data and an XPATH syntax that maps a generic XML structure into a tabular form. I decided to provide the file name as a command parameter named FileName. For the XPath syntax, I opted for an approach very similar to the OPENXML command in SQL Server 2000. It indicates the row selection criteria and, within the select XML nodes, the field's selection criteria. The query syntax is composed of three parts:

  1. <row selection XPATH criteria>
  2. <attr/elem/mix>
  3. <fields selection list>
The field's selection list is optional if you specify attr or elem in the second part. Using attr as the second parameter instructs the data provider extension to consider as row fields all the attributes of the XML nodes element that the row selection criteria extract. Using the elem option instructs the data provider extension to consider as row fields all the child elements of the XML nodes element that the row selection criteria extract. The following code uses attr:

 <book id="2" name="john" title="SQL Server Programming"/>
 <book id="3" name="mary" title=".NET Programming"/>

XML provider query text: /books/book,attr
You must specify mix as the second argument if fields are placed in an irregular mixed path relative to the row element. Notice that the fields in the selection expression are separated by the pound sign (#):

 <author name="john">
  <book id="2">
   <title> SQL Server Programming </title>
<author name="mary">
  <book id="3">
   <title>.NET Programming </title>

XML provider query text: /books/book,mix,@id#title#../@name
Most of the XML data's functionalities are implemented in the XMLDataReader internal constructor, which is called by the XMLCommand's ExecuteReader method. This constructor accepts a connection and a command object. The constructor implementation prepares the object internal state (using an XPathNavigator object) for subsequent calls to the Read method, which RS uses to pull data out of the data source (see Listing 2).

Note that the XMLDataReader class also is instantiated when the VS.NET designer tries to determine the field's number, name, and type. In this case, the Read method won't receive any subsequent calls. In the current implementation, I have no way to determine the row structure other than executing the actual XPath command (and assuming that the first row's structure is the same for all the rows set). You could extend the sample to take XML schemas into account. With an XML schema, the interaction between the XMLCommand and the XMLDataReader classes can be re-factored to move XPath query executions to the top of the Read method implementation.

Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date