Child items do not have a foreign key pointing to the parent

[nimafazeli]

Here is a minimum reproducible example:
consider the mre.xml file (this is a modified version of an xbrl file that I cropped ):

 <XML>
  <ROCRA>
    <RAN contextRef="m1">Moody's Investors Service</RAN>
    <FCD contextRef="m1">2022-04-15</FCD>
    <OD>
      <OSC contextRef="m1">Financial</OSC>
      <OBNAME contextRef="m1">MUFG Securities (Europe) N.V.</OBNAME>
      <OI contextRef="m1">830986287</OI>
      <OIS contextRef="m1">NRSRO</OIS>
      <ORD>
        <IP contextRef="m1">true</IP>
        <R contextRef="m1">A1</R>
        <RAD contextRef="m1">2018-10-25</RAD>
        <RAC contextRef="m1">NW</RAC>
        <RT contextRef="m1">Organization</RT>
        <RTT contextRef="m1">LT Issuer Rating</RTT>
      </ORD>
      <ORD>
        <IP contextRef="m1">true</IP>
        <R contextRef="m1">B1</R>
        <RAD contextRef="m1">2019-10-21</RAD>
        <RAC contextRef="m1">NW</RAC>
        <RT contextRef="m1">Organization</RT>
        <RTT contextRef="m1">LT Issuer Rating</RTT>
      </ORD>
    </OD>
    <ISD>
      <SSC contextRef="m1">Financial</SSC>
      <ISSNAME contextRef="m1">MUFG Securities (Europe) N.V.</ISSNAME>
      <ISI contextRef="m1">830986287</ISI>
      <ISIS contextRef="m1">NRSRO</ISIS>
    </ISD>
  </ROCRA>
  </XML>

If I run mre.xml %>% toRelational() I get the following:

$XML
  ID_XML FKID_ROCRA
1  13320       5294

$ROCRA
  ID_ROCRA                       RAN        FCD FKID_OD FKID_ISD
1     5294 Moody's Investors Service 2022-04-15  947669    90193

$OD
   ID_OD       OSC                        OBNAME        OI   OIS FKID_ORD
1 947669 Financial MUFG Securities (Europe) N.V. 830986287 NRSRO   103000

$ORD
  ID_ORD   IP  R        RAD RAC           RT              RTT
1 182003 true A1 2018-10-25  NW Organization LT Issuer Rating
2 103000 true B1 2019-10-21  NW Organization LT Issuer Rating

$ISD
  ID_ISD       SSC                       ISSNAME       ISI  ISIS
1  90193 Financial MUFG Securities (Europe) N.V. 830986287 NRSRO

I want to join the OD and ORD tables to create a dataframe with two rows from the ORD table joined with the relevant information from the OD table, i.e. the following:

        OSC                        OBNAME        OI   OIS   IP  R        RAD RAC           RT              RTT
1 Financial MUFG Securities (Europe) N.V. 830986287 NRSRO true A1 2018-10-25  NW Organization LT Issuer Rating
2 Financial MUFG Securities (Europe) N.V. 830986287 NRSRO true B1 2019-10-21  NW Organization LT Issuer Rating

I admit I am not very experienced in relational databases, but I guess logically I should be able to get this by matching a foreign key from the ORD table that points to a primary key in the OD table (i.e. the parent node) and not the other way round. But instead, it is the parent table that is giving one single key that matches with only one of its children.

Thanks

[mskyttner]

+1

I am confused by this too...