CODEWITHSUNDEEP
xmlms-access
Smit Sanghvi
Smit Sanghvi

Reputation: 41

Access XML import: Add foreign key to child table

I have An XML File that I want to import in MS-access. The problem is that it has multiple nested nodes which means access creates a new table for every different type of node. Now the problem is we cannot create any relationship between different tables, We can't even decipher their relationship in the database. Is there is anyway to solve this problem? The xml is generated by a software I use. A sample XML Data that will cause the type of problem I am facing:

<Envelope>
    <Sale>
        <CustomerName>a</CustomerName>
        <InvoiceNo>1</InvoiceNo>
        <item>
            <ItemName>I1</ItemName>
            <QTY>10</QTY>
            <Amount>25</Amount>
        </item>
        <item>
            <ItemName>I2</ItemName>
            <QTY>15</QTY>
            <Amount>14</Amount>
        </item>
    </Sale>
    <Sale>
        <CustomerName>b</CustomerName>
        <InvoiceNo>2</InvoiceNo>
        <item>
            <ItemName>I3</ItemName>
            <QTY>10</QTY>
            <Amount>72</Amount>
        </item>
        <item>
            <ItemName>I4</ItemName>
            <QTY>21</QTY>
            <Amount>43</Amount>
        </item>
    </Sale>
    <Sale>
        <CustomerName>c</CustomerName>
        <InvoiceNo>3</InvoiceNo>
        <item>
            <ItemName>I5</ItemName>
            <QTY>13</QTY>
            <Amount>53</Amount>
        </item>
        <item>
            <ItemName>I6</ItemName>
            <QTY>37</QTY>
            <Amount>27</Amount>
        </item>
    </Sale>
    <Sale>
        <CustomerName>d</CustomerName>
        <InvoiceNo>4</InvoiceNo>
        <item>
            <ItemName>I7</ItemName>
            <QTY>45</QTY>
            <Amount>67</Amount>
        </item>
        <item>
            <ItemName>I8</ItemName>
            <QTY>12</QTY>
            <Amount>98</Amount>
        </item>
    </Sale>
</Envelope>

Upvotes: 1

Views: 1877

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123654

If [InvoiceNo] can serve as the primary key of the [Sale] table then all you need to do is perform an XML Transform to add [InvoiceNo] to the [item] table while importing. If you save the following as "LinkItemsToSales.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="@*|node()">
        <xsl:copy>
            <xsl:apply-templates select="@*|node()"/>
        </xsl:copy>
    </xsl:template>

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

</xsl:stylesheet>

then you can use the "Transform..." button on the "Import XML" dialog to specify that file as the transform you want to apply. When the import is completed the [InvoiceNo] will appear in the [item] table like this:

InvoiceNo  ItemName  QTY  Amount
---------  --------  ---  ------
1          I1        10   25    
1          I2        15   14    
2          I3        10   72    
2          I4        21   43    
3          I5        13   53    
3          I6        37   27    
4          I7        45   67    
4          I8        12   98

Upvotes: 2

Related Questions