xslt - How to import XML with layers of nested nodes (parent/child/child relationships) into Access?

Question

I've tried modifying @Gord Thompson's solution for @AMB at

https://stackoverflow.com/questions/30029303/how-to-import-xml-with-nested-nodes-parent-child-relationships-into-access#=

to suit my purposes, but it's not inserting the parent value required into the child/sub-child nodes. Here is a sample of my XML:

<?xml version="1.0" encoding="utf-8"?>
<CueSheets xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"    
<Header>
    <ReportType>C</ReportType>
    <ReportPeriodStartDate>20110101</ReportPeriodStartDate>
    <ReportPeriodEndDate>20150814</ReportPeriodEndDate>
</Header>
<CueSheet>
    <NewOrUpdate>N</NewOrUpdate>
    <EbiquityId>7234709</EbiquityId>
    <EbiquityFilename>7234709_1.mpg</EbiquityFilename>
    <AdTitle>2015- Available Now At The Warehouse.</AdTitle>
    <AdDescription>Artists listed. Retailers listed.</AdDescription>
    <AdDuration>00:00:15</AdDuration>
    <FirstTransmissionDate>20150212</FirstTransmissionDate>
    <FirstTransmissionStation>FOUR</FirstTransmissionStation>
    <Brand>Summer Mix Tape</Brand>
    <Product>cd release</Product>
    <Cue>
        <TrackSequenceNumber>1</TrackSequenceNumber>
        <TrackTitle>Freaks (radio edit)</TrackTitle>
        <Artists>
            <Artist>Timmy Trumpet &amp; Savage</Artist>
        </Artists>
        <ProductionMusic>N</ProductionMusic>
        <ARID>52359527</ARID>
        <TimeIn>00:00:00</TimeIn>
        <TimeOut>00:00:04</TimeOut>
        <Duration>00:00:04</Duration>
    </Cue>
    <Cue>
        <TrackSequenceNumber>2</TrackSequenceNumber>
        <TrackTitle>I'm An Albatraoz</TrackTitle>
        <Artists>
            <Artist>AronChupa</Artist>
        </Artists>
        <Composers>
            <Composer>Aron Ekberg</Composer>
        </Composers>
        <ProductionMusic>N</ProductionMusic>
        <RecordLabels>
            <RecordLabel>Sony Music</RecordLabel>
        </RecordLabels>
        <ARID>54949472</ARID>
        <TimeIn>00:00:04</TimeIn>
        <TimeOut>00:00:09</TimeOut>
        <Duration>00:00:05</Duration>
    </Cue>
    <Cue>
        <TrackSequenceNumber>3</TrackSequenceNumber>
        <TrackTitle>Geronimo</TrackTitle>
        <Artists>
            <Artist>Sheppard</Artist>
        </Artists>
        <Composers>
            <Composer>George Sheppard</Composer>
            <Composer>Amy Sheppard ,Jay Bovino</Composer>
        </Composers>
        <ProductionMusic>N</ProductionMusic>
        <RecordLabels>
            <RecordLabel>UMI Decca Records</RecordLabel>
        </RecordLabels>
        <ISRCs>
            <ISRC>AU-IYA-14-00002</ISRC>
        </ISRCs>
        <ARID>204313468</ARID>
        <TimeIn>00:00:09</TimeIn>
        <TimeOut>00:00:15</TimeOut>
        <Duration>00:00:06</Duration>
    </Cue>
    <Complete>Y</Complete>
</CueSheet>
</CueSheets>

and my attempt at the XSL:

<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="CueSheet">
    <xsl:apply-templates select="@*|node()"/>
</xsl:template>

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

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

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

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

<xsl:template match="ISRCs">
    <ISRCs>
        <EbiquityID><xsl:value-of select="../../../EbiquityID"/></EbiquityID>
    </ISRCs>
</xsl:template>

I'm guessing it's to do with the number of "../", have tried a couple of variations more/less and can't get it to work. Hoping someone can help, thanks in advance.

Update after testing @Michael's suggestions:

I've incorporated your suggestions but still can't get it to work, here's the latest xsl:

<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="CueSheet">
    <xsl:apply-templates select="@*|node()"/>
</xsl:template>

<xsl:template match="Cue | Artists | Composers | RecordLabels | ISRCs">
<xsl:copy>
    <EbiquityID>
        <xsl:value-of select="$ebiquityId"/>
    </EbiquityID>
    <xsl:apply-templates/>
</xsl:copy>
</xsl:template>

Hope someone can spot the problem

Answer 1

If you're not sure how many levels to go up, you can use simply:

<EbiquityID>
    <xsl:value-of select="ancestor::CueSheet/EbiquityId"/>
</EbiquityID>

Or, even better, since you access this node so many times, put it into a variable at the top of your stylesheet.

Note also that you could use one template:

<xsl:template match="Cue | Artists | Composers | RecordLabels">
    <xsl:copy>
        <EbiquityID>
            <xsl:value-of select="$ebiquityId"/>
        </EbiquityID>
        <xsl:apply-templates/>
    </xsl:copy>
</xsl:template>

instead of 4.

---

In addition to the above: XML is case-sensitive. EbiquityID does not select EbiquityId!

---

Complete Solution

Below applies <EbiquityID> to <Cue> template and all its children. You can then use MS Access' XML Import wizard on Ribbon or the automated VBA method Application.ImportXML. Additionally, the <Header> node is removed as it does not seem to fit your database structure.

<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="@*|node()">
    <xsl:copy>
        <xsl:apply-templates select="@*|node()"/>
    </xsl:copy>
</xsl:template>

<xsl:template match="Header"/>    

<xsl:template match="Cue|Artists|Composers|RecordLabels|ISRCs">
    <xsl:copy>    
        <EbiquityID>
            <xsl:value-of select="ancestor::CueSheet/EbiquityId"/>
        </EbiquityID>
        <xsl:apply-templates/>
    </xsl:copy>
</xsl:template>

</xsl:stylesheet>