[XML-DEV Mailing List Archive Home] [By Thread] [By Date] [Recent Entries] [Reply To This Message]

Re: hierarchical XML in a relational DB structure - moving nod

• To: "xml-dev" <xml-dev@l...>
• Subject: Re: hierarchical XML in a relational DB structure - moving nodes
• From: "Ken North" <kennorth@s...>
• Date: Wed, 24 May 2006 14:22:02 -0700
• References: <4474B9A4.8010400@k...>

"Robert Koberg" <rob@k...> 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

• References:
  • hierarchical XML in a relational DB structure - moving nodes
    • From: Robert Koberg <rob@k...>

• Prev by Date: RE: hierarchical XML in a relational DB structure - moving nodes
• Next by Date: Re: hierarchical XML in a relational DB structure - moving nodes
• Previous by thread: Re: hierarchical XML in a relational DB structure - moving nodes
• Next by thread: RE: hierarchical XML in a relational DB structure - moving nodes
• Index(es):
  • Date
  • Thread