Mapping of data from XML to SQL

• US 7,363,310 B2
• Filed: 09/03/2002
• Issued: 04/22/2008
• 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: Nolan, Geoffrey John, Schnelle, Christoph
• Primary Examiner(s): Alam; Shahid
• Assistant Examiner(s): Stevens; Robert
• Application Number:
  US10/233,991
• Publication Number:
  US 20030070144A1
• Time in Patent Office: 2,058 Days
• Field of Search: 715/513, 715/523, 715/531, 707/102, 707/2
• US Class Current: 1/1
• 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 are 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 an XML encoded dataset into a set of SQL tables and storing said XML encoded dataset in said set of tables, said method comprising:

identifying at least one hierarchical structure in said XML encoded dataset; and

converting an XML encoded dataset associated with each identified hierarchical structure,

wherein for each identified hierarchical structure said converting step comprises the further steps of;

determining a node element set for said identified hierarchical structure of said XML encoded dataset, wherein each node element in said node element set is a discrete level of said identified hierarchical structure of said dataset, said discrete level having an internal structure containing either element content or element content and data content;

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

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

generating and storing said set of SQL tables, said set of SQL tables containing one or more records, each record corresponding to a respective one of said allocated node identifiers;

wherein said node elements are selected to eliminate the need for downstream applications to assemble data from an elemental level, and

wherein said set of SQL tables are limited to a number of tables required by said downstream applications.

2. The method according to claim 1 wherein said XML encoded dataset comprises a plurality of predefined portions of text-based data, each predefined portion of said text-based data being encoded using XML, and a plurality of attributes for organizing said predefined portions of said text-based data.

3. The method according to claim 2, wherein said predefined portions comprise at least one modified and stored predefined portion encoded using XML, said attributes for organizing said predefined portions and said modified predefined portion of said text-based data.

4. The method according to claim 3, wherein said XML encoded dataset further comprises a plurality of linking means of a markup language, each predefined portion of said text-based data and said at least one modified predefined portion of text-based data being encoded with at least one linking means.

5. The method according to claim 1, wherein for each identified hierarchical structure, 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.

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

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

8. The method according to claim 1 comprising the further step of:

converting the XML encoded dataset into pre-processed content fields.

9. The method according to claim 8, wherein for each identified hierarchical structure, said converting step comprises the further step of:

generating a content table with said pre-processed content fields in said content table, each record in said content table having a node identifier field containing the corresponding node identifier, a block identifier field and a content field containing said pre-processed content field.

10. The method according to claim 1, wherein for each identified hierarchical structure, said converting step comprises the further step of:

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

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

12. The method according to claim 1 comprising the further step of:

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

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

14. An apparatus for converting an XML encoded dataset into a set of SQL tables and storing said XML encoded dataset in said set of tables, said apparatus comprising:

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

a converter for converting an XML encoded dataset associated with each identified hierarchical structure, said converter comprising;

a processor for determining a node element set for said identified hierarchical structure of said XML encoded dataset, wherein each node element in said node element set is a discrete level of said identified hierarchical structure of said dataset, said discrete level having an internal structure containing either element content or element content and data content;

a processor for determining one or more nodes of said XML encoded dataset, each node being an instance of a node element;

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

a generator for generating and storing said set of SQL tables, said set of SQL tables containing one or more records, each record corresponding to a respective one of said allocated node identifiers;

wherein said node elements are selected to eliminate the need for downstream applications to assemble data from an elemental level, and

wherein said set of SQL tables are limited to a number of tables required by said downstream applications.

15. The apparatus according to claim 14, wherein said XML encoded dataset comprises a plurality of predefined portions of text-based data, each predefined portion of said text-based data being encoded using XML, and a plurality of attributes for organizing said predefined portions of said text-based data.

16. The apparatus according to claim 15, wherein said predefined portions comprise at least one modified and stored predefined portion encoded using XML, said attributes for organizing said predefined portions and said modified predefined portion of said text-based data.

17. The apparatus according to claim 16, wherein said XML encoded dataset further comprises a plurality of linking means of a markup language, each predefined portion of said text-based data and said at least one modified predefined portion of text-based data being encoded with at least one linking means.

18. The apparatus according to claim 14, wherein for each identified hierarchical structure, said converter for converting 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.

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

20. The apparatus according to claim 14, wherein each node comprises at least one of a property, sub-node, initial content, and block content.

21. The apparatus according to claim 14 further comprising:

a converter for converting the XML encoded dataset into pre-processed content fields.

22. The apparatus according to claim 21, wherein said means for converting an XML encoded dataset associated with each identified hierarchical structure further comprises:

a generator for generating a content table with said pre-processed content fields in said content table, each record in said content table having a node identifier field containing the corresponding node identifier, a block identifier field and a content field containing said pre-processed content field.

23. The apparatus according to claim 14, wherein said converter for converting an XML encoded 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 XML encoded dataset.

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

25. The apparatus according to claim 14 further comprising:

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

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

27. A computer program product having a computer readable storage medium having a computer program recorded therein for converting an XML encoded dataset into a set of SQL tables and storing said XML encoded dataset in said set of tables, said computer program product comprising:

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

computer program code means for converting an XML dataset associated with each identified hierarchical structure, said means comprising;

computer program code means for determining a node element set for said identified hierarchical structure of said XML encoded dataset, wherein each node element in said node element set is a discrete level of said identified hierarchical structure of said dataset, said discrete level having an internal structure containing either element content or element content and data content;

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

computer program code means for allocating to each said instance of a node element a unique node identifier, and

program code means for generating and storing said set of SQL tables, said set of SQL tables containing one or more records, each record corresponding to a respective one of said allocated node identifiers;

wherein said node elements are selected to eliminate the need for downstream applications to assemble data from an elemental level, and

wherein said set of SQL tables are limited to a number of tables required by said downstream applications.

28. The computer program product according to claim 27, wherein said XML encoded dataset comprises a plurality of predefined portions of text-based data, each predefined portion of said text-based data being encoded using XML, and a plurality of attributes for organizing said predefined portions of said text-based data.

29. The computer program product according to claim 28, wherein said predefined portions comprise at least one modified and stored predefined portion encoded using XML, said attributes for organizing said predefined portions and said modified predefined portion of said text-based data.

30. The computer program product according to claim 29, wherein said XML encoded dataset further comprises a plurality of linking means of a markup language, each predefined portion of said text-based data and said at least one modified predefined portion of text-based data being encoded with at least one linking means.

31. The computer program product according to claim 27, wherein for each identified hierarchical structure, said computer program code means for converting further comprises computer program code means 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.

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

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

34. The computer program product according to claim 27 further comprising:

computer program code means for converting the XML encoded dataset into pre-processed content fields.

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

computer program code means for generating a content table with said pre-processed content fields in said content table, each record in said content table having a node identifier field containing the corresponding node identifier, a block identifier field and a content field containing said pre-processed content field.

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

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

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

38. The computer program product according to claim 27 further comprising:

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

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