Method and system for implementing efficient updatable relational views over XML data

• US 9,396,284 B2
• Filed: 05/18/2011
• Issued: 07/19/2016
• Est. Priority Date: 05/18/2011
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Arora, Vikas, Baby, Thomas, Liu, Zhen Hua, Yu, Qin
• Primary Examiner(s): SYED, FARHAN M
• Application Number:
  US13/110,671
• Publication Number:
  US 20120296942A1
• Time in Patent Office: 1,889 Days
• US Class Current: 1/1
• CPC Class Codes: G06F 16/832 Query formulation
• CPC Class Codes: G06F 16/8358 Query translation

Abstract

Disclosed is a method, system, and program product for providing an efficient mechanism to provide updateable relational views over XML. This will allow application developers to write both queries and updates using a relational (SQL) syntax, even though the underlying data is stored as XML. According to some approaches, this is accomplished by implementing a reverse mapping from the data within the views to the node addresses of the corresponding data in the underlying XML document.

Claims

1. A computer implemented method for implementing an updateable relational view over XML, data, comprising:

generating the updateable relational view to respond to both SQL query operations and SQL update operations, the updateable relational view comprising one or more rows and one or more columns, wherein data within the one or more rows and the one or more columns are populated from underlying XML data that is not stored in a relational format;

using a processor to generate an index table for mapping node addresses within the underlying XML data to the updateable relational view, an entry in the index table relates a node in the underlying XML data to an item of the data in the updateable relational view, wherein the entry in the index table comprises a column that includes a node address for the node in the underlying XML data;

receiving a SQL update operation, wherein the SQL update operation includes an update is directed to the updateable relational view and not to the underlying XML data;

identifying the item of the data for updating in the updatable relational view to implement the SQL update operation, the item of the data corresponding to the node in the underlying XML data;

using the index table to identify the node address of the node in the underlying XML data; and

updating the node at the node address within the underlying XML data to implement the update made to the updateable relational view by the SQL update operation.

2. The method of claim 1 in which the SQL update operation comprises a SQL statement.

3. The method of claim 1 in which the index table is created using an index definition, wherein the index definition corresponds to an XML table definition used to create the updateable relation view.

4. The method of claim 1 in which a row expression expressed with either XPath or XQuery is used to construct the index table.

5. The method of claim 1 in which a predicate within the SQL update operation is used to filter the node addresses to perform the update operation.

6. The method of claim 1 in which the SQL update operation corresponds to an update statement or a delete statement for the updateable relational view.

7. The method of claim 1 in which the updateable relational view comprises an XMLTABLE( ) construct.

8. The method of claim 1 in which a node address points to a subtree of elements in the XML data.

9. The method of claim 1 in which a callback or reverse conversion function is used to perform an update operation that corresponds to transient XML values.

10. A computer program product embodied on a non-transitory computer usable medium, the non-transitory computer readable medium having stored thereon a sequence of instructions which, when executed by a processor causes the processor to execute a method for implementing an updateable relational view over XML data, the method comprising:

generating the updateable relational view to respond to both SQL query operations and SQL update operations, the updateable relational view comprising one or more rows and one or more columns, wherein data within the one or more rows and the one or more columns are populated from underlying XML data that is not stored in a relational format;

using a processor to generate an index table for mapping node addresses within the underlying XML data to the updateable relational view, wherein an entry in the index table relates a node in the underlying XML data to an item of the data in the updateable relational view, wherein the entry in the index table comprises a column that includes a node address for the node in the underlying XML data;

receiving a SQL update operation, wherein the SQL update operation includes an update is directed to the updateable relational view and not to the underlying XML data;

identifying the item of the data for updating in the updatable relational view to implement the SQL update operation, the item of the data corresponding to the node in the underlying XML data;

using the index table to identify the node address of the node in the underlying XML data; and

updating the node at the node address within the underlying XML data to implement the update made to the updateable relational view by the SQL update operation.

11. The computer program product of claim 10 in which the SQL update operation comprises a SQL statement.

12. The computer program product of claim 10 in which the index table is created using an index definition, wherein the index definition corresponds to an XML table definition used to create the updateable relation view.

13. The computer program product of claim 10 in which a row expression expressed with either XPath or XQuery is used to construct the index table.

14. The computer program product of claim 10 in which a predicate within the SQL update operation is used to filter the node addresses to perform the update operation.

15. The computer program product of claim 10 in which the SQL update operation corresponds to an update statement or a delete statement for the updateable relational view.

16. The computer program product of claim 10 in which the updateable relational view comprises an XMLTABLE( ) construct.

17. The computer program product of claim 10 in which a node address points to a subtree of elements in the XML data.

18. The computer program product of claim 10 in which a callback or reverse conversion function is used to perform an update operation that corresponds to transient XML values.

19. A computer-based system for implementing an updateable relational view over XML data, comprising:

a computer processor to execute a set of program code instructions;

a memory to hold the program code instructions, in which the program code instructions comprises program code to;

generate the updateable relational view to respond to both SQL query operations and SQL update operations, the updateable relational view comprising one or more rows and one or more columns, wherein data within the one or more rows and the one or more columns are populated from underlying XML data that is not stored in a relational format;

