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

• US 20040220927A1
• Filed: 05/01/2003
• Published: 11/04/2004
• 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
• Granted Patent: US 7,103,611 B2
• Time in Patent Office: Days
• US Class Current: 707/4
• 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 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 instances of XML type to represent XML constructs, comprising the steps of:

receiving a SQL statement that includes a particular operator that operates on a first instance of XML type 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 instances of XML type,

wherein

each different instance of XML type in the ordered collection is based on a different XML element from the first set; and

there is an instance of XML type in the ordered collection for every XML element in the first set.

2. A 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 instances of XML type to represent XML constructs, comprising the steps of:

receiving a SQL statement that includes a particular operator that operates on a first instance of XML type 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 instance of XML type 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. 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 instance of XML type to represent the current XML construct; and

associating, in a particular entry in the ordered collection, a current level with the current instance of XML type.

5. The method of claim 4, 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 instance of XML type is the child node of the first node, then generating the current level by incrementing a first level associated with a second instance of XML type that represents the first node.

6. The method of claim 4, 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 instance of XML type that represents the first node.

7. The method of claim 4, 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 instance of XML type 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.

8. A 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 instances of XML type to represent XML constructs, comprising the steps of:

receiving a SQL statement that includes a particular aggregation operator that operates on

a particular instance of XML type of a plurality of instances of XML type to be included in a result instance of XML type that represents an ancestor XML construct, and

a particular level that indicates a level in an XML tree hierarchy for the ancestor XML construct where a particular XML construct represented by the particular instance of XML type is to be placed; and

during execution of the SQL statement, evaluating the particular aggregation operator by inserting the particular instance of XML type into the result instance of XML type at a depth indicated by the level.

9. The method of claim 8, said step of inserting the particular instance of XML type into the result instance further comprising the step of receiving the particular instance of XML type that represents a next node of the XML tree hierarchy, which is subsequent to a first node of the XML tree hierarchy in XML document order, wherein:

the first node is represented by a first instance of XML type;

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

a first level indicates a level of the first node in the XML tree hierarchy; and

10. The method of claim 9, said step of inserting the particular instance of XML type into the result instance further comprising further comprising the steps of:

determining whether the particular level of the particular instance of XML type 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.

11. The method of claim 9, said step of inserting the particular instance of XML type into the result instance further comprising further comprising the steps of:

determining whether the particular level of the particular instance of XML type 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.

12. The method of claim 9, said step of inserting the particular instance of XML type into the result instance further comprising further comprising the steps of:

determining whether the particular level of the particular instance of XML type 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.

13. A computer-readable medium carrying one or more sequences of instructions 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 instances of XML type to represent 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 SQL statement that includes a particular operator that operates on a first instance of XML type 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 instances of XML type,

wherein

each different instance of XML type in the ordered collection is based on a different XML element from the first set; and

there is an instance of XML type in the ordered collection for every XML element in the first set.

14. A computer-readable medium carrying one or more sequences of instructions 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 instances of XML type to represent 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 SQL statement that includes a particular operator that operates on a first instance of XML type 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 instance of XML type 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.

15. A computer-readable medium carrying one or more sequences of instructions 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 instances of XML type to represent 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 SQL statement that includes a particular aggregation operator that operates on

a particular instance of XML type of a plurality of instances of XML type to be included in a result instance of XML type that represents an ancestor XML construct, and

a particular level that indicates a level in an XML tree hierarchy for the ancestor XML construct where a particular XML construct represented by the particular instance of XML type is to be placed; and

during execution of the SQL statement, evaluating the particular aggregation operator by inserting the particular instance of XML type into the result instance of XML type at a depth indicated by the level.