Systems and methods for transforming query results into hierarchical information

• US 20040199524A1
• Filed: 07/17/2003
• Published: 10/07/2004
• Est. Priority Date: 03/17/2000
• Status: Active Grant

• Current Assignee: Microsoft Technology Licensing LLC (Microsoft Corporation)
• Original Assignee: Microsoft Corporation
• Inventors: Rys, Michael, Kogan, Yevgeniy L.
• Granted Patent: US 7,213,017 B2
• Time in Patent Office: Days
• US Class Current: 1/1
• CPC Class Codes: G06F 16/258 Data format conversion from or to a database
• CPC Class Codes: G06F 16/84 Mapping; Conversion
• CPC Class Codes: Y10S 707/99934 Query formulation, input preparation, or translation
• CPC Class Codes: Y10S 707/99935 Query augmenting and refining, e.g. inexact access

Abstract

A computerized system and method for transforming (formatting or aggregating) the results of a query into a hierarchical information stream, such as an extensible Markup Language (XML) data stream is disclosed. A database server receives a query and generates a rowset. A rowset processor, using the mode specified in the query, processes the rowset and query to generate the XML data stream. For the “auto1” mode, the rowset processor transforms a rowset into an XML data stream using primary-foreign key information specified in the query to determine nesting. For the “auto2” mode, the rowset processor transforms a rowset into an XML data stream using table ordering information included in the query to determine nesting. For the “explicit” mode, the rowset processor transforms a rowset into an XML data stream using the explicit organizational information specified in the query. In one embodiment, it is easier for a developer to compose expressions to generate nested hierarchical structures than previous formulations and simpler semantics are provided without implicit hierarchy inference. The syntax of an embodiment of the hierarchical formatting function includes optional arguments including a name option, a root option, map option, namespace options and a null option.

Claims

1. A method for generating a hierarchical data stream, comprising:

receiving a query including at least one valid database statement used to retrieve at least one rowset from a database;

processing the query to retrieve said at least one rowset from the database; and

transforming a rowset of said at least one rowset into a datatype instance of the hierarchical language of said hierarchical data stream by mapping each row element of the rowset into an element of the hierarchical data stream.

2. A method according to claim 1, wherein said receiving includes receiving a query including at least one valid database statement used to retrieve at least one rowset from a database and at least one of a name option parameter, a root option parameter, a map option parameter, at least one namespace option parameter and a null option parameter.

3. A method according to claim 2, wherein said receiving includes receiving a query including at least one valid database statement used to retrieve at least one rowset from a database and a name option parameter, whereby said transforming includes overwriting a name of a row element.

4. A method according to claim 3, wherein if said name option parameter is specified to be a predetermined value, then said transforming includes dropping a tag of the row element and any contained attributes.

5. A method according to claim 2, wherein said receiving includes receiving a query including at least one valid database statement used to retrieve at least one rowset from a database and a root option parameter, whereby said mapping includes wrapping a plurality of row elements with a single element tag.

6. A method according to claim 2, wherein said receiving includes receiving a query including at least one valid database statement used to retrieve at least one rowset from a database and a map option parameter, whereby said transforming includes overwriting the row element mapping with a mapping that interprets names as paths.

7. A method according to claim 6, wherein said transforming includes overwriting the row element mapping with a mapping that interprets names as paths and said mapping includes mapping columns in the order in which the columns appear in the rowset to attributes or subelements by interpreting the column names as a path.

8. A method according to claim 2, wherein said receiving includes receiving a query including at least one valid database statement used to retrieve at least one rowset from a database and at least one namespace option parameter and said transforming includes associating a namespace to at least a portion of said hierarchical data stream.

9. A method according to claim 8, wherein said associating includes associating the namespace to the outermost element of the hierarchical data stream generated according to said transforming.

10. A method according to claim 8, wherein said associating includes associating the namespace to at least one top-level element of the hierarchical data stream generated according to said transforming, wherein if there is a plurality of top-level elements, the namespace is added to each of the plurality of top-level elements.

