System and method for automatic loading of an XML document defined by a document-type definition into a relational database including the generation of a relational schema therefor

• US 7,072,896 B2
• Filed: 02/14/2001
• Issued: 07/04/2006
• Est. Priority Date: 02/16/2000
• Status: Expired due to Term

• Current Assignee: Masonic Holdings Limited Liability Company
• Original Assignee: Verizon Laboratories Incorporated (Verizon Communications Inc.)
• Inventors: Zhang, Xin, Mitchell, Gail Anne, Lee, Wang-Chien
• Primary Examiner(s): Corrielus, Jean M.
• Assistant Examiner(s): Ly, Anh
• Application Number:
  US09/783,657
• Publication Number:
  US 20020169788A1
• Time in Patent Office: 1,966 Days
• Field of Search: 707100-1041, 707 1- 10, 707/200
• US Class Current: 1/1
• CPC Class Codes: G06F 16/284 Relational databases
• CPC Class Codes: G06F 16/986 Document structures and storage, e.g. HTML extensions
• CPC Class Codes: Y10S 707/99942 Manipulating data structure, e.g. compression, compaction, compilation
• CPC Class Codes: Y10S 707/99943 Generating database or data structure, e.g. via user interface
• CPC Class Codes: Y10S 707/99945 Object-oriented database structure processing

Abstract

A system and a method are provided for generating (28) a schema (22) for a relational database corresponding to a document having a document-type definition (18) and data complying with the document-type definition (18). The document-type definition (19) has content particles representative of the structure of the document data including one or more of the following content particles: elements, attributes of elements, nesting relationships between elements, grouping relationships between elements, schema (22) ordering indicators, existence indicators, occurrence indicators and element ID referencing indicators. The system and method described herein also contemplates loading the data into the relational database in a manner consistent with the relational schema (22). Metadata is extracted from the document-type definition (18) representative of the document-type definition (18). The schema (22) for the relational database is generated from the metadata, wherein at least one table is thereby defined in the relational database corresponding to at least one content particle of the document-type definition (18) via the metadata (34). The data is then loaded into the at least one table of the relational dataset according to the relational schema (22) in a manner driven by the metadata (34).

Claims

1. A method for generating a schema for a relational database corresponding to a document having a document-type definition and data complying with the document-type definition, the document-type definition having content particles representative of the structure of the document data, as well as loading the data into the relational database in a manner consistent with the relational schema, the method comprising the steps of:

extracting metadata representative of the document-type definition from the document-type definition;

storing the metadata in at least three tables comprising a metadata item table containing metadata representative of element types in the document-type definition, a metadata attribute table containing metadata representative of attributes in the document type definition, and a metadata nesting table containing metadata representative of nesting relationships between particles in the document type definition;

automatically generating the schema for the relational database from the metadata, wherein at least one table is thereby defined in the relational database corresponding to at least one content particle of the document-type definition via the metadata; and

loading the document data into the at least one table of the relational database according to the relational schema in a manner driven by the metadata.

2. The method of claim 1 wherein the extracting step further comprises the step of creating at least one default item in the item metadata table.

3. The method of claim 2 wherein the extracting step further comprises the step of updating the item metadata table with each of the element type content particles of the document-type definition.

4. The method of claim 3 wherein the extracting step further comprises the step of creating a default attribute value in the attribute metadata table corresponding to attributes of element types in the document-type definition.

5. The method of claim 4 wherein the extracting step further comprises the step of updating the attribute metadata table with each of the attribute type content particles of each element type of the document-type definition.

6. The method of claim 5 wherein the extracting step further comprises the step of generating a row in the nesting metadata table corresponding to each relationship between items identified in the item metadata table.

7. The method of claim 5 wherein the generated nesting table row indicates a relationship between a parent item and a child item.

8. The method of claim 5 wherein the generated nesting table row indicates a relative position of a child item with respect to other items in a definition of the corresponding parent item.

9. The method of claim 5 wherein the generating step further comprises the step of creating at least one table in the schema of the relational database corresponding to at least one row of the metadata item table.

10. The method of claim 9 wherein the generating step further comprises generating at least one default field in the table of the schema.

11. The method of claim 10 wherein the generating step further comprises the step of altering the schema of the relational database to add at least one column to the at least one table in the relational database schema corresponding to each row of the metadata attribute table.

