[SQL] Get order nodes with childrens in XML XPATH POSTGRES

i need your great knowledge for this that is killing me (i'm sorry for my english).

I have some XML in a table (let's call xml_table), I need get the parents with his children in perfect order but it doesn't happend. I'll simplify problem.

This is my XML:

<group name="A">
<category target_id="1001">
<category flow_id="0" />
<category flow_id="1" />
<category flow_id="2" />
</category>
</group>

<group name="B">
<category target_id="1002">
<category flow_id="0" />
</category>
<category target_id="1003">
<category flow_id="0" />
</category>
<category target_id="1004">
<category flow_id="0" />
</category>
<category target_id="1005">
<category flow_id="0" />
</category>
</group>

<group name="C">
<category target_id="1006">
<category flow_id="0" />
<category flow_id="2" />
<category flow_id="4" />
</category>
<category target_id="1007">
<category flow_id="1" />
<category flow_id="6" />
</category>
<category target_id="1008">
<category flow_id="0" />
<category flow_id="1" />
<category flow_id="2" />
</category>
<category target_id="1009">
<category flow_id="0" />
<category flow_id="1" />
<category flow_id="2" />
<category flow_id="3" />
</category>
</group>


And this is my SQL:

SELECT unnest((xpath('@name', team)::text[])) AS group
,unnest((xpath('category/@target_id', team)::text[]::int[])) AS target_id
,unnest((xpath('category/category/@flow_id', team)::text[]::int[])) AS flow_id
FROM (

SELECT team::xml
FROM xml_table;
)


With this SQL i get perfect in case GROUP A and B. When in the group is only 1 target_id and many flow_id (like A)

Group​

Target_id​

Flow_id​

A​

1001​

0​

A​

1001​

1​

A​

1001​

2​

Works Perfect

When each target_id has only one flow_id, doesn't care if is a lot of target_id, like group B.

Group​

Target_id​

Flow_id​

B​

1002​

0​

B​

1003​

0​

B​

1004​

0​

B​

1005​

0​

Works perfect too.

But in the group C doesn't work, repeat many times the instance target_id - flow_id and even mix it, for example appear target_id with other flow_id than doesn't belong (here repeat 3 times Target_id-Flow_id.

Group​

Target_id​

Flow_id​

C​

1006​

0​

C​

1006​

2​

C​

1006​

4​

C​

1006​

0​

C​

1006​

2​

C​

1006​

4​

C​

1006​

0​

C​

1006​

2​

C​

1006​

4​

Mix too

Group​

Target_id​

Flow_id​

C​

1006​

0​

C​

1003​

2​

C​

1004​

6​

Flow_id = 6 is part of target_id = 1007 and it appear in target_id = 1006.

This i want to get, the same structure of the XML holding the order.

Group​

Target_id​

Flow_id​

A​

1001​

0​

A​

1001​

1​

A​

1001​

2​

B​

1002​

0​

B​

1003​

0​

B​

1004​

0​

B​

1005​

0​

C​

1006​

0​

C​

1006​

2​

C​

1006​

4​

C​

1007​

1​

C​

1007​

6​

C​

1008​

0​

C​

1008​

1​

C​

1008​

2​

C​

1009​

0​

C​

1009​

1​

C​

1009​

2​

C​

1009​

3​

That will be perfect and that i needs to get.

Please your help again, thanks a lot!

Continue reading...