Mapping of data from XML to SQL

• US 8,204,913 B2
• Filed: 03/06/2008
• Issued: 06/19/2012
• Est. Priority Date: 09/04/2001
• Status: Expired due to Fees

• Current Assignee: TimeBase Pty Limited (Fiscalnote Holdings, Inc.)
• Original Assignee: TimeBase Pty Limited (Fiscalnote Holdings, Inc.)
• Inventors: Schnelle, Christoph, Nolan, Geoffrey John
• Primary Examiner(s): Fleurantin, Jean B
• Assistant Examiner(s): GIRMA, ANTENEH B
• Application Number:
  US12/073,511
• Publication Number:
  US 20080208879A1
• Time in Patent Office: 1,566 Days
• Field of Search: None
• US Class Current: 707/802
• CPC Class Codes: G06F 16/86 Mapping to a database
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing
• CPC Class Codes: Y10S 707/99943 Generating database or data structure, e.g. via user interface

Abstract

A method, an apparatus and a computer program product for converting an XML encoded dataset into a minimal set of SQL tables and provided. In the method, a hierarchical structure in the XML encoded dataset is identified. A node element set for the XML encoded dataset is determined, wherein each node element in the node element set is a discrete level of the hierarchical structure of the dataset. One or more nodes of the XML encoded dataset are determined, each node being an instance of a node element. A unique node identifier is allocated to each node. Then, an SQL node table containing one or more records is generated, each record corresponding to a respective one of the allocated node identifiers. An SQL ancestry table is optionally generated to define the inter-relationships among nodes of the identified hierarchical structure of the XML encoded dataset.

Claims

1. A computer implemented method for converting a dataset into at least one table, said method comprising:

identifying at least one hierarchical structure in said dataset; and

converting said dataset associated with said at least one identified hierarchical structure by;

determining a node element set for said at least one identified hierarchical structure of said dataset, wherein at least one node element in said node element set is a discrete level of said at least one identified hierarchical structure of said dataset;

determining one or more nodes of said dataset, each of said one or more nodes being an instance of a node element from said node element set;

allocating to said instance of said node element a unique node identifier; and

generating said at least one table, said at least one table containing one or more records, each record corresponding to a respective one of said allocated node identifiers;

wherein said node element set is selected to reduce the need for at least one downstream application to assemble data from an elemental level;

wherein said at least one table is as required by said at least one downstream application;

wherein said dataset comprises a plurality of predefined portions of text-based data, at least one of said plurality of predefined portions of text-based data being associated with at least one attribute for organizing at least one of said plurality of predefined portions of text-based data; and

wherein said plurality of predefined portions of text-based data comprise at least one modified and stored predefined portion of text based data associated with at least one attribute for organizing at least one of said plurality of predefined portions of text based data and said at least one modified and stored predefined portion of text-based data.

2. The method according to claim 1, wherein said dataset further comprises a plurality of links of at least one of code or a markup language, at least one of said plurality of predefined portions of text-based data and said at least one modified and stored predefined portion of text-based data being associated with at least one link.

3. The method according to claim 1, wherein said converting step further comprises the step of generating a node table with one record for each said node, each said record of said node table including a field containing the value of said unique node identifier allocated to said node.

4. The method according to claim 3, wherein each record of said node table further comprises a field for at least one simple property of the corresponding node, at least one simple property being capable of taking at most one value per node.

5. The method according to claim 1, wherein each node comprises at least one of a property, sub-node, initial content, or block content.

6. The method according to claim 1 further comprising converting the dataset into at least one pre-processed content field.

7. The method according to claim 6, wherein said converting step comprises generating a content table with said at least one pre-processed content field in said content table, at least one record in said content table having a node identifier field containing the corresponding node identifier, a block identifier field and a content or content reference field.

8. The method according to claim 1, wherein said converting step comprises generating an ancestry table with node identifiers defining the inter-relationships among nodes of said identified hierarchical structure of said dataset.

9. The method according to claim 8, wherein said ancestry table comprises a descendant node identifier field and an ancestor node identifier field.

10. The method according to claim 1 comprising generating a complex properties table having a node identifier field and a value field.

11. The method according to claim 10, wherein said complex properties table further comprises a sub-property field.

34. The method according to claim 1, wherein the dataset is an XML encoded dataset and the tables are SQL tables.

12. An apparatus for converting a dataset into at least one table, said apparatus comprising:

at least one processor for identifying at least one hierarchical structure in said dataset; and

a converter for converting said dataset associated with said at least one identified hierarchical structure, said converter comprising;

at least one processor for determining a node element set for said at least one identified hierarchical structure of said dataset, wherein at least one node element in said node element set is a discrete level of said at least one identified hierarchical structure of said dataset;

at least one processor for determining one or more nodes of said dataset, each of said one or more nodes being an instance of a node element from said node element set;

an allocator for allocating to said instance of said node element a unique node identifier; and

a generator for generating said at least one table, said at least one table containing one or more records, each record corresponding to a respective one of said allocated node identifiers;

wherein said node element set is selected to reduce the need for at least one downstream application to assemble data from an elemental level;

wherein said at least one table is as required by said at least one downstream application;

wherein said dataset comprises a plurality of predefined portions of text-based data, at least one of said plurality of predefined portions of text-based data being associated with at least one attribute for organizing at least one of said plurality of predefined portions of text-based data; and