12. The method of claim 11 wherein the generating step further comprises the step of altering the tables in the schema of the relational database to add columns representing links between tables of the relational database schema-corresponding to each relationship identified in each row of the metadata nesting table.

13. The method of claim 12 wherein the generating step further comprises the step of altering the tables in the schema of the relational database by adding a foreign key to a parent table if the identified relationship is a one-b-one relationship.

14. The method of claim 13 wherein the generating step further comprises the step of altering the tables in the schema of the relational database by adding a foreign key to a child table if the identified relationship is a one-to-many relationship.

15. The method of claim 14 and further comprising the step of initializing a link table.

16. The method of claim 14 and further comprising the step of determining whether each item in the metadata nesting table contains a group type.

17. The method of claim 14 and further comprising the step of initializing a pattern-mapping table.

18. The method of claim 17 and further comprising the step of directly mapping a link into the link table for each item in the metadata nesting table that does not contain a group type.

19. The method of claim 18 and further comprising the step of creating an additional link table containing a mapping of a link pattern for each group type identified in the metadata item table.

20. The method of claim 19 and further comprising the step of creating a create topic loading action in the pattern mapping table associated with a particular pattern corresponding to each item in the item metadata table.

21. The method of claim 20 wherein the loading step further comprises the step of creating an update topic loading action in the pattern mapping table associated with a particular pattern corresponding to each attribute in the attribute metadata table.

22. The method of claim 21 wherein the loading step further comprises the steps of:

creating a create triple loading action in the pattern mapping table associated with a particular pattern corresponding to each group in a link; and

creating an assign action triple loading action in the pattern mapping table associated with a particular pattern corresponding to each pair in the same link;

corresponding to each link in the link pattern table.

23. The method of claim 22 wherein the loading step further comprises the step of forming a tree structure with the document data.

24. The method of claim 23 wherein the loading step further comprises the step of traversing the formed tree and updating the at least one relational database table according to the rows of the pattern mapping table.

25. The method of claim 6 wherein the generated nesting table row indicates the cardinality between a pair of items.

26. The method of claim 25 wherein the cardinality is one of one-to-one and one-to-many.

27. The method of claim 1 and further comprising the step of optimizing the metadata.

28. The method of claim 27 wherein the optimizing step further comprises the step of eliminating duplicate particle references in the metadata.

29. The method of claim 28 wherein the optimizing step further comprises the step of simplifying references to corresponding elements, links and attributes in the metadata.

30. The method of claim 29 wherein the optimizing step further comprises the step of inlining particular attributes of the metadata.

31. The method of claim 30, wherein the step of inlining the particular attributes of the metadata reduces a number of nesting relationships and increases a number of attributes in the metadata.

32. The method of claim 1 wherein the document is an XML document.

33. The method of claim 1 wherein the document-type definition is a DTD.

34. The method of claim 1 wherein the data is tagged data.

35. The method of claim 1, and further comprising optimizing the metadata by searching the metadata nesting table for a parent item and a child item having a one-to-one relationship where the child item does not have its own child, and inlining an attribute of the child item into an attribute of the parent item.

36. The method of claim 35, wherein the optimizing step further includes removing the inlined attribute of the child item from the metadata item table.

37. A system for generating a schema for a relational database corresponding to a document having a document-type definition and data complying with the document-type definition, the document-type definition having content particles representative of the structure of the document data, as well as loading the data into the relational database in a manner consistent with the relational schema, the system comprising:

an extractor adapted to read a document-type definition and to extract metadata representative of the document-type definition from the document-type definition, wherein the extractor stores the metadata in at least three tables comprising a metadata item table containing metadata representative of element types in the document-type definition, a metadata attribute table containing metadata representative of attributes in the document type definition, and a metadata nesting table containing metadata representative of nesting relationships between particles in the document type definition;

a generator operably interconnected to the extractor for automatically generating the schema for the relational database from the metadata, wherein at least one table is thereby defined in the relational database corresponding to at least one content particle of the document-type definition via the metadata; and

a loader operably interconnected to the generator for loading the document data into the at least one table of the relational database according to the relational schema in a manner driven by the metadata.

38. The system of claim 37 wherein the extractor creates at least one default item in the item metadata table.

39. The system of claim 38 wherein the extractor generates a row in the item metadata table corresponding to each of the element type content particles of the document-type definition.

40. The system of claim 39 wherein the extractor generates a row in the attribute metadata table corresponding to each of the attribute type content particles of the document-type definition.

