Extensible Markup Language (XML) is a World Wide Web Consortium (W3C) standard, first proposed in 1998, for defining data markup languages that describe a document's content and logical structure. XML documents consist of data values and "tags" (markup) that describe the data, for example, <phone>123-456-7890</phone>. Using XML, businesses can specify a wide variety of data using a format that applications running on any platform can easily import and process. XML is increasingly being used by companies and government agencies as an internal data format for capturing data from electronic forms (W3C standard XForms is an example) and for modeling their business.
In addition, many XML-based industry standards have been developed to provide a common format to simplify the exchange and processing of data across and within companies. Some examples of these standards, in a variety of industries, are Association of Cooperative Operations Research (ACORD) in insurance, Financial Information eXchange Markup Language (FIXML) in finance, Health Level 7 (HL7) in healthcare, National Information Exchange Model (NIEM) for government agencies, Association for Retail Technology Standards (ARTS) for retail, Universal Business Language (UBL) for business documents such as invoices, and Modernized e-File (MeF) for tax filing.
Initially, many businesses treated XML as an information exchange format only, constructing it for sending and deconstructing upon receipt to extract the data they wanted. However, over time, as XML has grown in frequency of usage and in size and complexity, it has become a more critical component of business operations. As a result, many businesses are now keeping the XML intact and storing it for further processing and analysis. Due to the critical value of the XML data, these companies are turning to relational databases to store and manage this data, control access, manage backup and recovery processes, and enable application interaction for business processing and reporting. The XML data management choices are briefly reviewed in the next section.
Historically, businesses that wanted to process and store XML had limited options. They either serialized (made into a giant string) the full XML content and stored it in a file system or a database large object (CLOB or BLOB) column or parsed the XML and extracted specific elements discarding the rest, storing the extracted items in columns in relational tables. While the first option met regulatory requirements, it was highly inefficient, since each access of the data required a transfer of the whole document for reparsing by an application in order to get to the content. The latter approach caused a loss of the context in the original XML document as well as loss of the discarded content. Both approaches led to inefficiencies and considerable application overhead during runtime from constantly transferring, processing, and assembling or disassembling the XML documents.
With the introduction of pureXML in DB2 9, a new option for storing XML emerged that provides advantages over the alternatives for most applications. DB2 9 pureXML enables storing the XML in its native hierarchical format without requiring application manipulation of the XML to insert or retrieve. Subsets of the XML documents, even individual elements and attributes, can be queried and retrieved. By efficiently managing XML, labor-intensive document decomposition and reconstruction processes can be avoided yet the value of the XML content is made available for processing. See "A performance comparison of DB2 9 pureXML with CLOB and shredded XML storage", listed in the
Resources section of this document, for a review of the performance results of the different approaches for managing and querying XML in a database.
DB2 pureXML introduces the ability to natively store XML hierarchical data within a hybrid database that understands XML as well as relational data. Native XML storage means storing XML data intact with full database knowledge of each document's internal hierarchical structure and individual elements and attributes. With pureXML, businesses can efficiently store, control, query, and update XML data alongside existing data stored in tables.
The XML data in DB2 pureXML is presented as a column of type XML, which can coexist in a table with traditional relational columns. DB2 extends its common services, such as optimization, indexing, concurrency control, and utilities, across the traditional relational and XML column data. DB2 supports queries written in industry-standard SQL or XQuery languages, as well as "hybrid" queries that contain both SQL and XQuery expressions (sometimes referred to as SQL/XML), to access and manipulate the XML data. This hybrid database capability is shown in Figure 1. With pureXML, DB2 9 provides the facilities to store XML in large objects, decomposed to relational columns, or as XML. Increasingly, customers are choosing the native XML storage option of pureXML to take advantage of its greater flexibility and reduced application overhead. This article focuses on the ability to report on the XML data stored natively in XML columns in DB2.
Figure 1. DB2 architecture with built-in support for relational and XML data
DB2 pureXML provides the capability to dynamically transform XML data into result sets made up of columns with traditional SQL data types for applications that only deal with discrete character or numeric data elements, such as relational data columns. The simplest way to do this is through an SQL function called XMLTABLE, which concisely describes how to retrieve XML data in relational (or table) format. This is one way in which reporting tools, such as DataQuant can work directly with DB2 pureXML data, as described below. For more information about DB2 pureXML, including XMLTABLE, see the
Resources section at the end of this article.
Note: pureXML is available on DB2 9 for Linux®, UNIX®, and Windows® and DB2 9 for z/OS®, and DataQuant supports both DB2 systems. This article uses DB2 for Linux, UNIX, and Windows in its examples.
DataQuant is an all-purpose Business Intelligence (BI) tool from IBM, based upon the Eclipse open development platform and available on a variety of systems including IBM System Z®, Linux, Microsoft® Windows, IBM AIX®, and Solaris. DataQuant provides capabilities for creation and deployment of reports, charts, dashboards, and interactive data visualization solutions. DataQuant supports the creation of query, reporting, and dashboard solutions for all IBM DB2 platforms. For those familiar with Query Management Facility (QMF) for Windows or QMF Distributed Edition, DataQuant for Multiplatforms is the successor product. On the z/OS platform, DataQuant can be deployed independently or as an extension to QMF, where objects such as queries and forms may be jointly created and shared.
DataQuant provides a comprehensive array of BI capabilities that are both powerful and easy to use. Customers can benefit from DataQuant features that include most commonly performed BI tasks coupled with a flexible security infrastructure and resource governing and usage auditing. BI solutions based on DataQuant can handle large as well as small data reporting needs and interfaces and objects can be customized and personalized by the users.
See the DataQuant Web site, found in the
Resources section, for additional information.
DataQuant consists of the following components:
- DataQuant for Workstation: An Eclipse-based, cross-platform, workstation application that runs on Linux, Solaris, and Windows referred to as the "rich client."
- DataQuant for WebSphere: An application-server-based solution that uses a "thin client", browser-based, deployment model for wide enterprise deployments.
The basic architecture for deployment of these DataQuant components is shown in Figure 2. It shows how DataQuant can be deployed as either a "rich client" or "thin client" hosted by an application server, or even a combination of both. The rich client installation is used in the steps in this article, but the reporting could be performed from either client configuration.
Figure 2. DataQuant architecture
To demonstrate that XML reporting can be done on the complex XML data in use in industry, a database is used that contains sample corporate tax filing information based on the U.S. Government Tax1120 eFile XML format (now called Modernized eFile, or MeF). IBM offers a number of free, downloadable packages for getting started with DB2 pureXML and industry format data, including a Tax1120 bundle. These bundles include the scripts to create a test database and insert sample data, and some example queries to run against it. This article uses the database structure used in the Tax1120 bundle as the example database, so that those who download the bundle can use that environment to run their reports against (to download the bundle, see the
Resources section of this article).
An electronic form can be used as a means to capture data and submit it in a particular XML format, such as the Tax1120 MeF format. A sample of an electronic form for tax form 1120 filing, created in Lotus Forms and producing the Tax1120 XML format, is shown in Figure 3. The XML document produced by such a form is the source of the data you will create reports from in this article.
Figure 3. Sample electronic form for Tax1120 filing
This article describes how to create reports based on the XML data stored in a table that contains both relational and XML data. The structure of this table, TAX1120TABLE, is shown in Table 1.
Table 1. TAX1120TABLE structure
| ID | COMMENT | DATA |
|---|
| INTEGER | VARCHAR | XML |
If you are using the Tax1120 bundle, simply follow the user guide of the bundle to create the database and insert sample data. If you are creating the table by yourself instead of using the Tax1120 bundle, use the statement below to create the TAX1120TABLE then follow the instructions in the sample data attachment from the
Download section of this article to import sample data into the database.
CREATE TABLE TAX1120TABLE (
ID INTEGER PRIMARY KEY NOT NULL,
COMMENT VARCHAR(500)
DATA XML) |
The "ID" is a generic key column but is not used in the reporting. The "DATA" is the column that stores the TAX1120 XML data instance. Notice that the "DATA" column in this table is defined as type XML. This causes DB2 to store input XML data in its native hierarchical format.
XML data is often heavily nested and can be rather large. The reports generated in this article are based on the Tax1120 format, and are specific to that XML structure and the contents of the documents stored in the sample database. You don't need to be an XML or database expert to learn how to report on XML, but you do need to understand the structure (hierarchy) of the XML documents you want to report on so you can select the appropriate data items (elements or attributes) for reporting.
The XML content of a sample Tax1120 document is partially shown in Figure 4 (source view) and Figure 5 (tree view) using the DB2 Control Center document viewer, an administrative tool that ships with DB2. Some people find the tree view easier to read. (IBM Data Studio, an Eclipse-based database development environment, also enables users to inspect DB2 pureXML data in both source and tree-based formats.)
Figure 4. Sample Tax1120 data shown in Source View with the DB2 Control Center
Figure 5. Sample Tax1120 data shown in Tree View with the DB2 Control Center
Reporting and BI tools, like DataQuant, that work with relational databases, have been designed to work with distinct columns of simple numbers or text strings. While some can access data in an XML column, many don't have facilities to format complex XML into a structure that is meaningful for reports. To make the XML suitable for reporting or analysis for these tools, an intermediate step is required that separates out the relevant data from the overall XML document.
Two different ways are described that DB2 pureXML can provide XML data formatted into the numerical or text strings that are suitable for reporting in DataQuant. The first method provides the data results needed for the report by issuing SQL/XML statements that transform the XML dynamically into the data needed for the report. The second method initially creates a relational view with the columns containing the data from the XML document that are needed for reporting, and then issues SQL statements against the view to produce the report contents. With this method, all the DataQuant capabilities that operate on relational tables are available to access data in the view.
The steps in DataQuant to generate a report from SQL issued against relational views over XML data or from result sets dynamically returned by SQL/XML queries are very similar. These are described later in a later section.
The data for generating reports in DataQuant can be produced directly with SQL/XML queries, much like SQL queries are used to produce report data from traditional relational columns. This method is essentially a dynamic way of producing the desired results, identifying the specific elements and attributes from the XML documents to make available for the reports in the SQL/XML queries. It provides for a flexible selection of the data from the XML document for reporting using ad-hoc SQL/XML queries.
As described earlier, DB2 provides the capability to create relational representation of hierarchical data stored in XML columns. For this alternative, you will extend that capability by creating a view based on that representation. To do this, an administrator would write an SQL/XML query that provides the view definition made up of columns containing all the data necessary for reports extracted from the XML documents. The query maps one or more specific XML element or attribute node values to an equal number of relational data type columns (see Figure 6). To do this, an XPath expression is used to identify the portion of the XML data, normally a specific XML text node, that returns each relational data value. The SQL standard XMLTABLE function provides a straightforward way to provide the set of XPath expressions mapping the XML to desired relational values. Define a view that uses an XMLTABLE function to create your relational columns from the XML.
Figure 6. Mapping of XML data to relational columns
To learn more about SQL/XML, XPath expressions, or XMLTable, see the DB2 pureXML information in the
Resources section.
The diagram in Figure 7 illustrates the two approaches to creating reports on data in XML columns.
Figure 7. Two approaches to reporting on XML data
The first approach, as mentioned earlier, directly queries the XML documents, selecting the element and attribute values that are desired for the report. This method is essentially a dynamic way of producing the same results as the view method. The SQL/XML approach avoids the system overhead of having a view object and provides for a more flexible selection of the data from the XML document for reporting but to do this it requires that the reporting users be comfortable writing ad-hoc SQL/XML queries.
The second approach simplifies the query necessary for report production by producing a view that has already extracted the key data elements and attributes from the XML documents (2a), then applying a traditional SQL statement on the view to generate the result (2b). Since this view can be created by an administrator, the DataQuant report user does not need an understanding of the underlying XML, or need to have experience with SQL/XML or XPath statements. A simple SQL statement can be used to select which columns to include in a report or the user can use visual builder utilities against the view, as they would against a relational table, to visually select column data for report content. One drawback of the view approach is that the report builders have to articulate ahead of time which XML elements and attributes they want to have available so the database administrator can produce the appropriate view. Making changes to the view definition may take some planning and time. Another potential drawback is that, whereas the SQL/XML queries can be written to directly exploit indexes on the XML data documents, the SQL queries written against the view need to be indirectly matched to indexes by the system. DB2 9 for z/OS allows SQL queries written against the view to exploit XML indexes on the underlying XML. This helps improve performance.
The statement below creates the TAX1120VIEW that you will use in the reporting section. This is a relational view that contains selected information from the Tax1120 XML data. The TAX1120VIEW view contains columns representing the Employer Identification Number, Company Name, Total Income, and Total Tax. These are produced with the XMLTABLE function described above. You can execute the statement to create this view in a DB2 command line or other query tool.
CREATE VIEW TAX1120VIEW AS
SELECT T.EIN AS "EIN",
T.COMPANYNAME AS "Company Name",
T.TOTALINCOME AS "Total Income",
T.TOTALTAX AS "Total Tax"
FROM TAX1120TABLE, XMLTABLE(XMLNAMESPACES(DEFAULT 'http://www.irs.gov/efile'),
'$d/Return' PASSING DATA AS "d"
COLUMNS EIN VARCHAR(20) PATH 'ReturnHeader/Filer/EIN',
COMPANYNAME VARCHAR(100) PATH
'ReturnHeader/Filer/Name/BusinessNameLine1',
TOTALINCOME DOUBLE PATH 'ReturnData/IRS1120/TotalIncome',
TOTALTAX DOUBLE PATH 'ReturnData/IRS1120/TotalTax'
) AS T |
Table 2 illustrates the view structure produced by the statement above.
Table 2. Structure of TAX1120VIEW
| EIN | COMPANYNAME | TOTALINCOME | TOTALTAX |
|---|
| VARCHAR | VARCHAR | DOUBLE | DOUBLE |
Before the reporting can begin, an administrator needs to setup the reporting environment in DataQuant, including identifying the repository storage and the database connection information. Those steps are described below.
After DataQuant is installed, before a DataQuant user can create a report from data in a database, they, or an administrator, must first provide access to the data sources and create the workspace environment for reporting. The data source setup is a one-time process that includes creating the repository that holds the object, security models, and users for the source. Providing access to the data source involves the following steps:
- Create the repository storage. Define and name storage to hold the DataQuant user repositories.
- Create repository within the repository storage.
- Create the data source. Define a connection to each repository.
- Create a connection. Configure data source information within the repository.
These four setup steps are described for the database in the sections below. Note that the step to configure access to JDBC drivers was skipped, since it is assumed that each database user already has these available. If this is not true, follow the steps in the DataQuant guide. This example used DataQuant for Workstation installation on Windows, connecting to a DB2 9 database on the same system. If you have a different configuration, the steps for you may differ slightly. Startup DataQuant and proceed to Step 1.
- Open the Administrator perspective from Window > Open Perspective > Administrator on the menu bar.
- Right-click in the Repositories view and select New > Repository Storage.
- Select Database-based Repository Storage and click Next.
- Select DB2 JDBC Universal Driver for the JDBC driver field. Fill in the field for the JDBC URL, "jdbc:db2://localhost:50000/TAX1120" is used in this example. Fill in the username and password fields with your DB2 user ID and password. Click Next.
- Select Create or upgrade repository storage tables. Click Next. Click Next again, then click Finish.
Figure 8. Create new repository storage
- Right-click the repository storage in the Repositories view, and select New > New Repository.
- Give the repository a name. It is called "Tax1120Repository", in this example. Click Finish.
- Right-click the repository in the Repositories view, and select New > Relational Data Source.
- Give the data source a name. It is called "Tax1120DataSource" in this example. Select DB2 JDBC Universal Driver for the JDBC driver field. Fill in the fields for the JDBC URL, username, and Password. Click Next. Click Next again, and clickFinish.
Figure 9. Create New Data Source
- Open the Repository Connections view from Window > Show View > Other.
- Right-click in the Repository Connections view, and select New > Repository Connection.
- Select Database-based repository.
- Give the connection a name. It is called "Tax1120Connection" in this example. Select DB2 JDBC Universal Driver for the JDBC driver field. Fill in the fields for JDBC URL, username, and password. Click Refresh for the Repository name field to select a repository. Select Unprotected, and click Finish.
Figure 10. Create a new repository connection
Once the connection is created, you can see the repository and connection in the Administrator view as shown in Figure 11.
Figure 11. Administrator view of DataQuant
Now that the administrative setup is complete, you can proceed to the steps to access the data and create the reports.
The report creation process in DataQuant can be broken up into two major steps:
- Create query: Identify a query to produce candidate data contents for reports.
- Queries from both approaches shown in Figure 7 are shown in this section so it is easier to compare them.
- Create report: Develop a report layout.
Use the following steps to create reports in DataQuant.
- Switch to the User perspective from the menu Window > Open Perspective > User. Right-click in the Workspaces view, and select New > Query.
- Give the query a name, "Query1" is used in this example, and select the data source you created earlier (for example, Tax1120DataSource). Click Finish.
- Create the query for the report (both options described above are illustrated):
To create a report directly on the XML documents, enter the following SQL/XML query statement (or the SELECT portion from the
Create the DB2 view section that uses XMLTABLE).
SELECT XMLCAST(XMLQUERY('declare default element
namespace "http://www.irs.gov/efile";
$d/Return/ReturnHeader/Filer/EIN/text()'
PASSING DATA AS "d") AS VARCHAR(20)) AS "EIN",
XMLCAST(XMLQUERY('declare default element
namespace "http://www.irs.gov/efile";
$d/Return/ReturnHeader/Filer/Name/BusinessNameLine1/text()'
PASSING DATA AS "d") AS VARCHAR(100)) AS "Company Name",
XMLCAST(XMLQUERY('declare default element
namespace "http://www.irs.gov/efile";
$d/Return/ReturnData/IRS1120/TotalIncome/text()'
PASSING DATA AS "d") AS DOUBLE) AS "Total Income",
XMLCAST(XMLQUERY('declare default element
namespace "http://www.irs.gov/efile";
$d/Return/ReturnData/IRS1120/TotalTax/text()'
PASSING DATA AS "d") AS DOUBLE) AS "Total Tax"
FROM TAX1120TABLE |
The query statement above returns the same information as contained in the TAX1120VIEW view. Instead of defining a view, it produces the data dynamically. The above query is an alternative to the query with XMLTABLE used to define the view. It produces the same results, but as you can see, it is a little more complex to construct and interpret, so for most cases it is recommended that you use XMLTABLE to construct relational column data from XML, whether dynamically or for views. You can use either query for the purpose of this example. Note that XMLCAST is an SQL/XML function to convert XML data into relational data types. See the
Resources section for more information.
To create a report based on the relational view of the XML created previously, enter the following query statement:
SELECT * FROM TAX1120VIEW |
As you can see, having the TAX1120VIEW defined to contain precisely the data you want to report on from the XML documents, you only need a very simple report-producing query. Even choosing only a subset of the columns from the view would be a simple SQL query. Furthermore, the report could be built from the view source using DataQuant visual builder capabilities, further isolating end users from the specifics of the data. To use the visual builder approach, take the following steps:
- Click the Prompted tag at the bottom of the Query1 window.
- Click Add Table to add a source table to the query.
- Click Add From List....
- Enter the "Table Owner" name (this is the DB2 user that created the view), and then click Refresh List.
- Select TAX1120VIEW, and click Add to add the view to the query. Click Close.
- By default, all columns in the selected table/view are selected. You can click Add Column to add specific columns to the query, or click Change Column or Delete Column to change or delete a column in the query.
Figure 12. Visually create query statement against table/view
As shown in Figure 12, the above steps visually create a query statement that generates the same result asSELECT * FROM TAX1120VIEW. This approach allows the end users to create a report without explicit knowledge of the SQL language.
- Save "Query1". Select Query > Run from the menu bar or click the Run icon in the tool bar to execute the query. After execution, the query result is displayed, as shown in Figure 13.
Figure 13. Result set of the sample query
Whichever method you used to create the query results for the report, through view or directly, use the following steps for creating the report:
- Select Results > Display Report... from the menu bar.
- Select Create a new report, and click Next.
- Use the pre-selected option, which is Generated based on current query under "Create a visual report." Click Finish. DataQuant also provides two other options for reporting, classic report and BIRT report, which is not discussed in this article.
- To adjust the report, click the Design tab at the bottom of the window to switch to Design view, as shown in Figure 14.
Figure 14. Design view of the generated report
- You can adjust the size and location of the report objects, the format of the report data, and other visual properties of the report within the Design view. For instance, to display the Total Income values with two numbers to the right of the decimal, select the cell under the "Total Income" column in the "Detail1" section. Change the "Text" field in the Properties view to =formatNum("0.00",[Total Income]).
- Switch back to the Preview view, using the tab at the bottom of the window, to see the report as shown in Figure 15. Save as Report1, and close the report.
Figure 15. Preview view of the completed report
That is all that is required to create a report on XML data with DataQuant. Now look at another presentation option, the Visual Dashboard, in the section below.
Visual Dashboard is a type of visual project that presents interactive or persistent data to a wide range of users. Visual dashboards can simultaneously draw data from multiple, heterogeneous data sources and present the data using a variety of graphs, charts, and graphical widgets. Content developers can create a visual dashboard that can be viewed by multiple users with either a Web browser or a thin client version of DataQuant for Workstation.
To create a Visual Dashboard with the Tax 1120 XML data:
- Open the Visual Designer perspective by selecting Window > Open Perspective > Visual Designer.
- Open the Palette view by selecting Window > Show View > Other... and then select General > Palette.
- In the Project Explorer view, right-click Visual Dashboards, and select New Visual Dashboard....
- Give the report a name. In this example it is called "Dashboard1". Click Finish.
- In the Project Explorer view, right-click Visual Dashboards > *Dashboard1 > Connections and select Insert Connection....
- Select Data Sources > Tax1120DataSource and give the connection a name. In this example it is called "Connection1". Click Finish.
- In the Project Explorer view, right-click Visual Dashboards > *Dashboard1 > Queries, and select Insert Query....
- Check the Attach an existing query option, and click the Select Query From Repository symbol. Select Open from Repository, and click Next. Select Workspaces > Default > Query1, and click Finish. Give the query a name. In this example, it is called "Query1". Click Finish.
- In the Palette view, select BarChart, and click in the Detail1 section in the *Report2.MainPage window to create a chart.
- Select Query1, and click Next. Add the "Total Income" and the "Total Tax" fields to the Fields to be displayed area, and click Next. Check the Include a label box and select "Company Name" in the Label field drop down menu. Uncheck theDisplay the value of each bar check box. Click Finish.
- Adjust the size, location, and property of the chart as needed. Click the Runtime tab to display the dashboard, as shown in Figure 16. Save as Dashboard1, and close the dashboard.
Figure 16. Runtime view of the visual dashboard
DB2 pureXML is growing as a repository for storing and managing XML data. This article has described and provided instructions for two approaches for using DataQuant to create reports from DB2 pureXML data. The first approach involves direct execution of SQL/XML queries to produce the data from the XML for input to the DataQuant report. The second approach involves a two-step process of first creating a relational view of the XML data, then constructing simple SQL queries (or using DataQuant's wizards) to generate the reports from the relational view. It also showed how to create a visual dashboard in DataQuant for dynamic presentation of XML data. To learn more about DataQuant or DB2 pureXML, review the materials cited in the
Resources section.
The authors would like to thank Isayas Sium, Cindy Saracco, Jason Cu, Blanca Borden, Mary Desisto, and Kate Riley Tennant who provided input to or reviewed this article.
| Description | Name | Size | Download method |
|---|
| Sample data files | sampledata.zip | 24.7KB | HTTP |
More downloads
