Mapping hierarchical data from a query result into a tabular format with jagged rows
View Patent ↗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.
1. A computer-implemented method of representing hierarchical data from a database query result in a tabular display, comprising:
obtaining the database query result of an executed database query, wherein the query result includes one or more result fields, and wherein at least one of the result fields contains a hierarchal data object;
encapsulating the query result in a tree hierarchy;
retrieving mapping information defining mappings from one or more nodes of the tree hierarchy to specified cells contained within a plurality of rows of the tabular display, wherein at least one of the one or more nodes is flagged as a repeating node;
parsing the tree hierarchy to identify the one or more nodes mapped to the specified cells of the tabular display;
copying data values from the identified one or more nodes of the tree hierarchy to the specified cells of the tabular display;
upon determining, based on the mapping information, that a current node being copied from the tree hierarchy to a cell of the tabular display is flagged as the repeating node, determining whether multiple instances of the repeating node are present in a given record of the query result; and
for each of the multiple instances of the repeating node present in the given record of the query result, incrementing a row position of the tabular display to which the current node is copied, while maintaining a column position of the tabular display to create a jagged row in the tabular display for the result fields that contain the hierarchal data object.
2. The method of claim 1 , wherein the tree hierarchy represents the query result as a single hierarchical document, wherein child nodes of a root node of the single hierarchical document are the rows of the query result, and wherein child nodes of each row are the one or more result fields included in the query result.
3. The method of claim 1 , further comprising, upon determining that the data values copied to the specified cells of the tabular display have been modified, copying the modified data values from the specified cells of the tabular display to the corresponding nodes of the tree hierarchy.
4. The method of claim 3 , further comprising, performing an update query to update the database with the modified data values copied to the tree hierarchy.
5. The method of claim 1 , wherein the tree hierarchy represents the query result as a single hierarchical document, wherein child nodes of the root node are the rows of the query result, and wherein child nodes of each row are the one or more fields.
6. The method of claim 1 , wherein the hierarchal data object is an XML document.
7. The method of claim 1 , wherein the database query is a relational database query.
8. The method of claim 1 , wherein the tabular display is a spreadsheet.
9. A computer program product comprising a computer useable storage medium having a computer readable program, wherein the computer readable program when executed on a computer causes the computer to perform an operation to generate a tabular display of hierarchical data included in a database query result, the operation comprising:
obtaining the database query result of an executed database query, wherein the query result includes one or more result fields, and wherein at least one of the result fields contains a hierarchal data object;
encapsulating the query result in a tree hierarchy;
retrieving mapping information defining mappings from one or more nodes of the tree hierarchy to specified cells contained within a plurality of rows of the tabular display, wherein at least one of the one or more nodes is flagged as a repeating node;
parsing the tree hierarchy to identify the one or more nodes mapped to the specified cells of the tabular display;
copying data values from the identified one or more nodes of the tree hierarchy to the specified cells of the tabular display;
upon determining, based on the mapping information, that a current node being copied from the tree hierarchy to a cell of the tabular display is flagged as the repeating node, determining whether multiple instances of the repeating node are present in a given record of the query result; and
for each of the multiple instances of the repeating node present in the given record of the query result, incrementing a row position of the tabular display to which the current node is copied, while maintaining a column position of the tabular display to create a jagged row in the tabular display for the result fields that contain the hierarchal data object.
10. The computer-readable storage medium of claim 9 , wherein the tree hierarchy represents the query result as a single hierarchical document, wherein child nodes of a root node of the single hierarchical document are the rows of the query result, and wherein child nodes of each row are the one or more result fields included in the query result.
11. The computer-useable storage medium of claim 9 , wherein the operation further comprises, upon determining that the data values copied to the specified cells of the tabular display have been modified, copying the modified data values from the specified cells of the tabular display to the corresponding nodes of the tree hierarchy.
12. The computer-readable storage medium of claim 11 , wherein the operation further comprises, performing an update query to update the database with the modified data values copied to the tree hierarchy.
13. The computer-useable storage medium of claim 9 , wherein the tree hierarchy represents the query result as a single hierarchical document, wherein child nodes of the root node are the rows of the query result, and wherein child nodes of each row are the one or more fields.
14. The computer-useable storage medium of claim 9 , wherein the hierarchal data object is an XML document.
15. The computer-useable storage medium of claim 9 , wherein the database query is a relational database query.
16. The computer-useable storage medium of claim 9 , wherein the tabular display is a spreadsheet.
17. A system, comprising:
a processor; and
a memory containing a program which, when executed by the processor, is configured to:
obtain the database query result of an executed database query, wherein the query result includes one or more result fields, and wherein at least one of the result fields contains a hierarchal data object;
encapsulate the query result in a tree hierarchy;
retrieve mapping information defining mappings from one or more nodes of the tree hierarchy to specified cells contained within a plurality of rows of the tabular display, wherein at least one of the one or more nodes is flagged as a repeating node;
parse the tree hierarchy to identify the one or more nodes mapped to the specified cells of the tabular display;
copy data values from the identified one or more nodes of the tree hierarchy to the specified cells of the tabular display;
upon determining, based on the mapping information, that a current node being copied from the tree hierarchy to a cell of the tabular display is flagged as the repeating node, determine whether multiple instances of the repeating node are present in a given record of the query result; and
for each of the multiple instances of the repeating node present in the given record of the query result, increment a row position of the tabular display to which the current node is copied and maintain a column position of the tabular display to create a jagged row in the tabular display for the result fields that contain the hierarchal data object.
18. The system of claim 17 , wherein the tree hierarchy represents the query result as a single hierarchical document, wherein child nodes of a root node of the single hierarchical document are the rows of the query result, and wherein child nodes of each row are the one or more result fields included in the query result.
19. The system of claim 17 , wherein the program is further configured to, upon determining that the data values copied to the specified cells of the tabular display have been modified, copy the modified data values from the specified cells of the tabular display to the corresponding nodes of the tree hierarchy.
20. The system of claim 19 , wherein the program is further configured to perform an update query to update the database with the modified data values copied to the tree hierarchy.
21. The system of claim 17 , wherein the tree hierarchy represents the query result as a single hierarchical document, wherein child nodes of the root node are the rows of the query result, and wherein child nodes of each row are the one or more fields.
22. The system of claim 17 , wherein the hierarchal data object is an XML document.
23. The system of claim 17 , wherein the database query is a relational database query.
24. The system of claim 17 , wherein the tabular display is a spreadsheet.