xml (1 Viewer)

[zezo2021]

If I have an XML file that contains the following branches: grandfather table, father table, and son table, I have linked the grandfather table to the father table as shown in this code. However, I was unable to link the son table to the father table because there is no linking number PK in the father Table.

Can the attached code be used to add auto number to the father table and add it to the son table so that I can link the father to the son?

These are the names to understand the explanation: grandfather, father, and grandson.
Here actual table Name

Employees <<<<< GrandFather
--------Enrollment <<<<Father
--------------------CafeteriaData <<<<Son

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    <xsl:output indent="yes"/>
    <xsl:strip-space elements="*"/>

    <xsl:template match="/">
        <dataroot>
            <xsl:apply-templates select="@*|node()"/>
        </dataroot>
    </xsl:template>

    <xsl:template match="@*|node()">
        <xsl:copy>
            <xsl:apply-templates select="@*|node()"/>
        </xsl:copy>
    </xsl:template>

    <xsl:template match="Employees">
        <xsl:apply-templates select="@*|node()"/>
    </xsl:template>

    <xsl:template match="Enrollment">
        <Enrollment>
            <ExternalEmployeeId><xsl:value-of select="../../ExternalEmployeeId"/></ExternalEmployeeId>
            <xsl:apply-templates select="@*|node()"/>
        </Enrollment>
    </xsl:template>

    <xsl:template match="CafeteriaData">
        <CafeteriaData>
            <PKAutoNumber><xsl:value-of select="../../../PKAutoNumber"/></ExternalEmployeeId>
            <xsl:apply-templates select="@*|node()"/>
        </CafeteriaData>
    </xsl:template>

</xsl:stylesheet>

 

[gemma-the-husky]

I would not have thought you would map a grandfather to grandson relationship. All you need is a child (son) to father relationship. You don't need separate tables for these relations. All you need is a single table of all people, and to map the relationships of child to father (and child to mother).

The grandfather of a child is the father of the father. Then an uncle is a sibling of the father, ie another son of the father of the father. A cousin is the child of your father's siblings. Etc.

All familial relationships can be derived from these basic (recursive) relationship. (You really need a child to mother as well as child to father)

 

[zezo2021]

I would not have thought you would map a grandfather to grandson relationship. All you need is a child (son) to father relationship. You don't need separate tables for these relations. All you need is a single table of all people, and to map the relationships of child to father (and child to mother).

The grandfather of a child is the father of the father. Then an uncle is a sibling of the father, ie another son of the father of the father. A cousin is the child of your father's siblings. Etc.

All familial relationships can be derived from these basic (recursive) relationship. (You really need a child to mother as well as child to father)

Yes
I need a relationship between Father and soon
how can I do that in the attached xslt


I don't understand. I am not familiar with your English I hope not contain something wrong or a mystery


the family relation is an example I give you the actual tables

1
2
3
I need to create a relation between 2 and 3 because not exist

 

[GPGeorge]

Yes
I need a relationship between Father and soon
how can I do that in the attached xslt


I don't understand. I am not familiar with your English I hope not contain something wrong or a mystery


the family relation is an example I give you the actual tables

1
2
3
I need to create a relation between 2 and 3 because not exist

What Dave asked about is the relationship Grandfather-->Son-->Grandson, not just Father --> Son.

You do not need a separate way to relate Grandfather to Grandson.

The way this is often done is a Self-Join in a single table, in fact. Without seeing sample data, and relying only the XML provided, it's a bit iffy to talk about it concretely. But here is a table that displays what we're talking about.

It's a single table called "Individual", which could be something else depending on what you are really working with.

It has a Primary Key uniquely identifying each person. It has fields for first, middle and last names. Other specific name fields may be appropriate, again, depending on what you are really storing in the database.

It has two parent keys because each person has 1 father and 1 mother, not a single parent. That's where what you describe seems a bit vague and makes me wonder if you are using the parental relationship terms for something else. If your database only includes males, that is another head-scratcher because all people have both a mother and father.

The point is that the Primary Key for the father is entered in the Foreign Key field for Parent1 in a different record, and the Primary Key for the mother is entered in the Foreign Key field for Parent2 in that other record.

If you want to find the parents, you look at Parent1 and Parent2. If you want to find the grandparents you have to start with Parent 1 and Parent2 and then find the Parent1 and Parent2 for Parent1 and Parent1 and Parent2 for Parent2. That yields 4 grandparents, two grandfathers and two grandmothers.

