XPATH Postgres. Get nodes with his children attribute

Question

i need your help to resolve this. I have a xml and i need get 2 attributes, the first attribute could have many children attributes. I need get all children but with his parent attribute.

The XML have many parent levels but i only show what i'm interest.

  <group name="Name 1">
    <subgroup target="30" show="true">
      <subgroup step="0" key="342d8743cd43db67240ad88be462b5ee"/>
      <subgroup step="2" key="342d8743cd43db67240ad88be462b5ee"/>
      <subgroup step="5" key="342d8743cd43db67240ad88be462b5ee"/>
    </subgroup>
    <subgroup target="45" show="true">
      <subgroup step="0" key="d0a7c4e08dde0d5ea3558d17bbed1413"/>
    </subgroup>
    <subgroup target="23" show="true">
      <subgroup step="2" key="46c787738274a4bd3968dfbec5b12c7c"/>
    </subgroup>
    <subgroup target="80" show="true">
      <subgroup step="1" key="bf6972c426b1672e7108c1680626698b"/>
    </subgroup>
  </group>

I try with this SQL:

SELECT unnest((xpath('////subgroup/@target', oc.xml_row))::text) AS target ,unnest((xpath('////subgroup/subgroup/@step',oc.xml_row))::text) AS step FROM public.target_conf AS oc;

With that SQL i get cartesian product of target and step, but if i put only 1 row i get all target or all step fine.

I need get 'target' with his 'step' children.

Example what i want return with sql posgres:

TARGET | STEP

30 | 0

30 | 2

30 | 5

45 | 0

23 | 2

80 | 1

Thanks a lot for your help!

Tomalak · Accepted Answer · 2020-03-20 22:16:17Z

2

SELECT
    xpath('@target', subgroup) AS target,
    unnest(xpath('subgroup/@step', subgroup)::text[]) AS step
FROM (
    SELECT
        unnest(xpath('/group/subgroup', oc.xml_row)) AS subgroup
    FROM
        public.target_conf AS oc
) AS subgroups;

gives

target  step
    30     0
    30     2
    30     5
    45     0
    23     2
    80     1

See SQL fiddle: http://sqlfiddle.com/#!15/38a43/9

edited Mar 20, 2020 at 22:16

answered Mar 20, 2020 at 21:46

Tomalak

339k68 gold badges547 silver badges635 bronze badges

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

Collectives™ on Stack Overflow

XPATH Postgres. Get nodes with his children attribute

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related