41. The system of claim 40 wherein the extractor generates a row in the nesting metadata table corresponding to each relationship identified in the document-type definition between items identified in the item metadata table.

42. The system of claim 41 wherein the generator creates at least one table in the relational database schema of the relational database corresponding to data in the metadata item table.

43. The system of claim 42 wherein the generator alters the schema of the relational database to add a columns to the at least one table of the relational database schema corresponding to each row of the metadata attribute table.

44. The system of claim 43 wherein the generator alters the tables in the schema of the relational database to add columns representing links between tables of the relational database schema corresponding to each relationship identified in each row of the metadata nesting table.

45. The system of claim 44 wherein the generator alters the tables in the schema of the relational database by adding a foreign key to a parent table if a relationship identified between a pair of tables is a one-to-one relationship.

46. The system of claim 45 wherein the generator alters the tables in the schema of the relational database by adding a foreign key to a child table if a relationship identified between a pair of tables is a one-to-many relationship.

47. The system of claim 37 and further comprising a link table.

48. The system of claim 47 wherein the system determines whether each item in the metadata nesting table contains a group type content particle.

49. The system of claim 48 and further comprising a pattern-mapping table in an initialized state.

50. The system of claim 49 wherein the system directly forms a link in the link table for each item in the metadata nesting table that does not contain a group type.

51. The system of claim 50 wherein the loader creates an additional link table containing a mapping of a link pattern for each group type identified in the metadata item table.

52. The system of claim 51 wherein the system retrieves a preselected set of rows corresponding to each item in the metadata item table.

53. The system of claim 52 wherein the system creates a create tuple loading action in the pattern mapping table associated with a particular pattern corresponding to each item in the item metadata table.

54. The system of claim 53 wherein the system creates an update tuple loading action in the pattern mapping table associated with a particular pattern corresponding to each attribute in the attribute metadata table.

55. The system of claim 54 wherein the system:

creates a create tuple loading action in the pattern mapping table associated with a particular pattern corresponding to each group in a link; and

creates an assign action tuple loading action in the pattern mapping table associated with a particular pattern corresponding to each pair in the same link;

wherein each created action corresponds to each link in the link pattern table.

56. The system of claim 55 wherein the loader forms a tree structure with the document data.

57. The system of claim 56 wherein the loader traverses the formed tree structure and updates the at least one relational database table according to the rows of the pattern mapping table.

58. The system of claim 37 and further comprising an optimizer for refining the metadata.

59. The system of claim 58 wherein the optimizer eliminates duplicate particle references in the metadata.

60. The system of claim 59 wherein the optimizer simplifies references to corresponding elements, links and attributes in the metadata.

61. The system of claim 37 wherein the document is an XML document.

62. The system of claim 37 wherein the document-type definition is a DTD.

63. The system of claim 37 wherein the data is tagged data.

64. A system for generating a schema for a relational database corresponding to a document having a document-type definition and data complying with the document-type definition, the document-type definition having content particles representative of the structure of the document data, as well as loading the data into the relational database in a manner consistent with the relational schema, the system comprising:

an extractor adapted to read automatically a document-type definition that extracts metadata representative of the document-type definition from the document-type definition, wherein the extractor stores the metadata in at least three tables comprising a metadata item table containing metadata representative of element types in the document-type definition, a metadata attribute table containing metadata representative of attributes in the document type definition, and a metadata nesting table containing metadata representative of nesting relationships between particles in the document type definition; and

a generator operably interconnected to the extractor for automatically generating the schema for the relational database from the metadata, wherein at least one table is thereby defined in the relational database corresponding to at least one content particle of the document-type definition via the metadata.

65. The system of claim 64 and further comprising a pattern-mapping table initially constructed in an initialized state.

66. The system of claim 65 wherein the pattern mapping table is loaded with actions indicative of relationships between the data and the document-type definition.

67. The system of claim 64 wherein the generator forms a table with at least one default field in the relational database for each item contained in the metadata item table.

68. The system of claim 67 wherein the generator forms a column in a corresponding table in the relational schema corresponding to each attribute in the metadata attribute table linked to an item in the metadata item table.

69. The system of claim 68 wherein the generator forms a link between tables in the relational database corresponding to nesting relationships contained in the metadata nesting table.

70. The system of claim 64 and further comprising a loader operably interconnected to the generator for loading the document data into the at least one table of the relational database according to the relational schema and driven by the metadata.