And you continue that same process up and down the family line, one generation at a time. You can't skip over a generation and relate a person to a grandchild.

Also, I see that you are using aliases for the real names in the data, which is more confusing, not less for the most part. Because your data only has a single relationship, it would only need a single self-join field, of course.


How you translate the XML you have to that table is a different story, and if that's the heart of the problem, I'm afraid I have no relevant experience translating XML to relational tables. This, however, is how I would create the family ancestry relationship on the Individual or Person table. One table with two self joins.

 

[tvanstiphout]

Did y'all miss this part of the OP's post

Here actual table Name
Employees <<<<< GrandFather
--------Enrollment <<<<Father
--------------------CafeteriaData <<<<Son

This can be solved with normal one to many relations.
tblEmployees
EmployeeID autonumber pk
EmployeeFirstName text20 required
etc.

tblEnrollment
EnrollmentID autonumber pk
EnrollmentDate datetime required
EmployeeID long int required fk
etc.

tblCafetariaUsage
CafetariaUsageID autonumber pk
EmployeeID long int required fk
UsageDate datetime required
TotalAmount currency
etc.

And of course the proper enforced relations in Relationships window.

 

[gemma-the-husky]

But a system describing an allocation of two unrelated tables via a junction table, is not the same as a structure modelling three generations of a family.

To be honest I didn't understand the significance of the 3 record types, employees, enrolments and cafeterias.

 

[cheekybuddha]

To be honest I didn't understand the significance of the 3 record types, employees, enrolments and cafeterias.

You missed the point of 'the three generations of a family'.

That is purely to describe the relationship between the tables employees, enrolments and cafeterias.

There are no actual grandfathers, fathers or sons. It is a [mis-]translation of parent table / child table / child of child table.

 

[GPGeorge]

I was one of those mislead by the repeated misuse of the Grandparent-Parent-Grandchild terminology.

Fortunately, Tom vS was not.

 

[zezo2021]

Hello

I find the answer here:

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    <xsl:output indent="yes"/>
    <xsl:strip-space elements="*"/>

    <xsl:template match="/">
        <dataroot>
            <xsl:apply-templates select="@*|node()"/>
        </dataroot>
    </xsl:template>

    <xsl:template match="@*|node()">
        <xsl:copy>
            <xsl:apply-templates select="@*|node()"/>
        </xsl:copy>
    </xsl:template>

    <xsl:template match="Employees">
        <xsl:apply-templates select="@*|node()"/>
    </xsl:template>

    <xsl:template match="Enrollment">
        <Enrollment>
            <ExternalEmployeeId><xsl:value-of select="../../ExternalEmployeeId"/></ExternalEmployeeId>
            <xsl:apply-templates select="@*|node()"/>
        </Enrollment>
    </xsl:template>


 <xsl:template match="/">
        <dataroot>
            <xsl:apply-templates select="@*|node()"/>
        </dataroot>
    </xsl:template>

    <xsl:template match="@*|node()">
        <xsl:copy>
            <xsl:apply-templates select="@*|node()"/>
        </xsl:copy>
    </xsl:template>




 <xsl:template match="Enrollments">
        <xsl:apply-templates select="@*|node()"/>
    </xsl:template>

    <xsl:template match="CafeteriaData">
        <CafeteriaData>
            <Created><xsl:value-of select="../Created"/></Created>
            <xsl:apply-templates select="@*|node()"/>
        </CafeteriaData>
    </xsl:template>


<xsl:template match="/">
        <dataroot>
            <xsl:apply-templates select="@*|node()"/>
        </dataroot>
    </xsl:template>

    <xsl:template match="@*|node()">
        <xsl:copy>
            <xsl:apply-templates select="@*|node()"/>
        </xsl:copy>
    </xsl:template>




 <xsl:template match="Enrollments">
        <xsl:apply-templates select="@*|node()"/>
    </xsl:template>

    <xsl:template match="HSAData">
        <HSAData>
            <Created><xsl:value-of select="../Created"/></Created>
            <xsl:apply-templates select="@*|node()"/>
        </HSAData>
    </xsl:template>




</xsl:stylesheet>

 

[gemma-the-husky]

You missed the point of 'the three generations of a family'.

That is purely to describe the relationship between the tables employees, enrolments and cafeterias.

There are no actual grandfathers, fathers or sons. It is a [mis-]translation of parent table / child table / child of child table.

Yes, but employees, cafeteria, enrolments is different to grandparents, parents, children. The first is a junction table to deal with many to many. The second is 1 to many rather than many to many.