11. A method according to claim 8, wherein said receiving includes receiving a query including a first valid database statement used to retrieve at least one rowset from the database nested within a second valid database statement used to retrieve at least one rowset from the database, whereby said first valid database statement inherits all namespace option parameters associated with said second valid database statement and optionally, said first valid database statement is allowed to overwrite the namespace option parameters of said second valid database statement.

12. A method according to claim 2, wherein said receiving includes receiving a query including at least one valid database statement used to retrieve at least one rowset from a database and a null option parameter, whereby said transforming includes overwriting a default mapping of a NULL value during said transforming.

13. A method according to claim 12, whereby an “

absent”

value of said null option parameter specifies default behavior whereby a null value results in the absence of a row element'"'"'s containing attribute or subelement, whereby an “

xsinil”

value of said null option parameter adds a namespace declaration for a schema instance namespace and whereby an “

empty”

value of said null option parameter represents a null value with an empty string as the value of the attribute or subelement of a row element.

14. A method according to claim 1, wherein said receiving includes receiving a query including a first valid database statement used to retrieve at least one rowset from the database nested within a second valid database statement used to retrieve at least one rowset from the database, whereby said first valid database statement has access to variables of the second valid database statement and said hierarchical data stream is a nested hierarchical data stream.

15. A method according to claim 1, wherein said receiving includes receiving a query formatted according to structured query language (SQL).

16. A method according to claim 1, wherein said transforming includes transforming a rowset of said at least one rowset into an eXtensible markup language (XML) datatype instance of by mapping each row element of the rowset into an element of the XML data stream.

17. A computer readable medium comprising computer executable instructions for performing the method of claim 1.

18. An application programming interface comprising computer executable instructions for performing the method of claim 1.

19. At least one application programming interface comprising computer executable modules having computing executable instructions, the modules including:

a transmission module for transmitting a query request, the query request including at least one valid database statement used to retrieve at least one rowset from a database; and

a hierarchical data stream reception module for receiving a hierarchical data stream generated in response to the query request via transformation of at least one rowset retrieved pursuant to said query request into a datatype instance of the hierarchical language of the hierarchical data stream by mapping each row element of the rowset into an element of the hierarchical data stream.

20. An application programming interface according to claim 19, wherein said transmission module transmits a query request including at least one valid database statement used to retrieve at least one rowset from a database and at least one of a name option parameter, a root option parameter, a map option parameter, at least one namespace option parameter and a null option parameter.

21. An application programming interface according to claim 20, wherein said transmission module transmits a query request including at least one valid database statement used to retrieve at least one rowset from a database and a name option parameter, whereby said transformation includes a process of overwriting a name of a row element.

22. An application programming interface according to claim 20, wherein said transmission module transmits a query request including at least one valid database statement used to retrieve at least one rowset from a database and a root option parameter, whereby said transformation includes a process of wrapping a plurality of row elements with a single element tag.

23. An application programming interface according to claim 20, wherein said transmission module transmits a query request including at least one valid database statement used to retrieve at least one rowset from a database and a map option parameter, whereby said transformation includes a process of overwriting the row element mapping with a mapping that interprets names as paths.

24. An application programming interface according to claim 20, wherein said transmission module transmits a query request including at least one valid database statement used to retrieve at least one rowset from a database and at least one namespace option parameter, whereby said transformation includes a process of associating a namespace to at least a portion of said hierarchical data stream.

25. An application programming interface according to claim 20, wherein said transmission module transmits a query request including at least one valid database statement used to retrieve at least one rowset from a database and a null option parameter, whereby said transformation includes a process of overwriting a default mapping of a NULL value during said transforming.

26. An application programming interface according to claim 19, wherein said receiving includes receiving a query including a first valid database statement used to retrieve at least one rowset from the database nested within a second valid database statement used to retrieve at least one rowset from the database, whereby said first valid database statement has access to variables of the second valid database statement and said hierarchical data stream is a nested hierarchical data stream.

