从Access导出到XML时，从子元素中忽略外键值 [英] Omit foreign key values from child elements when exporting from Access to XML

问题描述

我有两个表CR和RELEASE，它们之间的关系从CR(ID)到RELEASE(CRID)一一对应.

I have two tables CR and RELEASE with relationship from CR (ID) to RELEASE (CRID) one to many.

CR(设计):

ID: key, unique
Description: text

发布(设计):

ID: key, unique
CRID: number, not unique
Name: text

使用以下VBA代码，可以将表导出为XML.

With the following VBA code, I am managed to export the tables to XML.

Set objOrderInfo = Application.CreateAdditionalData
objOrderInfo.Add ("RELEASE")
Application.ExportXML ObjectType:=acExportTable, DataSource:="CR", _
                      DataTarget:=pFileNAme, _
                      AdditionalData:=objOrderInfo

导出的XML类似于:

<?xml version="1.0" encoding="UTF-8"?>
<dataroot xmlns:od="urn:schemas-microsoft-com:officedata" generated="2015-12-09T09:34:28">
<CR>
    **<ID>1</ID>**
    <Description>Test</Description>
    <RELEASE>
        <ID>1</ID>
        **<CRID>1</CRID>**
        <Name>R2016</Name>
    </RELEASE>
    <RELEASE>
        <ID>2</ID>
        **<CRID>1</CRID>**
        <Name>R2017</Name>
    </RELEASE>
</CR>

请注意，CRID在XML中显示了几次，实际上是多余的.如何从XML的RELEASE元素中删除CRID元素?谢谢，

Note that CRID showed several times in the XML which is practically redundant. How to remove the CRID elements from RELEASE element in XML? Thanks,

推荐答案

如果在使用Application.ExportXML之后需要调整XML输出，则可以将初始输出导出到临时XML文件，然后使用.xslt文件，例如:

If you need to tweak the XML output after using Application.ExportXML you could do the initial export to a temporary XML file and then use an .xslt file like this:

<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="RELEASE/CRID">
        <!-- omit by doing nothing -->
    </xsl:template>

</xsl:stylesheet>

和这样的VBA例程

Option Compare Database
Option Explicit

Public Sub ApplyXmlTransform(sourceFile, stylesheetFile, resultFile)
    ' ref: http://archive.oreilly.com/pub/post/transforming_xml_in_microsoft.html
    '
    ' project reference required: Microsoft XML, v4.0

    Dim source As New MSXML2.DOMDocument30
    Dim stylesheet As New MSXML2.DOMDocument30
    Dim result As New MSXML2.DOMDocument30

    ' Load data.
    source.async = False
    source.Load sourceFile  ' source .xml file

    ' Load style sheet.
    stylesheet.async = False
    stylesheet.Load stylesheetFile  ' .xslt file

    If (source.parseError.errorCode <> 0) Then
        MsgBox ("Error loading source document: " & source.parseError.reason)
    Else
        If (stylesheet.parseError.errorCode <> 0) Then
            MsgBox ("Error loading stylesheet document: " & stylesheet.parseError.reason)
        Else
            ' Do the transform.
            source.transformNodeToObject stylesheet, result
            result.Save resultFile  ' resulting .xml file
        End If
    End If

End Sub

从<RELEASE>元素中删除<CRID>.

这篇关于从Access导出到XML时，从子元素中忽略外键值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！