small medium large xlarge

13 Feb 2012, 16:45


after reading the chapter 3 i have been playing around with closure table model. I´m trying to store a xml file with attributes where tag_id will be a key for ordering and where only leaf nodes has value.

I´m thinking of storing attributes in a dependant table named “attributes” rather than being nodes themself, so each node by using a node_id can find easy their own attributes. In this xml file can be sibling nodes of same tag. For example, i could have two nodes with same tag_id ‘1.1’

However, when a select the nodes(xml tags) by using the sql statement described in the book i dont find the way to select them in such an order that a sibling node with its own children node comes first in the order. This only works if i insert into db nodes in sequence but when i move nodes around or i dont follow a sequence but keepign parernt-children relationship the order is not kept.

For example, when i select ordered by tag_id (different of node_id) the order i get is:

SELECT n.node_id, n.tag_id,, l.value from nodes as n left join leaft_nodes as l on ( n.node_id = l.node_id ) join tree_paths as t on (n.node_id = t.descendant) where t.ancestor = (select node_id from nodes where tag_id = ‘0’ and name=’ROOT_001’) order by tag_id ;


0 1 1 1.1 - has value 1.1 - has value 1.2 - has value 1.2 - has value 2 2 2.1 - has value 2.1 - has value 3 - has value

i would like to achieve something like this:


0 1 1.1 - has value 1.2 - has value 1 1.1 - has value 1.2 - has value 2 2.1 - has value 2 2.1 - has value 3 - has value

that will represent a xml file like this: ` <0> <1> <1.1>has value</1.1> <1.2>has value</1.2> </1> <1> <1.1>has value</1.1> <1.2>has value</1.2> </1> <2> <2.1>has value</2.1> </2> <2> <2.1>has value</2.1> </2> <3>has value</3> </0> `

I´ve been thinking of adding in the nodes table another id like sibling_id to identify brothers of same tag_id, but still i did not come out with a UNIQUE SELECT statement to select as i wish.

Any advice or pointing to an url where something similar is trying to be achieved is really aprreciated, Thanks a lot.


21 Jun 2012, 17:27
Bill Karwin (19 posts)

To apply sibling ordering, you need to create a breadcrumbs string from all the ancestors, and sort by that.

See an example here:

  You must be logged in to comment