Get order nodes with childrens in XML XPATH POSTGRES

Question

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	1006	2
C	1006	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

Xpaths are missing context I think, it should be //category/@target_id, /group/@name and //category/category/@flow_id — LMC
– LMC, Commented Jul 20, 2021 at 2:03

Jim Jones · Accepted Answer · 2021-07-20 08:56:16Z

2

XPATH expressions can get really nasty with complex tasks. My suggestion: Split the operations in multiple subqueries or CTEs to make things easier. This might give you some inspiration:

SELECT DISTINCT
  g.group_id,
  t.target_id,
  unnest(xpath('//group[@name="'||g.group_id||'"]/category/category/@flow_id', team)::text[]) flow_id
FROM 
  xml_table x,
  LATERAL unnest(xpath('//group/@name', x.team)::text[]) g (group_id),
  LATERAL unnest(xpath('//group[@name="'||g.group_id||'"]/category/@target_id', team)::text[]) t (target_id)  
ORDER BY g.group_id,t.target_id,flow_id;

Demo: db<>fiddle

edited Jul 20, 2021 at 8:56

answered Jul 20, 2021 at 8:35

Jim Jones

19.9k3 gold badges44 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Alvaro Over a year ago

Thanks man for your answer, sorry for the late reply. I find other solution first, unnest by stages, not all together. Anyway your sql is another way than i don't know, thanks for that. But in the XML have 279 records and this sql return 450. I don't know why, i will look and check later. Anyway this give me another perspective, thanks!

Collectives™ on Stack Overflow

Get order nodes with childrens in XML XPATH POSTGRES

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related