Use XSLT to add key to all child nodes in XML to MS Access transfer

Question

I'm trying to use XSLT to add a key to all child nodes so tables created during an XML import into MS Access have a an appropriate identifier across all created tables. I've used some examples I've found to cobble some XSLT together but I don't fundamentally understand how this works to the degree where I can troubleshoot it. I can get Id placeholder in the Add node, but the value is blank. Is there a way to programmatically add the Id key to all child nodes without coding each one individually? If not, how to I go about editing the XSLT to ensure the Id field comes through?

To complicate things, the Add element is always under ContactDetails but ContactDetails varies in terms of nodes relative to the Id element. It may be 4-7 nodes deep, for example.

Sample XML:

<Response>


<Alices>
    <Alice>
        <Id>12345</Id>
        <Bobbers>
            <Name>John Doe</Name>
            <Bobs>
                <Bob>
                    <Organization>
                        <Name>John Doe</Name>
                        <ABB>987654</ABB>
                        <ContactDetails>
                            <Adds>
                                <Add>
                                    <Type>Postal</Type>
                                    <Line1>PO BOX 12345</Line1>
                                    <Suburb>Doeville</Suburb>
                                    <State>ENE</State>
                                    <PostCode>1111</PostCode>
                                    <Country>GB</Country>
                                    <Preferred>false</Preferred>
                                </Add>
                                <Add>
                                    <Type>Street</Type>
                                    <Line1>123 Anywhere</Line1>
                                    <Suburb>Doeville</Suburb>
                                    <State>ENE</State>
                                    <PostCode>1111</PostCode>
                                    <Country>GB</Country>
                                    <Preferred>true</Preferred>
                                </Add>
                            </Adds>
                            <PNs>
                                <PN>
                                    <Type>Mobile</Type>
                                    <Number>11111111</Number>
                                    <Preferred>true</Preferred>
                                </PN>
                            </PNS>
                            <EMs>
                                <EM>
                                    <Type>Personal</Type>
                                    <Add>[email protected]</Add>
                                    <Preferred>false</Preferred>
                                </EM>
                            </EMs>
                            <PreferredContactMethod>Email</PreferredContactMethod>
                        </ContactDetails>
                        <Contacts>
                            <Contact>
                                <LastName>Doe</LastName>
                                <FirstName>John</FirstName>
                            </Contact>
                        </Contacts>
                    </Organization>
                </Bob>
            </Bobs>
        </Bobbers>
        <Jons>
            <Jon>
                <Id>012991</Id>
                <PrimaryJon>true</PrimaryJon>
                <StartDate>1900-01-01</StartDate>
            </Jon>
        </Jons>
    </Alice>
</Alices>

</Response>

And the XSLT:

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

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

</xsl:stylesheet>

And here is what I'm after:

<Response>

<Alices>
    <Alice>
        <Id>12345</Id>
        <Bobbers>
            <Name>John Doe</Name>
            <Bobs>
                <Bob>
                    <Organization>
                        <Id>12345</Id>
                        <Name>John Doe</Name>
                        <ABB>987654</ABB>
                        <ContactDetails>
                            <Adds>
                                <Add>
                                    <Id>12345</Id>
                                    <Type>Postal</Type>
                                    <Line1>PO BOX 12345</Line1>
                                    <Suburb>Doeville</Suburb>
                                    <State>ENE</State>
                                    <PostCode>1111</PostCode>
                                    <Country>GB</Country>
                                    <Preferred>false</Preferred>
                                </Add>
                                <Add>
                                    <Id>12345</Id>                              
                                    <Type>Street</Type>
                                    <Line1>123 Anywhere</Line1>
                                    <Suburb>Doeville</Suburb>
                                    <State>ENE</State>
                                    <PostCode>1111</PostCode>
                                    <Country>GB</Country>
                                    <Preferred>true</Preferred>
                                </Add>
                            </Adds>
                            <PNs>
                                <PN>
                                    <Id>12345</Id>                              
                                    <Type>Mobile</Type>
                                    <Number>11111111</Number>
                                    <Preferred>true</Preferred>
                                </PN>
                            </PNS>
                            <EMs>
                                <EM>
                                    <Id>12345</Id>                              
                                    <Type>Personal</Type>
                                    <Add>[email protected]</Add>
                                    <Preferred>false</Preferred>
                                </EM>
                            </EMs>
                            <PreferredContactMethod>Email</PreferredContactMethod>
                        </ContactDetails>
                        <Contacts>
                            <Contact>
                                <Id>12345</Id>                          
                                <LastName>Doe</LastName>
                                <FirstName>John</FirstName>
                            </Contact>
                        </Contacts>
                    </Organization>
                </Bob>
            </Bobs>
        </Bobbers>
        <Jons>
            <Jon>
                <Id>012991</Id>
                <PrimaryJon>true</PrimaryJon>
                <StartDate>1900-01-01</StartDate>
            </Jon>
        </Jons>
    </Alice>
</Alices>

</Response>

Answer 1

Try along the lines of

<xsl:stylesheet
    xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    version="1.0">

  <xsl:output method="xml" indent="yes"/>
  <xsl:strip-space elements="*"/>

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

  <xsl:template match="*[*[not(*)] and ancestor::*[Id]]">
    <xsl:copy>
      <xsl:apply-templates select="ancestor::*[Id]/Id | *"/>
    </xsl:copy>
  </xsl:template>

</xsl:stylesheet>