如何将带有嵌套节点(父/子关系)的 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>

推荐答案

您需要做的是转换您的 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屋！