System and method for loading hierarchical data into relational database systems

• US 7,487,168 B2
• Filed: 11/01/2001
• Issued: 02/03/2009
• Est. Priority Date: 11/01/2001
• Status: Active Grant

• Current Assignee: Microsoft Technology Licensing LLC (Microsoft Corporation)
• Original Assignee: Microsoft Corporation
• Inventors: Denuit, Bruno, Low, Murray, Burugapalli, Srinivasa, Rys, Michael, Suver, Chris
• Primary Examiner(s): ROBINSON, GRETA LEE
• Application Number:
  US10/001,289
• Publication Number:
  US 20030101194A1
• Time in Patent Office: 2,651 Days
• Field of Search: 707/100, 707/102, 707/101, 707/10, 707200-205, 707/1, 709/201, 709/231
• US Class Current: 1/1
• CPC Class Codes: G06F 16/2386 Bulk updating operations data conversion details G06F16/258
• CPC Class Codes: G06F 16/258 Data format conversion from or to a database
• CPC Class Codes: G06F 16/86 Mapping to a database
• CPC Class Codes: Y10S 707/99942 Manipulating data structure, e.g. compression, compaction, compilation

Abstract

Users and programmers can load semi-structured hierarchical data, such as XML data, of arbitrary size and structure into relational database tables using an schema such as an SQL-annotated XML schema. The schema describes how XML data maps into base tables and fields, including complex one-to-many and many-to-many relationships. The hierarchical data is streamed into the relational database tables in a single operation.

Claims

1. A method for loading data organized in a hierarchical arrangement into at least two different relational tables, comprising:

receiving a schema describing a relationship of nodes in the data organized in a hierarchical arrangement to at least one column in each of the at least two different relational tables;

mapping the data organized in a hierarchical arrangement to the at least one column in each of the at least two different relational tables based on the schema and creating in at least two different files where each file is associated with one of at least two different relational tables records from the data organized in a hierarchical arrangement from nodes identified as data to be stored in the at least one column in each of the at least two different relational tables; and

streaming the records into the at least two different relational tables by inserting the records from the at least two different files into corresponding ones of the at least two different relational tables as each record is determined to be complete.

2. The method as recited in claim 1 further comprising creating a buffer for each of the at least two relational tables wherein the records are stored before being streamed into the at least two different relational tables.

3. The method as recited in claim 1 wherein the data organized in a hierarchical arrangement is not size constrained.

4. The method as recited in claim 1 wherein the data organized in a hierarchical arrangement comprises an XML document.

5. The method as recited in claim 1 wherein the schema comprises an XML schema.

6. The method as recited in claim 1 wherein the at least two different tables have a relational relationship.

7. The method as recited in claim 6 wherein one of the at least one columns is a key field in one of the at least two different tables and foreign key in the other one of the at least two different tables, wherein the method further comprises populating records associated with the at least two different tables with the data associates with the one of the at least one columns.

8. The method as recited in claim 1 further comprising streaming the record into the at least two different relational tables substantially in parallel.

9. The method as recited in claim 1 wherein the schema is received by way of a network connection.

10. A system for loading data organized in a hierarchical arrangement into at least two different relational tables, comprising:

instructions for receiving a schema describing a relationship of nodes in the data organized in a hierarchical arrangement to at least one column in each of the at least two different relational tables;

instructions for mapping the data organized in a hierarchical arrangement to the at least one column in each of the at least two different relational tables based on the schema and creating at least two different files where each file is associated with one of at least two different relational tables records from the data organized in a hierarchical arrangement from nodes associated identified as data to be stored in the at least one column in each of the at least two different relational tables; and

instructions for streaming the records into the at least two different relational tables by inserting the records from the at least two different files into corresponding ones of the at least two different relational tables as each record is determined to be complete.

11. The system as recited in claim 10 further comprising instructions for creating a buffer for each of the at least two different relational tables wherein the records are stored before being streamed into the at least two different relational tables.

12. The system as recited in claim 11 wherein the buffer comprises a file.

13. The system as recited in claim 10 wherein the data organized in a hierarchical arrangement comprises an XML document.

14. The system as recited in claim 10 wherein the schema comprises an XML schema.

15. The system as recited in claim 10 wherein the at least two tables have a relational relationship.

16. The system as recited in claim 10 wherein one of the at least one columns is a key field in one of the at least two tables and foreign key in the other one of the at least two tables, wherein the system further comprises instructions for populating records associated with the at least two tables with the data associates with the one of the at least one columns.

17. The system as recited in claim 10 further comprising instructions for streaming the record into the at least two relational tables substantially in parallel.