Lists Home |
Date Index |
"Robert Koberg" <firstname.lastname@example.org> wrote:
> Is using an index position the best way to maintain the position? (or is
preceding and/or following sibling better? or something
How to best map XML into SQL data is a topic somewhat like sorting -- there's no
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.
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 ===========