Method and apparatus for flexible storage and uniform manipulation of XML data in a relational database system

• US 20020078068A1
• Filed: 09/06/2001
• Published: 06/20/2002
• Est. Priority Date: 09/07/2000
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Krishnamurthy, Viswanathan, Murthy, Ravi, Krishnaprasad, Muralidhar
• Granted Patent: US 7,024,425 B2
• Time in Patent Office: Days
• US Class Current: 707/104.1
• CPC Class Codes: G06F 16/284 Relational databases
• CPC Class Codes: Y10S 707/99933 Query processing, i.e. searching
• 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
• CPC Class Codes: Y10S 707/99948 Application of database or data structure, e.g. distributed, multimedia, or image

Abstract

Techniques are provided for XML data storage and query rewrites in relational databases. According to certain embodiments of the invention, a mechanism is provided to allow the user to store an XML document in a relational database and to submit mapping information that indicates a mapping of each field of the XML document to the column in the relational database in which the data from each field is stored. If the user submits an XML query to access the data in the XML document that is stored in the relational database, then a mechanism is provided to generate a database query based on the XML query and the mapping information.

Claims

1. A method for managing data in a relational database, the method comprising the steps of:

receiving at a database server an XML document for storage in the relational database;

storing in the relational database mapping information that indicates a mapping of one or more fields in the XML document to corresponding columns in the relational database;

storing data from the XML document in the relational database in locations that are determined based on the mapping information; and

in response to the database server receiving a request for data from the XML document, inspecting the mapping information to determine how to access the data from the XML document.

2. The method of claim 1 further comprising the steps of:

determining which corresponding columns in the relational database to use to store data from fields of the XML document; and

generating the mapping information of the one or more fields in the XML document to corresponding columns in the relational database.

3. The method of claim 1, wherein the step of storing data includes the steps of:

storing values for a plurality of fields of the XML document into a first column of a relational table; and

storing values for at least one field of the XML document into a second column of the relational table, wherein the second column is different from said first column.

4. The method of claim 1, wherein a request for data is an XML query.

5. The method of claim 4, further comprising the steps of:

generating a database query based on the XML query and the mapping information; and

executing the database query to access data from the XML document.

6. The method of claim 5, wherein the database query directly accesses the underlying relational columns and uses indexes.

7. The method of claim 6, wherein the indexes are relational indexes.

8. The method of claim 6, wherein the indexes are text indexes.

9. The method of claim 1, wherein a structure of the XML document is defined by a user to contain one or more fields of structured data, and wherein structured data is data that is of a type that corresponds to an existing relational data object type in the relational database.

10. The method of claim 1, wherein a structure of the XML document is defined by a user to contain one or more fields of unstructured data, and wherein unstructured data is data that is mapped to a LOB data type in the relational database during processing of the XML document for storage in the relational database.

11. The method of claim 1, wherein the mapping information is defined by a user.

12. The method of claim 11, wherein the user has an option to map more than one field of the XML document to each column of the relational database for storage.

13. The method of claim 1, wherein an XMLType datatype is defined by a database server that manages the relational database for storing the XML document.

14. The method of claim 13, wherein a set of operations are defined for the XMLType datatype by the database server.

15. The method of claim 14, wherein the set of operations comprises:

extracting fragments of the XML document;

testing for existence of one or more elements in the XML document;

extracting one or more values from the XML document; and

transforming the XML document.

16. The method of claim 1, wherein:

the mapping information maps a field of said XML document to a plurality of columns of a relational table; and

the step of storing data from the XML document includes parsing said field of said XML document to identify a plurality of values and storing each of the plurality of values in a different one of said plurality of columns of the relational table.

17. The method of claim 1, wherein:

the request specifies criteria for a field of the XML document;

the method includes determining whether the criteria is satisfied by performing the steps of;

determining, based on said mapping information, that the field is one of a plurality of fields that is mapped to a particular column;

parsing data in said particular column to locate a value of said field for said XML document; and

determining whether said value satisfies the criteria.

19. The computer-readable medium of claim 18 further comprising the steps of:

determining which corresponding columns in the relational database to use to store data from fields of the XML document; and

generating the mapping information of the one or more fields in the XML document to corresponding columns in the relational database.

20. The computer-readable medium of claim 18, wherein the step of storing data includes the steps of:

storing values for a plurality of fields of the XML document into a first column of a relational table; and

storing values for at least one field of the XML document into a second column of the relational table, wherein the second column is different from said first column.

21. The computer-readable medium of claim 18, wherein a request for data is an XML query.

22. The computer-readable medium of claim 21, further comprising the steps of:

generating a database query based on the XML query and the mapping information; and

executing the database query to access data from the XML document.

23. The computer-readable medium of claim 22, wherein the database query directly accesses the underlying relational columns and uses indexes.

24. The computer-readable medium of claim 23, wherein the indexes are relational indexes.

25. The computer-readable medium of claim 23, wherein the indexes are text indexes.

26. The computer-readable medium of claim 18, wherein a structure of the XML document is defined by a user to contain one or more fields of structured data, and wherein structured data is data that is of a type that corresponds to an existing relational data object type in the relational database.

27. The computer-readable medium of claim 18, wherein a structure of the XML document is defined by a user to contain one or more fields of unstructured data, and wherein unstructured data is data that is mapped to a LOB data type in the relational database during processing of the XML document for storage in the relational database.

28. The computer-readable medium of claim 18, wherein the mapping information is defined by a user.

29. The computer-readable medium of claim 28, wherein the user has an option to map more than one field of the XML document to each column of the relational database for storage.

30. The computer-readable medium of claim 18, wherein an XMLType datatype is defined by a database server that manages the relational database for storing the XML document.

31. The computer-readable medium of claim 30, wherein a set of operations are defined for the XMLType datatype by the database server.

32. The computer-readable medium of claim 31, wherein the set of operations comprises:

extracting fragments of the XML document;

testing for existence of one or more elements in the XML document;

extracting one or more values from the XML document; and

transforming the XML document.

33. The computer-readable medium of claim 18, wherein:

the mapping information maps a field of said XML document to a plurality of columns of a relational table; and

the step of storing data from the XML document includes parsing said field of said XML document to identify a plurality of values and storing each of the plurality of values in a different one of said plurality of columns of the relational table.

34. The computer-readable medium of claim 18, wherein:

the request specifies criteria for a field of the XML document;

the method includes determining whether the criteria is satisfied by performing the steps of;

determining, based on said mapping information, that the field is one of a plurality of fields that is mapped to a particular column;

parsing data in said particular column to locate a value of said field for said XML document; and

determining whether said value satisfies the criteria.

18. A computer-readable medium carrying one or more sequences of instructions for managing data within a relational database, which instructions, when executed by one or more processors, cause the one or more processors to carry out the steps of:

receiving at a database server an XML document for storage in the relational database;

storing in the relational database mapping information that indicates a mapping of one or more fields in the XML document to corresponding columns in the relational database;

storing data from the XML document in the relational database in locations that are determined based on the mapping information; and

in response to the database server receiving a request for data from the XML document, inspecting the mapping information to determine how to access the data from the XML document.