Crystal Reporting with XML

The strong expansion of Internet commerce and the proliferation of proprietary data types are combining to produce an increasing need for platform-independent data transfer. A few years ago, a consortium of organizations began collaborating on developing a standard language called Extensible Markup Language, or XML. XML is a format for defining, transmitting, validating, and interpreting data across the Internet, or through electronic file transfers, among independent systems and industries.

While XML has similarities to HTML (Hypertext Markup Language, used widely for Internet applications), it has significant differences and advantages. HTML stores static information and presents it in a static format. XML files can contain a data set, but they also can contain or be accompanied by instructions on how to interpret the file content so that the end user or application can choose how to utilize it. Predictably, several different groups are continuing to attempt to establish a dominant, widely accepted XML standard, and several standards are emerging. Some standards are focused on certain industries, such as banking or retail, while other standards are intended to be industry independent.

An XML file usually either contains data interpretation instructions (in a prolog between the file header and the data content) or is accompanied by an instruction document that defines the XML file content. Generally, these instructions are referred to as a schema, though some of the types of schema also use Schema in their name to confuse the issue. The three most common types of instructions are

• The Document Type Definition (DTD), developed early on primarily for validation of XML file data rather than formatting; DTD instructions can reside within the XML file or as a separate .DTD file; they use a different language syntax than XML.

• The XML Schema, developed by the World Wide Web Consortium (W3C) as an international standard. XML Schema instructions reside in an .XML file and use XML syntax.

• The Extensible Stylesheet Language (XSL), which has begun to replace DTD due to its broader format-oriented syntax. XSL instructions can reside within the XML file or in a separate .XSL file.

To use an XML file as a source for a report, Crystal Reports includes ODBC drivers. Crystal Reports automatically sets up the CRXML ODBC data source using an updated version 4 driver, which allows you to connect to different types of XML files for reporting.

Note  

This XML ODBC driver, which is a Merant DataDirect product, has some limitations that may preclude connecting to your XML file. For more information about these limitations, as well as general information about this ODBC driver, see XML data, accessing in Crystal Reports online help.

Figure 20-7 shows an XML file exported from a personal video library database program, to which the CRXML ODBC driver can connect. The file consists of a DVD collection data, with tags identifying the different data elements.

Crystal Reports CRXML ODBC data source will probably need to be configured properly before creating your first report based on XML data. Configure this data source by using the ODBC Data Source icon in Windows Control Panel. Once you ve chosen various options particular to your environment, such as locations for the XML data files and schemas they conform to, you can return to Crystal Reports and begin the report design process.

To report on a supported XML file, start a new report just as you would for a standard database. You can use the report wizards or the Blank Report option. Expand the Create New Connection section of the Available Data Sources list, and then expand the ODBC (RDO) entry. From the Data Source Selection dialog box, choose the CRXML data source name (or other data source name that you defined for XML reporting). The ODBC XML Driver Connection dialog box appears. If you specified a user ID and password requirement when you defined the ODBC data source, specify them on this dialog box.

Once you ve completed this dialog box, the Database Expert will return with a new database entry appearing under the ODBC category. If you expand the database and subsequent entries, you ll eventually see a table name that corresponds to the XML data that the ODBC data source refers to.

Caution  

Depending on how you set up the ODBC Data Source, you may see multiple XML tables listed. However, they will not necessarily return valid data to Crystal Reports if they are not organized in a supported XML format.

Select the desired XML file from the list and add it to the report (by double-clicking the table name, by dragging the table name to the Selected Tables list, or by selecting the table name and clicking the right arrow). If you add multiple tables, link them on the Links tab. Then, close the Database Expert. The list of fields you can report from will appear in the expert or in the Field Explorer.