Techniques for rewriting XML queries directed to relational database constructs

• US 7,120,645 B2
• Filed: 05/01/2003
• Issued: 10/10/2006
• Est. Priority Date: 09/27/2002
• Status: Expired due to Term

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Manikutty, Anand, Liu, Zhen, Chandrasekar, Sivasankaran, Murthy, Ravi, Krishnaprasad, Muralidhar, Muralidhar, Subramanian, Warner, James
• Primary Examiner(s): Mizrahi, Diane D.
• Application Number:
  US10/428,878
• Publication Number:
  US 20040064466A1
• Time in Patent Office: 1,258 Days
• Field of Search: 707 1- 10, 707100-102, 707103-1041, 717/106
• US Class Current: 1/1
• CPC Class Codes: G06F 16/8373 Query execution
• CPC Class Codes: G06F 16/86 Mapping to a database
• CPC Class Codes: Y10S 707/99943 Generating database or data structure, e.g. via user interface

Abstract

Techniques for executing database commands include receiving a database command that includes an XML component operation that operates on an XML construct that is based on a first set of one or more relational database constructs. It is determined whether the XML component operation can be transformed to a relational database operation on a particular set of one or more relational database constructs of the first set, which does not involve the XML component operation. If it is determined that the XML component operation can be transformed, then the XML component operation is rewritten as a particular relational database operation that operates on the particular set and that does not involve the XML component operation. The particular relational database operation is evaluated. In another aspect, techniques include determining a primitive set of XML generation operations and replacing non-primitive XML generation operations with one or more operations from the primitive set.

Claims

1. A method for executing database commands that involve operations on extensible markup language (XML) constructs, comprising the computer-implemented steps of:

receiving a database command that includes an XML component operation that operates on an XML construct that is based on a first set of one or more relational database constructs;

wherein the XML component operation is a function that operates on a component identified by an XPath expression;

determining whether the XML component operation can be transformed to a relational database operation on a particular set of one or more relational database constructs of the first set of one or more relational database constructs, wherein the relational database operation does not involve the XML component operation; and

if it is determined that the XML component operation can be transformed to a relational database operation that does not involve the XML component operation, then

rewriting the XML operation as a particular relational database operation that does not involve the XML component operation, and

evaluating the particular relational database operation on the particular set of one or more relational database constructs.

2. The method of claim 1, wherein the first set of one or more relational database constructs are XML types based on a XML schema stored in the relational database.

3. The method of claim 1, said step of determining whether the XML component operation can be transformed to a relational database operation further comprising determining whether an included XPath expression in the XML component operation is at least one of:

a simple XPath expression that steps along only XML nodes on child axes and attribute axes, wherein each XML node corresponds to an object type column or a scalar type column;

a collection traversal expression that steps along only XML nodes on child axes and attribute axes, wherein at least one XML node corresponds to a collection type column;

a wildcard axis expression that results in XML nodes that can all be coerced to a same data type; and

a descendent axis expression that results in XML nodes that can all be coerced to a same data type.

4. The method of claim 3, said step of determining whether the XML component operation can be transformed to a relational database operation further comprising determining whether the included XPath expression is used by a structured query language (SQL)/XML function.

5. The method of claim 4, wherein the SQL/XML function is at least one of EXISTSNODE, EXTRACT, and EXTRACTVALUE.

6. The method of claim 4, said step of determining whether the XML component operation can be transformed to a relational database operation further comprising determining whether the included XPath expression is used by the SQL/XML function in an SQL query in at least one of a SELECT list, a WHERE clause predicate, a GROUP BY expression, an ORDER BY expression, a FROM clause, and a HAVING clause.

7. The method of claim 4, said step of determining whether the XML component operation can be transformed to a relational database operation further comprising determining whether the included XPath expression is used by the SQL/XML function in an INDEX clause of an SQL CREATE INDEX command.

8. The method of claim 3, said step of rewriting the XML component operation further comprising rewriting a simple XPath traversal as at least one of an SQL object type accessor and an SQL scalar type accessor.

9. The method of claim 1, said step of rewriting the XML component operation further comprising rewriting a predicate in an included XPath expression as a predicate in an SQL WHERE clause.

10. The method of claim 1, said step of rewriting the XML component operation further comprising rewriting an EXISTSNODE function of an XPath expression in the database command as an IS NOT NULL test on an object type corresponding to a target of the XPath expression.

11. The method of claim 1, said step of rewriting the XML component operation further comprising rewriting a XPath traversal of a collection as a sub-query on a collection table corresponding to the collection.

12. The method of claim 1, wherein:

the one or more relational database constructs are not XML types; and

said step of receiving a database command that includes an XML component operation includes receiving a database command that includes an XML component operation that operates on an XML construct that is based on an XML type sub-query over the first set of one or more relational database constructs.

13. The method of claim 1, said step of determining whether the XML component operation can be transformed further comprising expanding the XML component operation to a tree of XPath operators that each represent one location step in an XPath expression included in the XML component operation.

14. The method of claim 12, said step of receiving a database command that includes an XML component operation that operates on the XML construct that is based on the XML type sub-query further comprising retrieving the sub-query from an XML type view over the first set of one or more relational database constructs.