27. An application programming interface according to claim 19, wherein said transmission module transmits a query request formatted according to structured query language (SQL).

28. An application programming interface according to claim 19, wherein said hierarchical data stream reception module for receiving a hierarchical data stream receives an eXtensible markup language (XML) data stream.

29. At least one computing device comprising the transmission and reception modules of the application programming interface of claim 19.

30. A server for managing and accessing database information, including:

a first object that receives a query request;

a second object that retrieves at least one rowset from a database based on at least one valid database statement in the query request; and

a third object that generates a hierarchical data stream by transforming a rowset of said at least one rowset into a datatype instance of the hierarchical language of the hierarchical data stream by mapping each row element of the rowset into an element of the hierarchical data stream.

31. A server according to claim 30, wherein said second object retrieves at least one rowset based on at least one valid database statement and at least one of a name option parameter, a root option parameter, a map option parameter, at least one namespace option parameter and a null option parameter included in the query request.

32. A server according to claim 31, wherein said second object retrieves at least one rowset based on at least one valid database statement and a name option parameter, whereby said transforming performed by said third object includes overwriting a name of a row element.

33. A server according to claim 31, wherein said second object retrieves at least one rowset based on at least one valid database statement and a root option parameter, whereby said transforming performed by said third object includes wrapping a plurality of row elements with a single element tag.

34. A server according to claim 31, wherein said second object retrieves at least one rowset based on at least one valid database statement and a map option parameter, whereby said transforming performed by said third object includes overwriting the row element mapping with a mapping that interprets names as paths.

35. A server according to claim 31, wherein said second object retrieves at least one rowset based on at least one valid database statement and at least one namespace option parameter, whereby said transforming performed by said third object includes associating a namespace to at least a portion of said hierarchical data stream.

36. A server according to claim 31, wherein said second object retrieves at least one rowset based on at least one valid database statement and a null option parameter, whereby said transforming performed by said third object includes overwriting a default mapping of a null value.

37. A server according to claim 30, wherein said query request includes a first valid database statement used to retrieve at least one rowset from the database nested within a second valid database statement used to retrieve at least one rowset from the database, whereby said first valid database statement has access to variables of the second valid database statement and said third object generates a nested hierarchical data stream.

38. A server according to claim 30, wherein said first object receives a query request formatted according to structured query language (SQL).

39. A server according to claim 30, wherein said third object generates an eXtensible markup language (XML) data stream.

40. A server according to claim 30, further comprising a fourth object that transmits said hierarchical data stream to a destination.

41. A server according to claim 40, wherein said destination is specified by said query request received by said first object.

42. A computing device in a computing system for generating a hierarchical data stream, comprising:

means for receiving a query including at least one valid database statement used to retrieve at least one rowset from a database;

means for processing the query to retrieve said at least one rowset from the database; and

means for transforming a rowset of said at least one rowset into a datatype instance of the hierarchical language of said hierarchical data stream by mapping each row element of the rowset into an element of the hierarchical data stream.

43. A computing device according to claim 42, wherein said means for receiving includes means for receiving a query including at least one valid database statement used to retrieve at least one rowset from a database and a map option parameter, whereby said means for transforming overwrites the row element mapping with a mapping that interprets names as paths.

44. A computing device in a computing system for generating a hierarchical data stream, comprising:

means for transmitting a query request, the query request including at least one valid database statement used to retrieve at least one rowset from a database; and

means for receiving a hierarchical data stream generated in response to the query request via transformation of at least one rowset retrieved pursuant to said query request into a datatype instance of the hierarchical language of the hierarchical data stream by mapping each row element of the rowset into an element of the hierarchical data stream.

45. A computing device according to claim 44, wherein said means for transmitting includes means for transmitting a query request including at least one valid database statement used to retrieve at least one rowset from a database and a map option parameter, whereby said transformation includes a process of overwriting the row element mapping with a mapping that interprets names as paths.