Mapping of data from XML to SQL

• US 20080208879A1
• Filed: 03/06/2008
• Published: 08/28/2008
• Est. Priority Date: 09/04/2001
• Status: Active Grant

• Current Assignee: TimeBase Pty Limited (Fiscalnote Holdings, Inc.)
• Original Assignee: TimeBase Pty Limited (Fiscalnote Holdings, Inc.)
• Inventors: Schnelle, Christoph, Nolan, Geoffrey John
• Granted Patent: US 8,204,913 B2
• Time in Patent Office: Days
• US Class Current: 707/100
• 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 method for converting an XML encoded dataset into a minimal set of SQL tables including the steps of:

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 includes 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;

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

allocating to each node a unique node identifier; and

generating an SQL node table containing one or more records, each record corresponding to a respective one of said allocated node identifiers.

2. The method according to claim 1 wherein said XML encoded dataset includes 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 organising said predefined portions of said text-based data.

3. The method according to claim 2, wherein said predefined portions include at least one modified and stored predefined portion encoded using XML, said attributes for organising 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 includes 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 each record of said node table contains said node identifier.

6. The method according to claim 5, wherein each record of said node table further includes 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 includes at least one of a property, sub-node, initial content, and block content.

8. The method according to claim 1 including 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 includes the further step of:

storing said pre-processed content fields in an SQL 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.

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

generating an SQL ancestry table to define 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 includes a descendant node identifier field and an ancestor node identifier field.

12. The method according to claim 1 including 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 includes a sub-property field.

14. An apparatus for converting an XML encoded dataset into a minimal set of SQL tables, said apparatus including:

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

means for converting an XML encoded dataset associated with each identified hierarchical structure, said means including;

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;

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

means for allocating to each node a unique node identifier; and

means for generating an SQL node table containing one or more records, each record corresponding to a respective one of said allocated node identifiers.

15. The apparatus according to claim 14, wherein said XML encoded dataset includes 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 organising said predefined portions of said text-based data.

16. The apparatus according to claim 15, wherein said predefined portions include at least one modified and stored predefined portion encoded using XML, said attributes for organising 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 includes 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 each record of said node table contains said node identifier.

19. The apparatus according to claim 18, wherein each record of said node table further includes 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 includes at least one of a property, sub-node, initial content, and block content.

21. The apparatus according to claim 14 further including:

means 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 includes:

means for storing said pre-processed content fields in an SQL 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.

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

means for generating an SQL ancestry table to define 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 includes a descendant node identifier field and an ancestor node identifier field.

25. The apparatus according to claim 14 further including:

means 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 includes a sub-property field

27. A computer program product having a computer readable medium having a computer program recorded therein for converting an XML encoded dataset into a minimal set of SQL tables, said computer program product including:

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 including;

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;

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 node a unique node identifier, and

computer program code means for generating an SQL node table containing one or more records, each record corresponding to a respective one of said allocated node identifiers.

28. The computer program product according to claim 27, wherein said XML encoded dataset includes 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 organising said predefined portions of said text-based data.

29. The computer program product according to claim 28, wherein said predefined portions include at least one modified and stored predefined portion encoded using XML, said attributes for organising 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 includes 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 each record of said node table contains said node identifier.

32. The computer program product according to claim 31, wherein each record of said node table further includes 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 includes at least one of a property, sub-node, initial content, and block content.

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

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 includes:

computer program code means for storing said pre-processed content fields in an SQL 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.

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 includes:

computer program code means for generating an SQL ancestry table to define 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 includes a descendant node identifier field and an ancestor node identifier field.

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

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 includes a sub-property field.