15. The method of claim 12, said step of determining whether the XML component operation can be transformed further comprising the steps of:

determining a primitive set of XML generation operations for generating any XML construct from any relational database construct;

determining whether the XML type sub-query involves a particular XML generation operation that is not a member of the primitive set; and

if it is determined that the database command involves the particular XML generation operation that is not a member of the primitive set, then

determining a particular set of one or more XML generation operations from the primitive set that produces a same result as the particular XML generation operation, and

using the particular set of one or more XML generation operations from the primitive set to determine whether the XML component operation can be transformed.

16. The method of claim 15, said step of determining whether the XML component operation can be transformed further comprising expanding the XML component operation to a tree of XPath operators that each represent one location step in an XPath expression included in the XML component operation.

17. The method of claim 16, said step of determining whether the XML component operation can be transformed further comprising forming a normalized tree of one or more XML generation operations based on the particular set of one or more XML generation operations from the primitive set, wherein a normalized tree of one or more XML generation operations contains XML generation operations from the primitive set that each operate on only one of a single scalar operand, a single abstract data type operand, or a set of one or more XML type operands.

18. The method of claim 17, said step of determining whether the XML component operation can be transformed further comprising determining whether a particular node of the tree of XPath operators operating on a particular node of the normalized tree of XML generation operations corresponds to a particular SQL operation on a particular column of the first set of relational database constructs.

19. The method of claim 17, said step of determining whether the XML component operation can be transformed further comprising determining whether a particular node of the tree of XPath operators corresponds to an inverse operation for a particular node of the normalized tree of XML generation operations.

20. The method of claim 18, said step of rewriting further comprising replacing the XPath operation corresponding to the particular node of the tree of XPath operators and the XML generation operation corresponding to the particular node of the tree of XML generation operations with the particular SQL operation on the particular column.

21. The method of claim 19, said step of rewriting further comprising eliminating the XPath operation corresponding to the particular node of the tree of XPath operators and eliminating the XML generation operation corresponding to the particular node of the tree of XML generation operations.

22. The method of claim 18, said step of determining whether the XML component operation can be transformed further comprising applying an appropriate rule from a rule set for replacing a single step XPath operator operating on an XML generation operation of the primitive set.

23. The method of claim 11, said step of rewriting the XML component operation further comprising adding a predicate in the XPath traversal of the collection to a WHERE clause of the sub-query on the collection table.

24. A computer-implemented method for executing database commands that involve generating an extensible markup language (XML) type from relational database constructs, comprising the steps of:

determining a primitive set of XML generation operations for generating any XML construct from a relational database construct;

determining whether a database command involves a particular XML generation operation that is not a member of the primitive set; and

if it is determined that the database command involves the particular XML generation operation that is not a member of the primitive set, then

determining a particular set of one or more XML generation operations from the primitive set that produces a same result as the particular XML generation operation, and

using the particular set of one or more XML generation operations from the primitive set to evaluate the database command.

25. A computer-readable storage medium carrying one or more sequences of instructions for executing database commands that involve operations on extensible markup language (XML) constructs, wherein execution of the one or more sequences of instructions by one or more processors causes the one or more processors to perform the steps of:

receiving a database command that includes an XML component operation for an XML document based on one or more relational database constructs;

wherein the XML component operation is a function that operates on a component identified by an XPath expression;

determining whether the XML component operation can be transformed to a relational database operation on a particular set of one or more relational database constructs of the set of one or more relational database constructs, wherein the relational database operation does not involve the XML component operation; and

if it is determined that the XML component operation can be transformed to a relational database operation that does not involve the XML component operation, then

rewriting the XML component operation to a particular relational database operation that does not involve the XML component operation, and

evaluating the particular relational database operation on the particular set of one or more relational database constructs.

27. The computer-readable storage medium of claim 25, wherein the first set of one or more relational database constructs are XML types based on a XML schema stored in the relational database.

28. The computer-readable storage medium of claim 25, said step of determining whether the XML component operation can be transformed to a relational database operation further comprising determining whether an included XPath expression in the XML component operation is at least one of:

a simple XPath expression that steps along only XML nodes on child axes and attribute axes, wherein each XML node corresponds to an object type column or a scalar type column;

a collection traversal expression that steps along only XML nodes on child axes and attribute axes, wherein at least one XML node corresponds to a collection type column;

a wildcare axis expression that results in XML nodes that can all be coerced to a same data type; and

a descendent axis expression that results in XML nodes that can all be coerced to a same data type.

29. The computer-readable storage medium of claim 28, said step of determining whether the XML component operation can be transformed to a relational database operation further comprising determining whether the included XPath expression is used by a structured query language (SQL)/XML function.

30. The computer-readable storage medium of claim 29, wherein the SQL/XML function is at least one of EXISTSNODE, EXTRACT, and EXTRACTVALUE.

31. The computer-readable storage medium of claim 29, said step of determining whether the XML component operation can be transformed to a relational database operation further comprising determining whether the included XPath expression is used by the SQL/XML function in an SQL query in at least one of a SELECT list, a WHERE clause predicate, a GROUP BY expression, an ORDER BY expression, a FROM clause, and a HAVING clause.

