[
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:
http://www.sqlsummit.com/AdjacencyList.htm
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:
nodeID=1110
nodeID=1111
nodeID=1112
nodeID=1113
nodeID=1120
nodeID=1121
nodeID=1122
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 ===========
www.WebServicesSummit.com
www.SQLSummit.com
www.GridSummit.com
|
