1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

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

Discussão em 'Outras Linguagens' iniciado por Stack, Julho 20, 2021.

  1. Stack

    Stack Membro Participativo

    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...

Compartilhe esta Página

Para os pais de Plantão algumas dicas