32. The computer-readable storage medium of claim 29, said step of determining whether the XML component operation can be transformed to a relational database operation further comprising determining whether the included XPath expression is used by the SQL/XML function in an INDEX clause of an SQL CREATE INDEX command.

33. The computer-readable storage medium of claim 28, said step of rewriting the XML component operation further comprising rewriting a simple XPath traversal as at least one of an SQL object type accessor and an SQL scalar type accessor.

34. The computer-readable storage medium of claim 25, said step of rewriting the XML operation further comprising rewriting a predicate in an included XPath expression as a predicate in an SQL WHERE clause.

35. The computer-readable storage medium of claim 25, said step of rewriting the XML component operation further comprising rewriting an EXISTSNODE function of an XPath expression in the database command as an IS NOT NULL test on an object type corresponding to a target of the XPath expression.

36. The computer-readable storage medium of claim 25, said step of rewriting the XML component operation further comprising rewriting a XPath traversal of a collection as a sub-query on a collection table corresponding to the collection.

37. The computer-readable storage medium of claim 25, wherein:

the one or more relational database constructs are not XML types; and

said step of receiving a database command that includes an XML component operation includes receiving a database command that includes an XML component operation that operates on an XML construct that is based on an XML type sub-query over the first set of one or more relational database constructs.

38. The computer-readable storage medium of claim 25, said set of determining whether the XML component operation can be transformed futhter comprising expanding the XML component operation to a tree of XPath operators that each represent one location step in an XPath expression included in the XML component operation.

39. The computer-readable storage medium of claim 37, said step of receiving a database command that includes an XML component operation that operates on the XML construct that is based on the XML type sub-query futher comprising retrieving the sub-query from an XML type view over the first set of one or more relational database constructs.

40. The computer-readable storage medium of claim 37, said step of determining whether the XML component operation can be transformed further comprising the steps of:

determining a primitive set of XML generation operations for generating any XML construct from any relational database construct;

determining whether the XML type sub-query involves a particular XML generation operation that is not a member of the primitive set; and

if it is determined that the database command involves the particular XML generation operation that is not a member of the primitive set, then

determining a particular set of one or more XML generation operations from the primitive set that produces a same result as the particular XML generation operation, and

using the particular set of one or more XML generation operations from the primitive set to determine whether the XML component operation can be transformed.

41. The computer-readable storage medium of claim 40, said step of determining whether the XML component operation can be transformed further comprising expanding the XML component operation to a tree of XPath operators that each represent one location step in an XPath expression included in the XML component operation.

42. The computer-readable storage medium of claim 41, said step of determining whether the XML component operation can be transformed further comprising forming a normalized tree of one or more XML generation operations based on the particular set of one or more XML generation operations from the primitive set, wherein a normalized tree of one or more XML generation operations contains XML generation operations from the primitive set that each operate on only one of a single scalar operand, a single abstract data type operand, or a set of one or more XML type operands.

43. The computer-readable storage medium of claim 42, said step of determining whether the XML component operation can be transformed further comprising determining whether a particular node of the tree of XPath operators operating on a particular node of the normalized tree of XML generation operations corresponds to a particular SQL operation on a particular column of the first set of relational database constructs.

44. The computer-readable storage medium of claim 42, said step of determining whether the XML component operation can be transformed further comprising determining whether a particular node of the tree of XPath operators corresponds to an inverse operation for a particular node of the normalized tree of XML generation operations.

45. The computer-readable storage medium of claim 43, said step of rewriting further comprising replacing the XPath operation corresponding to the particular node of the tree of XPath operators and the XML generation corresponding to the particular node of the tree of XML generation operations with the particular SQL operation on the particular column.

46. The computer-readable storage medium of claim 44, said step of rewriting further comprising eliminating the XPath operation corresponding to the particular node of the tree of XPath operators and eliminating the XML generation operation corresponding to the particular node of the tree of XML generation operations.

47. The computer-readable storage medium of claim 43, said step of determining whether the XML component operation can be transformed further comprising applying an appropriate rule from a rule set for replacing a single step XPath operator on an XML generation operation of the primitive set.

48. The computer-readable storage medium of claim 36, said step of rewriting the XML component operation further comprising adding a predicate in the XPath traversal of the collection to a WHERE clause of the sub-query on the collection table.

26. A computer-readable storage medium carrying one or more sequences of instructions for executing database commands that involve generating an extensible markup language (XML) type from relational database constructs, wherein execution of the one or more sequences of instructions by one or more processors causes the one or more processors to perform the steps of:

determining a primitive set of XML generation operations for generating any XML construct from a relational database construct;

determining whether a database command involves a particular XML generation operation that is not a member of the primitive set; and

if it is determined that the database command involves the particular XML generation operation that is not a member of the primitive set, then

determining a particular set of one or more XML generation operations from the primitive set that produces a same result as the particular XML generation operation, and

using the particular set of one or more XML generation operations from the primitive set to evaluate the database command.