XML and ODBC

Chapter 10 - Data Sources and the Database Expert
Mastering Crystal Reports 9
by Cate McCoy and Gord Maric
Sybex 2003

Extensible Markup Language (XML) files are becoming a popular medium for transferring data from one system to another. These files have a hierarchical structure that allows the data to be treated relationally as rows and columns through an ODBC interface. Crystal Reports can both read from and export to an ODBC XML data source. Figure 10.29 shows the structure of a typical XML file as viewed through Internet Explorer. This particular example file is a set of books. In relational terms, here’s how such a file will be interpreted:

• Table: The set of all books represented in the file

• Row: An individual book within the file

• Column: The information used to describe the book, such as title and author

  
  Figure 10.29. An XML file structure

Using XML As a Data Source

Crystal Reports 9 ships with the Crystal Reports XML ODBC driver that can be used to access an XML document. A single document or set of XML documents is a valid Crystal Reports data source. Configuring the connection requires choosing the driver as a system DSN as well as installing the XML driver on your system. Although Crystal ships with the driver, it is not actually installed on your system until the first time you create a data source that uses the driver. To install the driver, refer back to Figure 10.4 and you’ll see the Add button. After clicking this button, choose the CR ODBCXML Driver 4.10 from the list of drivers, and then click Finish, as shown in Figure 10.30.

Figure 10.30. Installing the XML ODBC driver

On the General tab of the ODBC XML Driver Setup dialog, do the following:

1. Type a meaningful data source name.

2. Add an optional description.

3. Click the Add button to find the XML file.

Clicking the Add button displays the Configure Location dialog shown in Figure 10.31. Enter the Location Name field in the free-form typing area. Choosing the location itself, on the other hand, involves clicking a Location Type radio button and then clicking the Location browse button (an ellipsis appears on the button). The location can be a URL pointing to a folder containing the XML documents (choose the Folder radio button), a single XML file, or a single HTML document. You can either type the path for a file use or the browse button to locate it. Figure 10.31 shows this task completed.

Tip 

When you use a URL, the Location field needs to specify the name of the folder containing the XML file(s) and not the XML file(s) themselves.

Figure 10.31. Configuring an XML location

You can test the ODBC connection from this dialog by clicking the Test Connect button. If all goes well, you should see a message that the test connect succeeded. After testing the connection, you can click the Advanced tab, shown in Figure 10.32, to configure additional options if required for your application. The Advanced options include thefollowing:

• Adding an Extensible Stylesheet (XSL) reference for the table (table hint)

• Adding an XSL reference for the row (row hint)

• Validating the schema (business rules) found in the XML document against another file that contains the business rules

• Requiring a user ID and password to use the data source

  
  Figure 10.32. Advanced options for XML configuration

The table and row hints will be required if the XML document contains a hierarchy that is more than one level deep because the CR ODBCXML driver supports only one hierarchy level. The sample XML file shown in Figure 10.29 is only one level deep, with the hierarchy being bookstore. You can add this to the Advanced tab even though it’s only one level deep, and if you had more levels, you would simply add a forward slash before typing the next level name after the previous level name. The table hint identifies the <bookstore> hierarchy as the table, while the row hint identifies the <book> tag as the row within the table. In Figure 10.32, the row hint contains a period before the forward slash; this indicates that the book is an element in the XML file (as opposed to an attribute). When you change a table or row hint, you are essentially changing the structure of the data source, so you’ll need to log out of the data source and log back in before the new hints will be applied to the data. To log on or off a data source, choose Database > Log On Or Off Server.

Tip 

XML is a case-sensitive language, therefore the text of table and row hints must match the case used in the XML document exactly.

Once the data source is configured at the ODBC level, back in Crystal Reports you simply choose to connect to an ODBC (RDO) data source and select the DSN you created to point to the XML documents. Figure 10.32 shows the Database Expert with an XML ODBC source selected.

Figure 10.33. The Database Expert with an XML data source

Creating XML from an RPT File

Crystal Reports can also be used to create XML documents. To do this, choose File  Export to export data from the Details section of an RPT file to an XML ODBC data source. There are three formats that Crystal can use to create XML documents, and these are visible as radio button choices in the Table Creation area in Figure 10.32. These three choices represent the three different types of XML files that Crystal knows how to read as well to write. Table 10.4 describes these three choices.

Table 10.4: Valid XML Formats for Crystal Reports

Table Creation Choice	Description
Data Island Format	Choosing this option means that you have an XML document that represents the data as well as a second document that specifies the structure. The second file is a Data Definition document. Both files must be stored in the same file directory folder.
ADO Format	Choosing this option means that you have an XML document(s) that contains data, but within that same file a schema structure is also stored.
DataDirect Format	Choosing this option means that you have an XML document (or set of documents) that contains the data, and no external data definition document exists.

If your report is intended to be XML output, you can use the XML Expert (Report > XML Expert) to format the XML element tags and attributes. The expert allows you to specify the Crystal ML schema used to identify the components of an XML document as well as apply external XSD (XML Schema Definition) or DTD (Document Type Definition) validations.

Note 

XSD is an XML-based grammar that describes the structure of an XML document and can therefore be used to validate it. Likewise, DTDs do the same thing, but a DTD is not based on XML grammar.

Use of content on this site is expressly subject to the restrictions set forth in the Membership Agreement

Conello © 2000-2003     Feedback