Jagged Rows for Hierarchical XML Data Mapping
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing methods for representing hierarchical data, such as XML documents, in spreadsheet applications are inadequate, as they either treat XML data as a character string, result in cluttered reports due to data repetition when flattening, or fail to accommodate user-defined formats, leading to inefficient queries and reporting challenges.
Innovation Solution
A method that encapsulates database query results in a tree hierarchy, retrieves mapping information to specify node mappings to a tabular display, and increments row positions for repeating nodes, creating jagged rows to effectively represent hierarchical data in a tabular format, allowing for both relational and hierarchical data to be mapped to spreadsheet cells.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Ease of manufacture
If XML data is treated as a character string in spreadsheet cells, then the implementation is simple, but the hierarchical structure of XML data is lost and reporting capability is insufficient
Solution Approach 1:
The patent segments the XML hierarchical data into multiple spreadsheet rows, with each row representing a different level or element of the XML hierarchy. This allows the hierarchical structure to be preserved in a tabular format, enabling both simple spreadsheet implementation and retention of structural information.
Solution Approach 2:
The patent implements nested spreadsheets where a parent spreadsheet can contain child spreadsheets that represent nested XML elements. This nesting approach preserves the hierarchical structure of XML data while maintaining the familiar spreadsheet interface, allowing users to drill down into nested elements without losing structural context.
2Ease of operation
If XML data is flattened using SQL/XML functions like XMLTable, then a tabular result set is produced, but the report becomes cluttered with repeated data and queries become more complicated
Solution Approach 1:
The patent introduces dynamic row positioning where rows in the spreadsheet can shift position based on the presence of repeating nodes in the XML hierarchy. This dynamic approach allows the spreadsheet to adapt to the actual data structure, displaying repeated elements only where necessary and maintaining a clean, uncluttered presentation without full flattening.
Solution Approach 2:
Instead of fully flattening the XML data which causes repetition, the patent segments the data display into hierarchical rows that only expand when repeating nodes are present. This selective segmentation maintains tabular display capability while avoiding unnecessary data duplication in the report.
3Loss of information
If hierarchical grids with encapsulated grid sets are used, then XML hierarchy is represented, but the grid structure is fixed and does not accommodate user-defined report formats
Solution Approach 1:
The patent makes the spreadsheet grid dynamic by allowing row positions to change based on the XML data structure and user-defined mappings. Unlike fixed hierarchical grids, this implementation allows users to define custom report formats by specifying how XML nodes map to spreadsheet cells, while the grid automatically adapts its structure to accommodate both hierarchical representation and user-defined layouts.
4Ease of manufacture
If XML data is flattened to work with existing reporting tools, then infrastructure disruption is minimized, but query complexity increases and performance decreases
Solution Approach 1:
The patent makes the spreadsheet application multi-functional by enabling it to handle both traditional tabular data and hierarchical XML data natively. This eliminates the need for XML flattening to compatibility with reporting tools, as the spreadsheet itself can directly process and display hierarchical structures, thereby improving query efficiency while maintaining infrastructure compatibility.
Data Source
AI summary
Embodiments of the present invention provide techniques for mapping hierarchical data from a query result into a tabular display, such as a spreadsheet. The tabular display allows some (or all) of the elements included in the hierarchical display to be mapped to positions on the report. In one embodiment, a field of the result that stores hierarchical data with repeating elements (e.g., an XML document) is mapped into the tabular report using jagged rows. This approach allows a user to map both the hierarchical and relational data in the result set to cells in the spreadsheet, and in the process, handle any embedded collection (repeated element) in the hierarchical data.