wherein said plurality of predefined portions of text-based data comprise at least one modified and stored predefined portion of text based data associated with at least one attribute for organizing at least one of said plurality of predefined portions of text based data and said at least one modified and stored predefined portion of text-based data.

13. The apparatus according to claim 12, wherein said dataset further comprises a plurality of links of at least one of code or a markup language, at least one of said plurality of predefined portions of text-based data and said at least one modified and stored predefined portion of text-based data being associated with at least one link.

14. The apparatus according to claim 12, wherein said converter further comprises a generator for generating a node table with one record for each said node, each said record of said node table including a field containing the value of said unique node identifier allocated to said node.

15. The apparatus according to claim 14, wherein each record of said node table further comprises a field for at least one simple property of the corresponding node, at least one simple property being capable of taking at most one value per node.

16. The apparatus according to claim 12, wherein each node comprises at least one of a property, sub-node, initial content, or block content.

17. The apparatus according to claim 12 further comprising:

a converter for converting the dataset into at least one pre-processed content field.

18. The apparatus according to claim 17, wherein said converter further comprises:

a generator for generating a content table with said at least one pre-processed content field in said content table, at least one record in said content table having a node identifier field containing the corresponding node identifier, a block identifier field and a content or content reference field.

19. The apparatus according to claim 12, wherein said converter for converting said dataset associated with each identified hierarchical structure further comprises:

a generator for generating an ancestry table with node identifiers defining the inter-relationships among nodes of said identified hierarchical structure of said dataset.

20. The apparatus according to claim 19, wherein said ancestry table comprises a descendant node identifier field and an ancestor node identifier field.

21. The apparatus according to claim 12 further comprising:

a generator for generating an complex properties table having a node identifier field and a value field.

22. The apparatus according to claim 21, wherein said complex properties table further comprises a sub-property field.

35. The apparatus according to claim 12, wherein the dataset is an XML encoded dataset and the tables are SQL tables.

37. The apparatus according to claim 12, wherein said at least one processor for identifying at least one hierarchical structure in said dataset;

said at least one processor for determining a node element set for said at least one identified hierarchical structure of said dataset; and

said at least one processor for determining one or more nodes of said dataset, can be implemented in hardware, software, or a combination of hardware and software.

23. A computer program product having a computer readable storage medium having a computer program recorded therein for converting a dataset into at least one table, said computer program product comprising:

computer program code for identifying at least one hierarchical structure in said dataset;

computer program code for converting said dataset associated with said at least one identified hierarchical structure, said computer program code comprising;

computer program code for determining a node element set for said at least one identified hierarchical structure of said dataset, wherein at least one node element in said node element set is a discrete level of said at least one identified hierarchical structure of said dataset;

computer program code for determining one or more nodes of said dataset, each of said one or more nodes being an instance of a node element from said node element set;

computer program code for allocating to said instance of said node element a unique node identifier; and

computer program code for generating said at least one table, said at least one table containing one or more records, each record corresponding to a respective one of said allocated node identifiers;

wherein said node element set is selected to reduce the need for at least one downstream application to assemble data from an elemental level;

wherein said at least one table is as required by at least one downstream application;

wherein said dataset comprises a plurality of predefined portions of text-based data, at least one of said plurality of predefined portions of text-based data being associated with at least one attribute for organizing at least one of said plurality of predefined portions of text-based data; and

wherein said plurality of predefined portions of text-based data comprise at least one modified and stored predefined portion of text based data associated with at least one attribute for organizing at least one of said plurality of predefined portions of text based data and said at least one modified and stored predefined portion of text-based data.

24. The computer program product according to claim 23, wherein said dataset further comprises a plurality of links of at least one of code or a markup language, at least one of said plurality of predefined portions of text-based data and said at least one modified and stored predefined portion of text-based data being associated with at least one link.

25. The computer program product according to claim 23, wherein said computer program code for converting further comprises computer program code for generating a node table with one record for each said node, each said record of said node table including a field containing the value of said unique node identifier allocated to said node.

26. The computer program product according to claim 25, wherein each record of said node table further comprises a field for at least one simple property of the corresponding node, a simple property being capable of taking at most one value per node.

27. The computer program product according to claim 23, wherein each node comprises at least one of a property, sub-node, initial content, or block content.

28. The computer program product according to claim 23 further comprising:

computer program code for converting said dataset into at least one pre-processed content field.

29. The computer program product according to claim 28, wherein said computer program code for converting an dataset associated with each identified hierarchical structure further comprises:

computer program code for generating a content table with said at least one pre-processed content field in said content table, at least one record in said content table having a node identifier field containing the corresponding node identifier, a block identifier field and a content or content reference field.

30. The computer program product according to claim 23, wherein said computer program code for converting said dataset associated with each identified hierarchical structure further comprises:

computer program code for generating an ancestry table with node identifiers defining the inter-relationships among nodes of said identified hierarchical structure of said dataset.

31. The computer program product according to claim 30, wherein said ancestry table comprises a descendant node identifier field and an ancestor node identifier field.

32. The computer program product according to claim 23 further comprising:

computer program code for generating an complex properties table having a node identifier field and a value field.

33. The computer program product according to claim 32, wherein said complex properties table further comprises a sub-property field.

36. The computer program product according to claim 23, wherein the dataset is an XML encoded dataset and the tables are SQL tables.