Techniques for retaining hierarchical information in mapping between XML documents and relational data

• US 7,103,611 B2
• Filed: 05/01/2003
• Issued: 09/05/2006
• Est. Priority Date: 05/01/2003
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Manikutty, Anand, Liu, Zhen, Murthy, Ravi, Krishnaprasad, Muralidhar, Warner, James
• Primary Examiner(s): MIZRAHI, DIANE D
• Application Number:
  US10/428,443
• Publication Number:
  US 20040220927A1
• Time in Patent Office: 1,223 Days
• Field of Search: 707 1- 10, 707100-1041, 707200-205, 709/217
• US Class Current: 1/1
• 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 managing XML data in an SQL compliant DBMS include receiving an SQL statement. The SQL statement includes a particular operator that operates on a first instance of XML type that represents a first set of XML elements. During execution of the SQL statement, the particular operator is evaluated by generating an ordered collection of instances of XML type. Each different instance in the ordered collection is based on a different XML element from the first set; and there is an instance in the ordered collection for every XML element from either the first set or from the first set and its descendents. When descendents are included, each entry in the ordered collection indicates a level in the XML tree. In another aspect, an aggregate operator in the SQL statement operates on a collection of instances, with associated levels, to generate a single instance of XML type.

Claims

1. A computer implemented method for converting data between eXtensible Markup Language (XML) constructs and Structured Query Language (SQL) constructs in an SQL compliant database management system (DBMS) that allows data items to represent XML constructs, comprising the steps of:

receiving a SQL statement that includes a particular operator that operates on a first data item that represents a first set of zero or more XML elements; and

during execution of the SQL statement, evaluating the particular operator by generating an ordered collection of zero or more data items,

wherein

each different data item in the ordered collection is based on a different XML element from the first set; and

there is a data item in the ordered collection for every XML element in the first set.

13. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 1.

2. A computer-implemented method for converting hierarchical data between eXtensible Markup Language (XML) constructs and Structured Query Language (SQL) constructs in an SQL compliant database management system (DBMS) that allows data items to represent XML constructs, comprising the steps of:

receiving a SQL statement that includes a particular operator that operates on a first data item that represents at least a first XML construct that includes a first set of one or more XML constructs for which the first XML construct is an ancestor node in an XML tree hierarchy; and

during execution of the SQL statement, evaluating the particular operator by generating an ordered collection of one or more entries,

wherein

each entry in the ordered collection includes

a particular data item that represents a particular XML construct of the first set, and

data that indicates a level of the particular XML construct in the XML tree hierarchy.

3. The method of claim 2, wherein the data that indicates the level indicates a level of the particular XML construct below the first XML construct.

4. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 3.

5. The method of claim 2, said step of generating the ordered collection further comprising the steps of:

receiving from the first set a current XML construct at a next node of the XML tree hierarchy immediately subsequent to a first node of the XML tree hierarchy in XML document order;

generating a current data item to represent the current XML construct; and

associating, in a particular entry in the ordered collection, a current level with the current data item.

6. The method of claim 5, said step of generating the ordered collection further comprising the steps of:

determining whether the next node is a child node of the first node in the XML tree hierarchy; and

if it is determined that the current data item is the child node of the first node, then generating the current level by incrementing a first level associated with a second data item that represents the first node.

7. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 6.

8. The method of claim 5, said step of generating the ordered collection further comprising the steps of:

determining whether the next node is a sibling node of the first node in the XML tree hierarchy; and

if it is determined that the next node is the sibling node of the first node, then generating the current level as equal to a first level associated with a second data item that represents the first node.

9. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 8.

10. The method of claim 5, said step of generating the ordered collection further comprising the steps of:

determining whether the next node is a sibling of an ancestor node of the first node in the XML tree hierarchy; and

if it is determined that the next node is the sibling of the ancestor node, then generating the current level by decrementing a first level associated with a second data item that represents the first node by an amount related to a change in depth between the next node and the first node in the XML tree hierarchy.

11. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 10.

12. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 5.

14. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 2.

15. A computer-implemented method for processing a Structured Query Language (SQL) statement, the method comprising:

detecting that the SQL statement includes a particular operator that specifies an operation for aggregating information from one or more relational tables; and

during execution of the SQL statement that includes the particular operator, reading from the one or more relational tables

attribute values, and

corresponding level values; and

generating hierarchical eXtensible Markup Language (XML) information as a result of said operation;

wherein, within said hierarchical XML information, each of the attribute values belongs to a hierarchical level that is based on the level value that corresponds to the attribute value.

16. The method of claim, 15 wherein:

the step of generating hierarchical XML information includes inserting a particular data item into the result;

the particular data item represents a next node of an XML tree hierarchy, which is subsequent to a first node of the XML tree hierarchy in XML document order;

the first node is represented by a first data item;

a closing tag for an XML construct for the first node has not been inserted into the result; and

the first node resides at a first level in the XML tree hierarchy.

17. The method of claim 16, said step of inserting the particular data item into the result further comprising further comprising the steps of:

determining whether the particular level of the particular data item is one increment greater than the first level; and

if it is determined that the particular level is one increment greater than the first level, then inserting an opening tag for the particular XML construct before inserting a closing tag.

18. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 17.

19. The method of claim 16, said step of inserting the particular data item into the result further comprising further comprising the steps of:

determining whether the particular level of the particular data item is equal to the first level; and

if it is determined that the particular level is equal to the first level, then inserting a closing tag, and

immediately after inserting the closing tag, inserting an opening tag for the particular XML construct.

20. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 19.

21. The method of claim 16, said step of inserting the particular data item into the result further comprising further comprising the steps of:

determining whether the particular level of the particular data item is less than the first level; and

if it is determined that the particular level is less than the first level, then inserting a closing tag, and

decrementing the first level.

22. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 21.

23. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 16.

24. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 15.