Querying XML or Web Services Data Sources Using InetSoft's Business Intelligence Software (continued)

Tree Transformation Rules

If a path in the schema tree is selected without any query conditions, the query will simply parse the XML output and return the selected subtrees as the result. The result tree is interpreted by the report engine based on the binding of the query. If tabular data is expected by the element the query is associated with, a tree is transformed into a table using the following rules:

• If the tree contains multiple nodes of the same type, each node is converted to a table row. This is the case if any tree path node is a selection node.

• If the tree contains a single root node, this node is converted to a single table row. This is the case if the tree path does not contain any selection nodes.

• A sub-tree is converted to a table row by treating each child as a table column. The child node’s name is used as the column name, and the child node’s value is used as the column value.

Example tabular output

Because the tree is converted to a table using the default table conversion, the child values of the name and location elements are not used in the final table. To display the values properly, you need to specify more precise table construction rules.


tabular output

Table Construction

Table construction rules can be specified as part of a query. The conversion rules in the query give developers more control over how to convert a tree to a table. Additional conversion parameters are:

• Inclusion of sub-tree children nodes as table columns.

• Inclusion of node attributes as table columns.

• Table column aliases.

• Data type conversion.

Read why choosing InetSoft's cloud-flexible BI provides advantages over other BI options.

Adding Table Construction Rules

To add table construction rules to a query, first select the ‘Map to Table’ option. When the option is selected, the controls for specifying the table construction rules are enabled. The Join Subtrees option, if checked, will create multiple rows per record, which correspond to each combination of the child nodes of the subtrees.

For example, if the selected node represented job application information, there could be subtree nodes for job location and for job type. When ‘Join Subtrees’ is not selected, only one row will be created for each application, and the first child node for each subtree would appear (‘New York’ & ‘engineering’). If the ‘Join Subtrees’ option is selected, multiple rows will be created to combine each location with each job type (‘New York’ & ‘engineering’, ‘Boston’ & ‘engineering’, ‘New York’ & ‘programming’, ‘Boston’ & ‘programming’, etc.).

The values for other columns would be duplicated. Individual column attributes can be changed on the selection table, where each available column is displayed. The column header shows the node or attribute name. The fields under the column name are column conversion attributes.

• Select

Select the column in the final table. If this field is selected, the column is included in the output table.

• Alias

Column alias. If a value is entered, the value is used as the column header in the final table. Otherwise, the node or attribute name is used. In the case of XML queries, type conversion must be done on the data source schema definition screen during data source definition.

  Previous: Querying Non-relational Data Sources