如何将嵌套节点（父/子关系）的XML导入Access？ [英] How to import XML with nested nodes (parent/child relationships) into Access?

问题描述

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

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.

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

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

以下是XML数据的示例：

Here is a sample of the XML data:

<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>

推荐答案

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

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.

以下XSLT文件将为您执行此操作

The following XSLT file will do that for you

<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>

这将改变你的XML ......

That will transform your XML from this ...

<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>

...进入：

<?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>

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

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

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

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文件添加到列表中并选择它......

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

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

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.

导入完成后，您仍然会有两个表，但现在您可以在 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:

给我们

这篇关于如何将嵌套节点（父/子关系）的XML导入Access？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！