System and method for loading hierarchical data into relational database systems

• US 20030101194A1
• Filed: 11/01/2001
• Published: 05/29/2003
• Est. Priority Date: 11/01/2001
• Status: Active Grant

• Current Assignee: Microsoft Technology Licensing LLC (Microsoft Corporation)
• Original Assignee: Microsoft Corporation
• Inventors: Rys, Michael, Suver, Chris, Denuit, Bruno, Low, Murray, Burugapalli, Srinivasa
• Granted Patent: US 7,487,168 B2
• Time in Patent Office: Days
• US Class Current: 707/101
• 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 hierarchical data into a first relational table comprising:

identifying a first node within the hierarchical data corresponding to a first column in the relational table and second node associated with the first node corresponding to data to be stored in a row of the table corresponding to the first column;

creating a record in a first buffer associated with the first relational table; and

copying the record from the first buffer to the first relational table.

2. The method as recited in claim 1 wherein the relational table is part of a relational database.

3. The method as recited in claim 1 wherein the hierarchical data is XML data.

4. The method as recited in claim 1 wherein the hierarchical data is identified to correspond to a column in the relational table by way of a hierarchical schema.

5. The method as recited in claim 1 wherein the buffer associated with first table is created after determining that the parent node of the first node within the hierarchical data is associated with the first relational table.

6. The method as recited in claim 1 further comprising a second relational table.

7. The method as recited in claim 5 further comprising creating a second buffer corresponding to the second relational table when a third node within the hierarchal data corresponds to a second relational table.

8. The method as recited in claim 7 further comprising:

creating a record in the second buffer associated with the second relational table when a child node of the third node indicates that the hierarchical data comprises data associated with a column in the second relational table; and

copying the record from the second buffer to the second relational table.

9. The method as recited in claim 8 further comprising copying the record from the first buffer to the first relational table substantially in parallel to copying the record from the second buffer to the second relational table.

10. The method recited in claim 9 wherein the first relational table has a relational relationship with the second relational table.

11. The method as recited in claim 1 wherein the first buffer comprises a disk file.

12. The method as recited in claim 1 wherein the hierarchical data comprises an XML document.

13. A computer-readable medium bearing computer readable instructions for carrying out the method recited in claim 1.

14. A method for loading hierarchical data into at least two relational tables, comprising:

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

mapping the hierarchical data based on the schema and creating records from the hierarchical data from nodes associated identified as data to be stored in the at least one column in each of the at least two relational tables; and

streaming the records into the at least two relational tables.

15. The method as recited in claim 14 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 relational tables.

16. The method as recited in claim 15 wherein the buffer comprises a file.

17. The method as recited in claim 14 wherein the hierarchical data is not size constrained.

18. The method as recited in claim 14 wherein the hierarchical data comprises an XML document.

19. The method as recited in claim 14 wherein the schema comprises an XML schema.

20. The method as recited in claim 14 wherein the at least two tables have a relational relationship.

21. The method as recited in claim 14 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 method further comprises populating records associated with the at least two tables with the data associates with the one of the at least one columns.

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

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

24. A computer-readable medium bearing computer readable instructions for carrying out the method recited in claim 14.

25. A system for loading hierarchical data into at least two relational tables, comprising:

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

instructions for mapping the hierarchical data based on the schema and creating records from the hierarchical data from nodes associated identified as data to be stored in the at least one column in each of the at least two relational tables; and

instructions for streaming the records into the at least two relational tables.

26. The system as recited in claim 25 further comprising instructions for 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 relational tables.

27. The system as recited in claim 26 wherein the buffer comprises a file.

28. The system as recited in claim 25 wherein the hierarchical data comprises an XML document.

29. The system as recited in claim 25 wherein the schema comprises an XML schema.

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

31. The system as recited in claim 25 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.

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