OASIS Mailing List ArchivesView the OASIS mailing list archive below
or browse/search using MarkMail.


Help: OASIS Mailing Lists Help | MarkMail Help



   Re: [xml-dev] hierarchical XML in a relational DB structure - moving nod

[ Lists Home | Date Index | Thread Index ]

"Robert Koberg" <rob@koberg.com> wrote:
> Is using an index position the best way to maintain the position? (or is
preceding and/or following sibling better? or something
> else?)

How to best map XML into SQL data is a topic somewhat like sorting -- there's no
single solution.

One consideration is whether you're looking for a generic solution that's
interoperable across several SQL platforms. SQL:2003 provides an XML type (a
column of type XML that preserves structure). It's available with DB2 'Viper',
Microsoft SQL Server 2005 and Oracle 10g, but it's not supported by platforms
such as MySQL.

As for a generic solution, one approach is the Adjacency List described by Joe
Celko (link below). He uses a linking column and UNIQUE constraints to ensure
the hierarchical relationship:

Sometimes a simple, intuitive approach is best. If you want to use an outline
model, you need to use unique, ordered node IDs.

Your mapping structure doe not preserve order because you're using integer IDs.
1111-1113 are greater than 112. An SQL query with an ORDER BY nodeID clause
would return nodes 111, 112, 1111, 1112, 1113. One option is 111.1, 111.2 and
111.3 but integer processing is more expensive than floating point calculations,
so an integer numbering scheme is preferable.

myTable includes:

Assuming the example data above,  you can create a slot for a new node between
1111 and 1112 with
UPDATE myTable SET  nodeID = nodeID +1 WHERE nodeID > 1111;

======== Ken North ===========


News | XML in Industry | Calendar | XML Registry
Marketplace | Resources | MyXML.org | Sponsors | Privacy Statement

Copyright 2001 XML.org. This site is hosted by OASIS