CODEWITHSUNDEEP
xmlms-accessxslt
Chris
Chris

Reputation: 25

XML Import into Access needs a Foreign key

I'm trying to import multiple XML files into Access and using a transform. I need the foreign key OrderID to show up on the Program table. Here is my XML:

<Requests>
<Request>
<orderID>12345</orderID>
<FA><![CDATA[Jon Smith]]></FA>
<FA_state>MN</FA_state>
<ship_to>Online Delivery</ship_to>
<prospect><![CDATA[Elvis]]></prospect>
<address><![CDATA[2999 Street]]></address>
<city><![CDATA[Minneapolis]]></city>
<state>MN</state>
<zip>55413</zip>
<ship_dt>10/16/2015 11:45:11</ship_dt>
<delivery_method>Online Delivery</delivery_method>
<programs>
  <program>
    <program_name><![CDATA[Hedge Fund]></program_name>
    <copy_number>56211B87IUQPTCE</copy_number>
  </program>
</programs>
 <file_create_dt>10/16/2015</file_create_dt>
<request_dt>10/15/2015 09:19:50</request_dt>
<employee_id>1102132</employee_id>
</Request>
</Requests>

And the transform I am using:

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

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

The transform creates the foreign key field on the Program table, but fails to insert any values. I'm totally perplexed at this point. thank you!

Upvotes: 1

Views: 232

Answers (1)

Tim C
Tim C

Reputation: 70618

Your template matches the program node, so the XPath expression ../OrderID will select the OrderID element that is a child of the parent of the current program node. But the OrderID is a child of the "grandparent".

You should try this, to go back up one extra level

<xsl:value-of select="../../orderID"/>

Upvotes: 1

Related Questions