RE: [xml-dev] hierarchical XML in a relational DB structure - moving nodes

["Nathan Young -X \(natyoung - Artizen at Cisco\)" <natyoung@cisco.com>]

Hi.

I can float a couple of ideas but it's hard to know what's going to work
best for you.

My understanding is that squishing indexes down when you remove one or
more ordered children is a pretty common task.  When you are removing
one or more children in a block you can do "update node_table set
parentIndexPosition = parentIndexPosition - (size of block of removed
nodes) where parentIndex = (the index of the parent) and
parentIndexPosition > (the end point of the removed block)".  Opening
space to add in a child is similar but reversed...

Alternately you can model your table:

node_table
id
id-of-next-sibling
id-of-first-child

This is more of a linked list approach.  It lets you go down into the
data pretty easily and it would let you yank siblings without
re-ordering indexes but implementing anything like an ancestor or
preceding sibling axis is going to be difficult.

---N



.:||:._.:||:._.:||:._.:||:._.:||:._.:||:._.:||:._.:||:._.:||:._.:||:._.:
||:.

Nathan Young
CDC Site Design & Development->Interface Development Team
A: ncy1717
E: natyoung@cisco.com  

> -----Original Message-----
> From: Robert Koberg [mailto:rob@koberg.com] 
> Sent: Wednesday, May 24, 2006 12:53 PM
> To: xml-dev
> Subject: [xml-dev] hierarchical XML in a relational DB 
> structure - moving nodes
> 
> Hi,
> 
> I want to use a relational database to store data that is nested and 
> where the order is preserved. I am having a hard time 
> wrapping my head 
> around how to do the following mostly because I don't have much 
> experience with relational databases.
> 
> I am looking at mapping a structure like so:
> 
> <node id="1">
>    <node id="11">
>      <node id="111">
>        <node id="1111"/>
>        <node id="1112"/>
>        <node id="1113"/>
>      </node>
>      <node id="112"/>
>      <node id="113"/>
>      <node id="114"/>
>    </node>
>    <node id="12">
>      <node id="121"/>
>      <node id="122"/>
>      <node id="123"/>
>    </node>
> </node>
> 
> To a relational database that might have a table representing it like:
> 
> Node
> -------
> id
> parentId
> parentIndexPosition
> 
> First question: Is using an index position the best way to 
> maintain the 
> position? (or is preceding and/or following sibling better? 
> or something 
> else?)
> 
> OK, lets say I have imported some data/XML into the above 
> table, giving 
> appropriate parents and index positions.
> 
> Next, say I want to move /node/node[2]/node[@id=121] to the position 
> /node/node[1]/node[1].
> 
> How do you handle updating the index position of other nodes? All 
> children of /node/node[@id=12] will need their 
> parentIndexPosition moved 
> down one. And all children of /node/node[@id=11] will need their 
> parentIndexPosition moved up one. (the ID attributes where 
> abritrarily 
> created and should not indicate position)
> 
> Is there a built in mechanism to do this type of thing in relational 
> databases? If not how would an UPDATE look?
> 
> Are there any java libraries or articles out there that 
> handle this type 
> of thing? (I am having a hard time finding anything with google)
> 
> thanks,
> -Rob
> 
> 
> -----------------------------------------------------------------
> The xml-dev list is sponsored by XML.org <http://www.xml.org>, an
> initiative of OASIS <http://www.oasis-open.org>
> 
> The list archives are at http://lists.xml.org/archives/xml-dev/
> 
> To subscribe or unsubscribe from this list use the subscription
> manager: <http://www.oasis-open.org/mlmanage/index.php>
>