use the processor to generate an index table for mapping node addresses within the underlying XML data to the updateable relational view, an entry in the index table relates a node in the underlying XML data to an item of the data in the updateable relational view, wherein the entry in the index table comprises a column that includes a node address for the node in the underlying XML data;

receive a SQL update operation, wherein the SQL update operation includes an update is directed to the updateable relational view and not to the underlying XML data;

identify the item of the data for updating in the updatable relational view to implement the SQL update operation, the item of the data corresponding to the node in the underlying XML, data;

use the index table to identify the node address of the node in the underlying XML data; and

update the node at the node address within the underlying XML data to implement the update made to the updateable relational view by the SQL update operation.

20. The system of claim 19 in which the SQL update operation comprises a SQL statement.

21. The system of claim 19 in which the index table is created using an index definition, wherein the index definition corresponds to an XML table definition used to create the updateable relation view.

22. The system of claim 19 in which a row expression expressed with either XPath or XQuery is used to construct the index table.

23. The system of claim 19 in which a predicate within the SQL update operation is used to filter the node addresses to perform the update operation.

24. The system of claim 19 in which the SQL update operation corresponds to an update statement or a delete statement for the updateable relational view.

25. The system of claim 19 in which the updateable relational view comprises an XMLTABLE( ) construct.

26. The system of claim 19 in which a node address points to a subtree of elements in the XML data.

27. The system of claim 19 in which a callback or reverse conversion function is used to perform an update operation that corresponds to transient XML values.

28. A computer implemented method for updating data within an updateable relational view that is constructed over XML data, comprising:

identifying the updateable relational view to respond to a SQL update operation, the updateable relational view comprising one or more rows and one or more columns, wherein data within the one or more rows and the one or more columns are populated from underlying XML data that is not stored in a relational format;

receiving an instruction to perform the SQL update operation, wherein the SQL update operation includes an update directed to the updateable relational view rather than to the underlying XML data;

identifying an item of the data for updating in the updatable relational view to implement the SQL update operation, the item of the data corresponding to a node in the underlying XML data;

using a processor to perform the SQL update operation on the item of the data in the updatable relational view; and

using a processor to update the node in the underlying XML data to implement the update made to the updateable relational view by the SQL update operation, wherein a reverse lookup is performed on an index table to identify a node address of the node in the underlying XML data corresponding to the item of the data in the updateable relational view, wherein an entry in the index table comprises a column that includes the node address for the node in the underlying XML data.

29. The method of claim 28 in which the SQL update operation comprises a SQL statement.

30. The method of claim 28 in which the index table is created using an index definition, wherein the index definition corresponds to an XML table definition used to create the updateable relation view.

31. The method of claim 28 in which a predicate within the SQL update operation is used to filter the node addresses to perform the update operation.

32. The method of claim 28 in which the update operation corresponds to an update statement or a delete statement for the updateable relational view.

33. The method of claim 28 in which the updateable relational view comprises an XMLTABLE( ) construct.

34. The method of claim 28 in which a node address points to a subtree of elements in the XML data.

35. The method of claim 28 in which a callback or reverse conversion function is used to perform an update operation that corresponds to transient XML values.

36. A computer-based system for updating data within an updateable relational view that is constructed over XML data, comprising:

a computer processor to execute a set of program code instructions;

a memory to hold the program code instructions, in which the program code instructions comprises program code to;

identify the updateable relational view to respond to a SQL update operation, the updateable relational view comprising one or more rows and one or more columns, wherein data within the one or more rows and the one or more columns are populated from underlying XML data that is not stored in a relational format;

receive an instruction to perform the SQL update operation, wherein the SQL update operation includes an update directed to the updateable relational view rather than to the underlying XML data;

identify an item of the data for updating in the updatable relational view to implement the SQL update operation, the item of the data corresponding to a node in the underlying XML data;

use the processor to perform the SQL update operation on the item of the data in the updatable relational view; and

use the processor to update the node in the underlying XML data to implement the update made to the updateable relational view by the SQL update operation, wherein a reverse lookup is performed on an index table to identify a node address of the node in the underlying XML data corresponding to the item of the data in the updateable relational view, wherein an entry in the index table comprises a column that includes the node address for the node in the underlying XML.

37. A computer program product embodied on a non-transitory computer usable medium, the non-transitory computer readable medium having stored thereon a sequence of instructions which, when executed by a processor causes the processor to execute a method for updating data within an updateable relational view that is constructed over XML data, the method comprising:

identifying the updateable relational view to respond to a SQL update operation, the updateable relational view comprising one or more rows and one or more columns, wherein data within the one or more rows and the one or more columns are populated from underlying XML data that is not stored in a relational format;

receiving an instruction to perform the SQL update operation, wherein the SQL update operation includes an update directed to the updateable relational view rather than to the underlying XML data;

identifying an item of the data for updating in the updatable relational view to implement the SQL update operation, the item of the data corresponding to a node in the underlying XML data;

using a processor to perform the SQL update operation on the item of the data in the updatable relational view; and

using a processor to update the node in the underlying XML data to implement the update made to the updateable relational view by the SQL update operation, wherein a reverse lookup is performed on an index table to identify a node address of the node in the underlying XML data corresponding to the item of the data in the updateable relational view, wherein an entry in the index table comprises a column that includes the node address for the node in the underlying XML data.