A nested set approach for building recursive XML tree structures from relational databases

Extensible Markup Language (XML) is a popular format for the interchange of data between disparate, heterogeneous systems. However, when hierarchical data is exchanged between relational database management systems (RDBMSs), the full expressive power of XML is often ignored. A major factor is the inability of RDBMSs to manipulate hierarchical data, such as an organization chart. We address this by replacing traditional link tables with a nested set approach, which has several benefits: SQL queries may be used to extract data subtrees and perform aggregate functions on them; nested set data may be converted to and from the hierarchical XML form; and the hierarchical structure is explicitly included with the data. It is important for servers to be able to manipulate subtrees quickly and efficiently in order to fill data requests from their clients. In this paper, we adapt a nested set approach to include recursive, hierarchical XML data. An algorithm is presented to use nested set information to restore the original XML document without requiring the entire tree to be reconstructed by a procedural language, and it is tested and evaluated with a prototype application. Our approach allows two relational systems to efficiently exchange subtree data as XML hierarchical data instead of merely using XML as a wrapper around relational data.