如何将嵌套节点（父/子关系）的XML导入Access？

I'm trying to import an XML file into Access but it creates 3 unrelated tables. That is, the child records are imported into the child table, but there is no way of knowing which child records belong to which parent.

我正在尝试将XML文件导入Access，但它会创建3个不相关的表。也就是说，子记录被导入到子表中，但是无法知道哪个子记录属于哪个父记录。

How can I import the data to maintain the relationship between the parent and child nodes (records)?

如何导入数据以维护父节点和子节点（记录）之间的关系？

Here is a sample of the XML data:

以下是XML数据的示例：

<NOTARIO>
    <C_NOT>8404180</C_NOT>
    <APE>Abalos Nuevo</APE>
    <NOM>Francisco José</NOM>
    <NOTARIAS>
        <NOTARIA>
            <PRO>23</PRO>
            <MUN>0888</MUN>
            <F_IN>1984-12-01</F_IN>
            <F_FI>1986-09-19</F_FI>
        </NOTARIA>
        <NOTARIA>
            <PRO>14</PRO>
            <MUN>0569</MUN>
            <F_IN>1990-09-17</F_IN>
            <F_FI>1995-03-15</F_FI>
        </NOTARIA>
        <NOTARIA>
            <PRO>21</PRO>
            <MUN>0412</MUN>
            <F_IN>1995-03-30</F_IN>
            <F_FI></F_FI>
        </NOTARIA>
    </NOTARIAS>
</NOTARIO>

1 个解决方案

#1

What you need to do is transform your XML data into a format that works better with Access. Specifically, you need to insert the parent key value (assuming that it is C_NOT in this case) into each child node.

您需要做的是将XML数据转换为更适合Access的格式。具体来说，您需要将父键值（假设在这种情况下为C_NOT）插入每个子节点。

The following XSLT file will do that for you

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

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

</xsl:stylesheet>

That will transform your XML from this ...

这会改变你的XML ......

<NOTARIO>
    <C_NOT>8404180</C_NOT>
    <APE>Abalos Nuevo</APE>
    <NOM>Francisco José</NOM>
    <NOTARIAS>
        <NOTARIA>
            <PRO>23</PRO>
            <MUN>0888</MUN>
            <F_IN>1984-12-01</F_IN>
            <F_FI>1986-09-19</F_FI>
        </NOTARIA>
        <NOTARIA>
            <PRO>14</PRO>
            <MUN>0569</MUN>
            <F_IN>1990-09-17</F_IN>
            <F_FI>1995-03-15</F_FI>
        </NOTARIA>
        <NOTARIA>
            <PRO>21</PRO>
            <MUN>0412</MUN>
            <F_IN>1995-03-30</F_IN>
            <F_FI></F_FI>
        </NOTARIA>
    </NOTARIAS>
</NOTARIO>

... into this:

......进入这个：

<?xml version="1.0" encoding="UTF-8"?>
<dataroot>
    <NOTARIO>
        <C_NOT>8404180</C_NOT>
        <APE>Abalos Nuevo</APE>
        <NOM>Francisco José</NOM>
        <NOTARIA>
            <C_NOT>8404180</C_NOT>
            <PRO>23</PRO>
            <MUN>0888</MUN>
            <F_IN>1984-12-01</F_IN>
            <F_FI>1986-09-19</F_FI>
        </NOTARIA>
        <NOTARIA>
            <C_NOT>8404180</C_NOT>
            <PRO>14</PRO>
            <MUN>0569</MUN>
            <F_IN>1990-09-17</F_IN>
            <F_FI>1995-03-15</F_FI>
        </NOTARIA>
        <NOTARIA>
            <C_NOT>8404180</C_NOT>
            <PRO>21</PRO>
            <MUN>0412</MUN>
            <F_IN>1995-03-30</F_IN>
            <F_FI />
        </NOTARIA>
    </NOTARIO>
</dataroot>

... in the background while Access is importing it.

...在Access导入时在后台运行。

Save that XSLT file to your hard drive (I called mine "transformio.xslt"), then start the Access XML import process. Once you've selected your XML file to import, click the "Transform" button ...

将该XSLT文件保存到您的硬盘驱动器（我称之为“transformio.xslt”），然后启动Access XML导入过程。选择要导入的XML文件后，单击“转换”按钮...

... add your newly-created XSLT file to the list and select it ...

...将新创建的XSLT文件添加到列表中并选择它...

When you click "OK" and return to the "Import XML" dialog, you can expand the tree view to see that you now have C_NOT values in both tables.

单击“确定”并返回“导入XML”对话框时，可以展开树视图以查看两个表中现在都有C_NOT值。

When the import is complete you will still have two tables, but now you can JOIN them on C_NOT to get a "flat" view of the data:

导入完成后，您仍然会有两个表，但现在您可以在C_NOT上加入它们以获得数据的“平面”视图：

which gives us

这给了我们