mapping a xml file into closure table model and rendering the XML in order

Hi,

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, n.name, 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 ;

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:

tag_id:
—--
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.

Alejandro

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

See an example here:
http://stackoverflow.com/questions/8252323/mysq…