Mapping of data from XML to SQL

• US 8,396,901 B2
• Filed: 05/16/2012
• Issued: 03/12/2013
• 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): GIRMA, ANTENEH B
• Application Number:
  US13/473,207
• Publication Number:
  US 20120226720A1
• Time in Patent Office: 300 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 method for mapping an XML encoded dataset into a set of SQL tables comprising:

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

converting said XML encoded dataset associated with said identified hierarchical structure, wherein for said identified hierarchical structure said converting step comprises;

determining a node element set for said identified hierarchical structure associated with said XML encoded dataset, wherein a 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, said node being an instance of a node element;

allocating to said node a unique node identifier; and

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

wherein said XML encoded dataset includes a plurality of predefined portions of text-based data, the plurality of predefined portions of said text-based data being encoded using XML, and being associated with a plurality of attributes for organizing said predefined portions of said text-based data; and

wherein said predefined portions include at least one modified and stored predefined portion encoded using XML, said at least one modified predefined portion being associated with one or more attributes for organizing said predefined portions and said modified predefined portion of said text-based data.

2. The method according to claim 1, wherein said XML encoded dataset further comprises a plurality of links of a markup language, at least one of said predefined portions 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.

3. The method according to claim 1, wherein said record of said node table contains said node identifier.

4. The method according to claim 3, wherein said 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.

5. The method according to claim 1, wherein said node includes at least one of a property, sub-node, initial content, and block content.

6. The method according to claim 1 further comprising:

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

7. The method according to claim 6, wherein for said identified hierarchical structure, said converting step further comprises:

storing said one or more pre-processed content fields in an SQL content table, said record in said content table having a node identifier field containing the corresponding node identifier, a block identifier field and a content field.

8. The method according to claim 1, wherein for said identified hierarchical structure, said converting step further comprises:

generating an SQL ancestry table to define the inter-relationships among nodes of said identified hierarchical structure of said XML encoded dataset.

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

10. The method according to claim 1 further comprising:

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

12. An apparatus for mapping an XML encoded dataset into a set of SQL tables, said apparatus comprising:

at least one processor for identifying at least one hierarchical structure associated with said XML encoded dataset; and

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

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

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

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

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

wherein said XML encoded dataset includes a plurality of predefined portions of text-based data, said predefined portions of said text-based data being encoded using XML, and being associated with a plurality of attributes for organizing said predefined portions of said text-based data; and

wherein said predefined portions include at least one modified and stored predefined portion encoded using XML, said modified predefined portion being associated with one or more attributes for organizing said predefined portions and said modified predefined portion of said text-based data.

13. The apparatus according to claim 12, wherein said XML encoded dataset further comprises a plurality of links of a markup language, at least one of said 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.

14. The apparatus according to claim 12, wherein said record of said node table contains said node identifier.

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

16. The apparatus according to claim 12, wherein said node includes at least one of a property, sub-node, initial content, and block content.

17. The apparatus according to claim 12 further comprising:

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

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

storage for storing said one or more pre-processed content fields in an SQL content table, said record in said content table having a node identifier field containing the corresponding node identifier, a block identifier field and a content field.

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

a generator for generating an SQL ancestry table to define the inter-relationships among nodes of said identified hierarchical structure of said XML encoded dataset.

20. The apparatus according to claim 19, wherein said ancestry table includes 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 SQL 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 includes a sub-property field.

23. A computer program product having a non-transitory computer readable medium, said non-transitory computer readable medium having a computer program recorded therein for mapping an XML encoded dataset into a set of SQL tables, said computer program product comprising:

computer program code for identifying at least one hierarchical structure associated with said XML encoded dataset;

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

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

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

computer program code for allocating to said node a unique node identifier, and

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

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 being associated with a plurality of attributes for organizing said predefined portions of said text-based data; and

wherein said predefined portions include at least one modified and stored predefined portion encoded using XML, said modified predefined portion being associated with one or more attributes for organizing said predefined portions and said modified predefined portion of said text-based data.

24. The computer program product according to claim 23, wherein said XML encoded dataset further comprises a plurality of links of a markup language, at least one of said 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.

25. The computer program product according to claim 23, wherein said record of said node table contains said node identifier.

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

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

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

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

computer program code for storing said one or more pre-processed content fields in an SQL content table, said record in said content table having a node identifier field containing the corresponding node identifier, a block identifier field and a content field.

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

computer program code for generating an SQL ancestry table to define the inter-relationships among nodes of said identified hierarchical structure of said XML encoded dataset.

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

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

computer program code for generating an SQL 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 includes a sub-property field.